Excel to Access extraction
I generate 10 Excel worksheets a day that are in a form format that I
have to extract about 200 fields of data into an Access database. The form is fixed format - each worksheet field has different data, but are in the same cell location. I wish to automate the extraction using OLE and am looking for direction on how best to navigate the excel worksheet. I cannot embed any addtional macros within the Excel worksheets, so any code has to be external to Excel and "append" data to the Access database. Any suggestions? Thanks, Prokayaker |
Excel to Access extraction
1) You can write sub procedures in modules other that code ,odule for
sheets. 2) You can add reference to ADODB from tools/references in VBA window (press alt+F11 fro vba window in excel) 3) connect to access database using adodb.connection object 3) Read sheet cells script and use sql statements to append tables in a access database via adodb.conncetion. wrote in message oups.com... I generate 10 Excel worksheets a day that are in a form format that I have to extract about 200 fields of data into an Access database. The form is fixed format - each worksheet field has different data, but are in the same cell location. I wish to automate the extraction using OLE and am looking for direction on how best to navigate the excel worksheet. I cannot embed any addtional macros within the Excel worksheets, so any code has to be external to Excel and "append" data to the Access database. Any suggestions? Thanks, Prokayaker |
All times are GMT +1. The time now is 05:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com