Import from Excel using Import Spec

[sumdumgai]

Is it possible to import a spreadsheet into Access using an import specification? I've created import specifications to successfully import spreadsheets into Access and I would like to automate those imports using VBA. But, I cannot find the 'DoCmd.TransferSpreadsheet' syntax that'll work. I do not want to convert the spreadsheets to .txt or .csv.

Thanks.

 

[NickHa]

Is there any particular reason you want to use an import specification? You can transfer a spreadsheet like this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Import Table", "C:\ExcelFile.xlsx", True, "ImportRange"

where ImportRange is a named range in the file. This does not use the import specification.

 

[gemma-the-husky]

how have you created importspecs for spreadsheets? are you sure?

you create import specs for csv's and txt files. when you imprt spreadsheets, access makes its own rules up, gives you little control, and often gets it wrong, in my experience.

 

[sumdumgai]

There are import specifications created and saved by other people, and it looks like these work with Excel spreadsheets because I've run them manually successfully. Since I do not know any way to view these specifications, I was hoping to simply write some code in VBA to automate their running. Thanks.