How do I import Excel table into Access (1 Viewer)

[t00ley]

Hi All,

I appreciate there are numerous posts on importing from Excel, but I've not yet managed to find one that solves my problem.

How do I important and excel table with headers into Access (If its possible, as Excel tables are dynamic ranges)?

I can import into a new temporary table using the following, but both methods have issues:

A.Full Sheet: The following imports but as the table column header starts on cell A10, the imported fields names are wrong, so a subsequent query that copies from the temporary table to a main data table fails (note the excel table column headers are named as per the Access table field names):

DoCmd.TransferSpreadsheet acImport, , TableName:=myTempTableName, FileName:=myFullFilePathAndName, HasFieldNames:=True, Range:=""

B:A range: The following imports with but includes the table totals & some extra blank rows (The easier option at the moment as field names are imported correctly and I can delete the total and blank rows from the temp table before copying to the main data table):

DoCmd.TransferSpreadsheet acImport, , TableName:=myTempTableName, FileName:=myFullFilePathAndName, HasFieldNames:=True, Range:=mySheetName!A10:P

Whilst B is currently workable (assuming the last used excel column is always P) , I'd like to understand if there's a better way so can future proof etc?

Some issues to consider:
1.The database is in the cloud (accessed via Remote Desktop Connection) and users mapped drives are inconsistent, so I use the transferspreadsheet method rather than a defined import specification (we did initially try using import specifications, by using VBA to change the spec XML, but this wasn't reliable enough).
2.I have no control over the Excel file, as is an extract from our ERP produced via Atlas6.
3.The Excel table doesn't have a specific named range, so I've tried referencing as mySheetName!MyAtlasTable[#All], but it doesn't get recognised (probably due to being a dynamic range)?

Any idea's?

Thanks

Tooley

 

[Ranman256]

if you use a macro, it lets you pick the options
the macro does it right.
edited.

 

[t00ley]

HI Ranman,

Thanks for the quick response.

Firstly, I assume you meant acImport, so the query parameter isn't needed?

Unfortunately, the VB method fails as stated in my original post & the macro method also fails, the error message is..."The MS Access engine could not find the object....etc'. I've manually created a fixed named range which works, so I assume this approach doesn't work with dynamic named ranges, which I believe excel tables are, hence I'm trying to establish if there's another way?

Thanks

 

[Ranman256]

oops, sorry , I used acExport.
docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12,table ,filename,true

you use table ,not query. you can use query, but be careful. it wont always work w queries.