TransferSpreadsheet with range

[Guest]

Im trying to import a spreadsheet where the column headings are on row 2 and
the data on row 3+. I have tried (with no luck) the following:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"BindersTaken1", Directory, True, "Binders$2:65536"

The data is on the sheet called "binders" and i would like row 2 on ward
imported.

With the following code i was able to import the correct sheet but i needed
to add a range value to it:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"BindersTaken1", Directory, True, "Binders$"


Thanks for the help!
Ben

 

[Guest]

Ben;
I'd suggest two routes:
First, if it's acceptable to modify the XL data, use VB to open an instance
of XL in your code and delete the first row prior to importing. CRUCIAL, if
you do this make sure you close your XL instance prior to exiting the sub.

Second, import the data as you've done then use a query or code to select
and delete the first record. If there's consistent data in that first row
that is not needed (ie: a Title) then use that as the criteria for your
deletion query.

 

[fredg]

Im trying to import a spreadsheet where the column headings are on row 2 and
the data on row 3+. I have tried (with no luck) the following:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"BindersTaken1", Directory, True, "Binders$2:65536"

The data is on the sheet called "binders" and i would like row 2 on ward
imported.

With the following code i was able to import the correct sheet but i needed
to add a range value to it:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"BindersTaken1", Directory, True, "Binders$"

Thanks for the help!
Ben

You did not correctly identify the Excel file path and file name. Nor
did you correctly identify the worksheet and range. I'll guess you
just want column A imported, not all columns.

Since I'm quite confused with your names, here is generic code.
Substitute your actual path, file, worksheet, and columns. Don't
forget the ! between the worksheet and range.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"TableToImportInto", "CorrectPathToFolder\SpreadsheetName.xls", True,
"WorkSheetName!A2:A"

Note.. the path to the folder must include the drive number, i.e.
"c:\FolderName\FileName.xls".

The above will import data from the named worksheet, column A rows 2
onward (to the last row that contains data, not necessarily 65536).
The data in cell A2 will be the field name as you have written True
for that argument.