more the one range with DoCmd.TransferSpreadsheet

[amir369]

Hi All,
I'm trying to import an Excel sheet, but with more the one rang, couse the columns aint sequence.
for example:
DoCmd.TransferSpreadsheet acImport, , "DailyRprzntvRpt", _
Application.CurrentProject.Path & "\DailyRprzntvRpt.xls", True, "(A1:C18, F1:J18)"

I'm getting an error, that saies that the range is not recognized by Access.

Any idea?

[ADezii]

@amir369
I'm not 100% sure on this one, but I think you can only perform the Import on a single, specified Range as in:

DoCmd.TransferSpreadsheet acImport, , "DailyRprzntvRpt", _
Application.CurrentProject.Path & "\DailyRprzntvRpt.xls", True, "A1:C18"

[Stewart Ross]

ADezii is spot-on - you cannot use a split range with TransferSpreadsheet under any circumstances. If you look up the the help information for the TransferSpreadsheet action it is clear about what a range is:

Range
The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax does not work in Access 97 or later). If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7. Note When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

Why not just import all columns and ignore the additional columns D and E imported into your table? You are not obliged to refer to them in whatever subsequent queries you use on your Excel-imported data if you do not need to.

-Stewart