Reading an Excel workbook from Access

Question

I want my Access (2007) application to read the information in the different worksheets of a given Excel (2007) workbook. On the internet I see a lot of examples doing this by using DAO/ADO/Excel Object Model/VBA and I think it's rather confusing which way is best.

Now I'm doing it this way:

Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim ExcelWorkSheet As Excel.Worksheet

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWorkbook = ExcelApp.Workbooks.Open("C:\Temp\test.xls")

For Each ExcelWorkSheet In ExcelWorkbook.Worksheets
    MsgBox ExcelWorkSheet.Name
Next

ExcelWorkbook.Close (False)
ExcelApp.Quit
Set ExcelWorkbook = Nothing
Set ExcelApp = Nothing

(As an example I loop through the worksheets and print their names)

Is this the correct way of doing this? And is this the most efficient way? (if there is a "most efficient way")

What do you want to do with the worksheet once you've read it? That will tend to restrict the choices for how to do it. — David-W-Fenton
– David-W-Fenton, Commented Nov 30, 2010 at 0:58

Fionnuala · Accepted Answer · 2010-11-29 14:06:41Z

1

What is best depends very much on what you want to do. If the data in Excel is structured, and you wish to import or manipulate, it is usually best to use a query with the IN keyword, or more simply, link Excel named ranges, ranges, or complete worksheets.

Link via VBA
http://msdn.microsoft.com/en-us/library/aa141565(office.10).aspx

It is possible to get a full list of worksheets and named ranges with ADo Schemas.

Query
http://stackoverflow.com/questions/2689087/where-in-query-with-two-recordsets-in-access-vba/2689151#2689151

If you wish to update say, SQL server from Excel via Access, ADO might be a good choice.

ADO
http://stackoverflow.com/questions/2086234/query-excel-worksheet-in-ms-access-vba-using-adodb-recordset/2086626#2086626

Automation, as illustrated in your question, is usually only useful if you wish to do a lot of formatting and / or updating of single cells and small ranges.

edited Nov 29, 2010 at 14:06

answered Nov 29, 2010 at 13:55

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wndrs Over a year ago

Thanks for your comprehensive answer. In my case I want to read certain ranges of the worksheets and process this data. I have no neat columns and rows with the same kind of data, otherwise I had used DoCmd.TransferSpreadsheet

Collectives™ on Stack Overflow

Reading an Excel workbook from Access

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related