从Access读取Excel工作簿 [英] Reading an Excel workbook from Access

问题描述

我希望Access(2007)应用程序读取给定Excel(2007)工作簿的不同工作表中的信息.在互联网上，我看到了很多使用DAO/ADO/Excel对象模型/VBA来执行此操作的示例，我认为哪种方法最好是令人困惑的.

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.

现在，我这样做:

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")

推荐答案

最好的方法很大程度上取决于您想做什么.如果Excel中的数据是结构化的，并且希望导入或操作，通常最好使用带有IN关键字的查询，或更简单地说，是链接Excel命名范围，范围或完整工作表.

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.

通过VBA链接
http://msdn.microsoft.com/zh-cn/library/aa141565(office.10).aspx

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

可以使用ADo模式获得工作表和命名范围的完整列表.

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

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

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

如果您想通过Access通过Excel更新SQL Server，ADO是一个不错的选择.

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

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.

这篇关于从Access读取Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！