Accessing data in Excel Spreadsheet from Access

[JoeSmoe55]

Hello all,

I need help. I have tried to no avail to figure out the Excel libraries and functions. I have data inside an Excel spreadsheet that I want to read only. I was told the following statement would open file:

Excel.Workbooks.Open("C:[...]\file.csv")

This seems to work and returns a workbook. I cannot figure out how to go from workbook to accessing the data. I went through what seems to be every function call in the object browser to no avail. I was also given the "transferspreadsheet" method but could anyone explain what this will do? It is not going to be a straight forward process to import the data. Thank you in advance. Thank you also for your mercy and Grace as I am new to working with Excel spreadsheets.

Joe

 

[PHV]

I was also given the "transferspreadsheet" method but could anyone explain what this will do?
When in access VBE search this method in the object browser and then press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit submit too fast...
But for csv files have a look at the DoCmd.TransferText method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ed2020]

Joe,

Are you familiar with working with recordsets in Access? If so I have created a prototype class that allows you to use Excel sheets as a recordset (including reading from and writing to).

It hasn't been properly tested, so it's probably a little buggy, but you're welcome to have what I've got.

Unfortunately I only have it at work but if you want a copy I can post back here on Monday. Let me know.

Ed Metcalfe.

Please do not feed the trolls.....