Excel to Access extraction

  • Thread starter Thread starter U4Canoe
  • Start date Start date
U

U4Canoe

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
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro that extracts specific data from multiple worksheets 0
Extracting Excel2007 data from structured Storage 0
Excel 2003 VBA In directory Open Spreadsheets Extract data 1
Data Extract from Multiple Worksheets Macro 4
Easy way to extract data and put into Excel? 1
Retrieve data from encrypted Access 1
Macros to extract data frommultiple workbooks to a master workbook 0
Passing data to an event handler 3

Back
Top