TransferSpreadsheet method

[JoeA2006]

I am trying to import spreadsheet data into an Access table, when I use the
following code I get a table named LocationInventory with a header row and
no data. If I use the default spreadshseet type parameter, I get an error
"External Table not in expected format.

Private Sub cmdImportData_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, _
"LocationInventory", "P:\Common\Store Inventories\InvReportImport.xls",
True, "A1:K1"

End Sub
Of course if I use the import wizard it imports correctly.
What is going on?

 

[Nikos Yannacopoulos]

Joe,

Your code specifies that range "A1:K1" of the spreadsheet is to be
imported, i.e. (part of) the first row only. You need to either expand
the range to include all the data, or use the name of the Sheet instead
of a range, so as to import all data in it, such as:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, _
"LocationInventory", "P:\Common\Store Inventories\InvReportImport.xls",
True, "Sheet1"

if Sheet1 is the actual name of the sheet.

Also, check for available options for the type argument, and use the
Excel type with the highest number available, as I suspect you are using
an old version (which is not your problem here, though).

HTH,
Nikos

 

[JoeA2006]

Nikos
The import works fine using the range as you showed me, but when I try to
use the name of the sheet I get an error 3011 ... could not find the object
'1-25-07' using this code
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, _

"LocationInventory", "P:\Common\Store Inventories\InvReportImport.xls",
True, "1-25-07"

I also tried it renaming the sheet "Sheet1" but got the same error.




The tab on the sheet says "1-25-07",is that not the sheet name? I need to
use the sheet name because the range could be different each time.

 

[JoeA2006]

Nikos
Actually,I left out the range arguement, the data imported fine.I thought I
had already tried that.
Thanks