Hello - I have an Excel workbook that has a complex structure, and so I can't just import the whole thing into Access using VBA. Rather, I want to import certain rows into a pre-existing Access table (all Cols exist in Access, but all Cols may or may not exist in Excel). I have coding working to import one cell at a time, but as you can imagine, this takes too long. Is there a more efficient way to grab the whole table of data, or at least a row at a time? Thanks!!!
Quickly copy Excel rows to Access
- Thread starter CedarTree
- Start date
- Local time
- Today, 03:31
- Joined
- Feb 19, 2013
- Messages
- 17,059
Perhaps use sql?
Something like this to get you started
SELECT *
FROM (SELECT * FROM [sheet1$D25:F40] AS xlData IN 'C:\path\XLfile.XLSX'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes] WHERE True) AS XL;
change sheetname/range/path/filename to suit. Set HDR=Yes if the first row contains headers. I've included a criteria you can modify to reduce the number of records if required otherwise you can remove
From this you can convert to an update or append query as required. Utilise functions like Val, CDbl, cCurr etc to change datatypes if required
Something like this to get you started
SELECT *
FROM (SELECT * FROM [sheet1$D25:F40] AS xlData IN 'C:\path\XLfile.XLSX'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes] WHERE True) AS XL;
change sheetname/range/path/filename to suit. Set HDR=Yes if the first row contains headers. I've included a criteria you can modify to reduce the number of records if required otherwise you can remove
From this you can convert to an update or append query as required. Utilise functions like Val, CDbl, cCurr etc to change datatypes if required
Similar threads
