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):
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):
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
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):
Code:
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):
Code:
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