Get Excel Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 04 Feb 2004 12:11 GMT

All
Using VB 6.0, I need to get the data out of an Excel sheet and insert it in an existing Access table. I'd like to do this strictly through VB rather than start an instance of Access and use the TransferSpreadsheet function. Any pointers
Thanks,

Reply to this Message

Ronald Dodge - 24 Feb 2004 20:56 GMT

Provided you are working with Office 2000 or later, take a look at the ADO
coding. I am still in the learning process of ADO coding myself as I'm now
digging into the OLE DB coding (currently on Chapter 7 of 28 chapters).
However, both, Excel and Access allows for ADO coding, so you could use the
following:

Create/Open a connection object to the Excel workbook
Create/Open a recordset to the data in the Excel worksheet
Create/Open a connection object to the Access DB file that contains the data
Create/Open a recordset for the data that will be used to add to Access.

Between these 2 recordsets, copy the data from Excel and add to Access.
Since I am still learning ADO coding, I'm not sure of all the details that's
involved. However, you will need to use the Jet 4.0 OLE DB provider for the
Access File via ADO coding.

Signature

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

> All,
> Using VB 6.0, I need to get the data out of an Excel sheet and insert it in an existing Access table. I'd like to do this strictly through VB rather

than start an instance of Access and use the TransferSpreadsheet function.
Any pointers?

> Thanks,

Reply to this Message

VB Forum / COM / February 2004

Get Excel Data