Command button to Import files (1 Viewer)

C

crc_love

New member

Local time

Today, 10:57

Joined

Nov 22, 2015

Messages

6

• Nov 22, 2015

• #1

Greetings,

I need help with adding a command button to a form that will import data from an Excel file into a table. I am not a programmer at all and really need help. I have created an Overtime Hours table and need to add a command button to the form that will import the Overtime Hours Excel file into the table. So each week when I receive the Overtime spreadsheet from HR, I would like to click an Import button on my form that will pull the data from the spreadsheet into the existing Overtime Hours table.

 

S

sneuberg

AWF VIP

Local time

Today, 07:57

Joined

Oct 17, 2014

Messages

3,506

• Nov 22, 2015

• #2

Check out https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

If you can't figure out how to use this, let us know.

 

C

crc_love

New member

Local time

Today, 10:57

Joined

Nov 22, 2015

Messages

6

• Nov 22, 2015

• #3

Hi sneuberg,

I tried but received a run-time error. Here is exactly what I entered.

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 3, _
"Overtime Hours", "C:\Users\CRC\Documents\OVERTIME HOURS", True, "A1:G12"
End Sub

 

S

sneuberg

AWF VIP

Local time

Today, 07:57

Joined

Oct 17, 2014

Messages

3,506

• Nov 23, 2015

• #4

What error did you get? The only thing I wonder about in your statement is the file name. Why doesn't it have an excel extension like "C:\Users\CRC\Documents\OVERTIME HOURS.XLS"

 

C

crc_love

New member

Local time

Today, 10:57

Joined

Nov 22, 2015

Messages

6

• Nov 23, 2015

• #5

I figured out that the initial error was due to acImport,3 (used for Lotus). So I updated to acImport, 12.

New Values...
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 12, _
"States", "C:\Users\LACHONDA\Documents\STATES.xlsx", True, "A1:G12"
End Sub

Now I am receiving the below error.

A run-time error '2508':
A value you entered for the spreadsheettype argument is invalid.

 

S

sneuberg

AWF VIP

Local time

Today, 07:57

Joined

Oct 17, 2014

Messages

3,506

• Nov 23, 2015

• #6

Try

DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12 , _
"States", "C:\Users\LACHONDA\Documents\STATES.xlsx", True, "A1:G12"

 

S

sneuberg

AWF VIP

Local time

Today, 07:57

Joined

Oct 17, 2014

Messages

3,506

• Nov 23, 2015

• #7

Also see

https://msdn.microsoft.com/en-us/library/office/ff196017.aspx

 

C

crc_love

New member

Local time

Today, 10:57

Joined

Nov 22, 2015

Messages

6

• Nov 23, 2015

• #8

It worked!!! Thank you so much!!!