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

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.

<[email protected]> wrote in message
news:[email protected]...
>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
>