Dear all,

I'm trying to embed an Excel worksheet into an Access database in an OLE field. The operation is generated in the Excel workbook, through VBA. I have set up an ADODB connection to the database, but I don't know how to handle OLE fields. Can anyone, please tell me how to insert the worksheet?
And how to extract, afterwards, the worksheet from OLE field?

Would linking be a better solution?

Thanx,
Mihai

I have found that when using excel and access I always get problems with formatting of text strings and numbers, but that is my problem, however for this solution I would recommend the use of a linked xls file into access, any changes made in the spread are, naturally, reflected in the database. Once the link is active you have a variety of options.

1 use buttons and other control objects to execute MSQuery in excel that passes through statements into Access. My preferred option.
2 use automation from within Excel to control Access.

Tell me which you would prefer to use and I would be pleased to offer any assistance.

cheers

Matthew

Hi Matthew,

thanks for the suggestions. Indeed, I use one of the options (the second one) and collect all the relevant data (financial data) from the Excel files into an Access database.
And I have indeed some problems with format of the data (there are different regional settings for each user).

But my real problem is that I want to keep an entire spreadsheet into the database (it's an evaluation report written by a financial analyst, which don't have a standard layout). So I was thinking about using an OLE field; but I don't know how to handle this with VBA.
Can you give me a clue?

Mihai