Excel Import - the second worksheet can't be found

[OfficeDummy]

Hello, everyone!

I need to import an Excel table consisting of two worksheets named 'ISKosten' and 'ITKosten'. The first worksheet is imported flawlessly.

However, Access doesn't seem to recognise the second sheet. After this piece of code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "IT", vrtSelectedItem, False, "ITKosten!A1:I80000"

I get error '3011': the Microsoft-Jet-Database modul couldn't find the object 'ITKosten$A1:I80000'.

WHY not? Everything works with the first worksheet!

Any help would be very much appreciated!

OfficeDummy

[Jim Doherty]

Hello, everyone!

I need to import an Excel table consisting of two worksheets named 'ISKosten' and 'ITKosten'. The first worksheet is imported flawlessly.

However, Access doesn't seem to recognise the second sheet. After this piece of code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten!A1:I80000"

I get error '3011': the Microsoft-Jet-Database modul couldn't find the object 'ITKosten$A1:I80000'.

WHY not? Everything works with the first worksheet!

Any help would be very much appreciated!

OfficeDummy

leave the first sheet syntax as it is but put place a dollar sign in your syntax when you wish to import the second sheet you need two transferspreadsheet commands here remember one for the first sheet and any extra sheets need the dollar denotation for a sheet without the exclamation separator prior to the range

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten$A1:I80000"

Jim :)

[OfficeDummy]

leave the first sheet syntax as it is but put place a dollar sign in your syntax when you wish to import the second sheet you need two transferspreadsheet commands here remember one for the first sheet and any extra sheets need the dollar denotation for a sheet without the exclamation separator prior to the range

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten$A1:I80000"

Jim :)

Hi, Jim!

It still doesn't work. :( I get the same error. I tried changing the range to A1:I2000, but no success.

Any suggestions?

[Jim Doherty]

Hi, Jim!

It still doesn't work. :( I get the same error. I tried changing the range to A1:I2000, but no success.

Any suggestions?

ok this works 'flawlessly' on my machine amend the path to the xls file accordingly

 
vrtSelectedItem = "D:\ITKosten.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten!A1:I10"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ISKosten!A1:I10"
 

Jim :)

[OfficeDummy]

ok this works 'flawlessly' on my machine amend the path to the xls file accordingly

 
vrtSelectedItem = "D:\ITKosten.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten!A1:I10"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ISKosten!A1:I10"
 

Jim :)

But I thought vrtSelectedItem is already path direction, because I choose the Excel file from the FileDialog window. And I can't give a fixed path in the code because those tables are created weekly, and have therefore to be imported on a weekly basis...

Anyway, everything works miraculously now, even though I haven't changed a single thing neither in the code nor in the Excel map. (????)

It'd be still interesting to find out, what the problem was.

Best,
OfficeDummy

[Jim Doherty]

But I thought vrtSelectedItem is already path direction, because I choose the Excel file from the FileDialog window. And I can't give a fixed path in the code because those tables are created weekly, and have therefore to be imported on a weekly basis...

Anyway, everything works miraculously now, even though I haven't changed a single thing neither in the code nor in the Excel map. (????)

It'd be still interesting to find out, what the problem was.

Best,
OfficeDummy

Ask Bill Gates :)).. fact is vrtSelected item is a variable having an assigned value (and logically I agree with you) should contain the path to the relevant excel file whether this be hard coded or passed by the filedialog method, provided it identifies the file, this should be sufficient the only variances here are the actual targetted sheet within the file itself as required by the transferspreadsheet method

Regards

Jim :)