TransferSpreadsheet - Defining Range Syntax?

[dvs]

Hi,

I am trying to auto-import data w/ a single-click from several Excel sheets. A sample line:

DoCmd.TransferSpreadsheet acImport, , "tblTS1", filename, True, Product!A1:H100

where
tblTS1 - tbl where I'm putting the data
filename - c:\test.xls
Product!A1:H100 - range of data I want in sheet Product

I am having problems w/ the "range" portion of Transferspreadsheet (I know it's this portion, because it works when I pre-name the range in Excel). I have tried all sorts of variations on the syntax:

Product!A1:H100
"Product!A1:H100" or 'Product!A1:H100'
"Product" & ! & "A1" & : "H100"
"'Product'" & ! & "A1" & : "H100"
Product & "!" & A1 & ":" H100

I get the error msg:
The MS Jet database engine could not find the object '$:'. Make sure the object exists and that you spell its name and the path name correctly." or "Syntax error"

I wanted to mistake-proof the data transfer, so I did not want to:
- predefine the range in Excel (under Insert\Name\Define)
- have the user enter values in text boxes

Any help w/ the syntax would be greatly appreciated!

Cheers,
dvs

 

[sportsguy]

yes, there is a hidden or previously delete range in your spreadsheet.

you can't see it because in the Excel Name range its been deleted,
so try copying the data to a new spreadsheet and renaming the
ranges.

Then do not import the spreadsheet, but link the spreadsheet
using code, and make sure there are firld headings in Excel,

then create a query to do what you want with the spreadsheet.
I import about 120 spreadsheets with 300 rows each in under two minutes
about once a month, and then i do updates from the excel spreadsheets to the already imported data, in about the same time.

sportsguy