TransferSpreadsheet help please ...

[solo7]

I am importing a spreadsheet useing the following code :-

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TblTemp", StrXlsAddress, True, StrXlsSheet

where StrXlsAddress is a string holding the address of the spreadsheet, and StrXlsSheet is the name of a specific worksheet within that spreadsheet.
It's giving me an error which says it cannot find the object which relates to the name of the individual sheet within the spreadsheet.

Anyone have any clues please?

solo7

 

[LScharf]

My Access 2000 database produced this:

DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", "mySpreadsheet.xls", True, "myRange"

The last value, "myRange", denotes a named range in the spreadsheet, NOT a sheet name. Try creating a named range in your sheet, then use that name in your VBA.

 

[solo7]

Named ranges would be nice, but I have a large number of old spreadsheets to import. I really would like to use the name of the spreadsheet 'sheet' if I could ...

solo7