使用 Access VBA 2010 操作 Excel 数据
在 Access 2003 中，我曾经导入第三方提供给我们的特殊 xls 文件。我为此使用了 ADODB，效果非常好。
ADODB 不再是 Access 2007/2010 中的一个选项，而且我认为您无法使用 DAO 或 ADO“查询”电子表格。
那么，还有其他选择吗？
In Access 2003 I used to import special xls files provided to us by a third party. I used ADODB for this, which worked perfectly.
ADODB is no longer an option in Access 2007/2010, and I don't think you can "query" a spreadsheet using DAO or ADO.
So, are there any alternatives?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您确实可以使用 ADO 和 Access 的 OLE DB 提供程序（Jet，等等）来查询 Excel 工作簿。有关更多详细信息，请参阅这篇 MSDN 文章。
事实并非如此。我认为您一定误解了某些东西，考虑到互联网上围绕 Access 中的“ADO 与 DAO”有很多废话，这并不奇怪。
我不太确定当 ADO classic 被引入 Access2000 社区时发生了什么，但似乎很多老前辈都感到受伤了。 Microsoft 的营销信息是这样的：“DAO 现在是旧的工作方式，而 ADO 是新的工作方式。” MS 有意采取的举措是，新引擎功能通常只能通过 ADO 获得。
正如常见的情况，当巩固地位受到威胁时，Access+DAO 的长期粉丝就会发起反击。这通常表现为“扔足够的泥，有些会粘住”。新来到 AccessLand 的人可能会对这些混杂的信息感到困惑。在新闻组中，有抱负的 Access MVP 效仿现有 Access MVP 的反 ADO 立场。
在 200 和 2007 版本之间，Access 产品在引擎方面变得有些陈旧，其责任已移交给 SQL Server 团队，他们出于所有实际目的放弃了它：他们试图使其符合条目级标准 SQL-92 受到 Windows 团队的阻挠，该团队的组件依赖于与标准背道而驰的功能。 ADO经典队也同样被解散。 Visual Basic COM 与 Office 套件有效地共享 VBA 库，但它被 VB.NET 淘汰，而 ADO.NET 诞生了，
Access2007 团队再次进行了变革。他们通过私有分支从 SQL Server 和 Windows 团队手中夺回了引擎。他们放弃了用户级安全性，大概是因为维护起来太复杂了。他们添加了可能违反 1NF 的多值类型。勇敢的东西！虽然某些功能可以通过为 Access 编写新的 OLE DB 提供程序来容纳在 ADO classic 中，但其他功能则不能。例如，要完全支持（例如通过 SQL 更新）ADO 中的多值数据类型，就需要更改 Access 团队不拥有的 ADO 经典库。
由于 DAO 归 Access 所有，因此新版本（称为 ACEDAO）获得了新 OLE DB 提供程序与旧 ADO 经典库所缺乏的功能（尽管需要 ADO 才能在 64 位计算机上使用该引擎）。然而，他们没有做的是回顾性地修复 DAO 以适应仅包含在 ADO 中的 Access2000 时代的功能。
自然，Access 团队将 ACEDAO 提升为一等公民。当然，Access+DAO 的长期粉丝很高兴：“ADO 已被弃用，取而代之的是 ADO.NET，”他们欢呼雀跃。事实上，最终结果是好坏参半。请参阅此帖子 了解完整详细信息。
You can indeed query an Excel workbook using ADO with the OLE DB provider for Access (Jet, whatever). See this MSDN article for more details.
That is not the case. I think you must have misunderstood something, which is hardly surprising considering there is a lot of nonsense out there on the interwebs surrounding "ADO vs DAO" in Access.
I'm not exactly sure what happened when ADO classic was introduced to the Access2000 community but it seems that a lot of old-timers were left with hurt egos/pride. The marketing message from Microsoft was in effect, "DAO is now the old way of working and ADO is the new." In a deliberate move by MS,, new engine features were generally only available via ADO.
As is often the case when entrench positions are threatened, there was a counter-movement by long time Access+DAO fans. This often took the form of, "throw enough mud and some will stick." People newly arrived in AccessLand would be confused by the mixed messages. In the newsgroups, aspiring Access MVPs emulated existing Access MVPs' anti-ADO stance.
Between the 200 and 2007 releases, the Access product became IMO somewhat stale as regards the engine, responsibility for which had been handed to the SQL Server team, who had for all practical purposes given up on it: their attempts to make it comply with entry-level Standard SQL-92 were thwarted by the Windows team, whose components relied on features that flew in the face of the Standard. The ADO classic team had similarly been disbanded. Visual Basic COM, which effectively shared VBA libraries with the Office suite, was killed off in favour of VB.NET and ADO.NET was born,
The Access2007 team shook things up again. They reclaimed the engine from the SQL Server and Windows teams by taking a private branch. They ditched user level security, presumably because it was too complex for them to maintain. They added multi-valued types that arguably violate 1NF. Brave stuff! While some features could be accommodated in ADO classic by authoring a new OLE DB provider for Access, others could not. For example, full support (e.g. updating via SQL) for multi-valued data types in ADO would require a change to the ADO classic libraries which the Access team does not own.
Because DAO was owned by Access, the new version (called ACEDAO) got features the new OLE DB provider with the old ADO classic libraries would lack (although ADO was required to use the engine on 64 bit machines). However, what they did not do was to retrospectively fix DAO to accommodate the Access2000-era functionality that went into ADO only.
Naturally, the Access team promoted ACEDAO as first class citizen. Of course, the long-time Access+DAO fans were delighted: "ADO has been deprecated in favour of ADO.NET," they crowed. In truth, the net result is a mixed bag. See this thread for full details.
在 Access 中的 VBA 项目中，单击“工具/引用”，然后找到“Microsoft Excel 14.0 对象库”。这将使您能够访问所有 VBA Excel 对象，以便您可以对电子表格执行任何您想要的操作。
正如其他人所说，在工作表上运行查询不是一种选择。如果您习惯于从数据库角度思考，请考虑对模板进行检查，以确保标题匹配，这样您就很可能拥有一个正在读取的良好文件。然后，一旦您验证了文档设置，SQL 就会将包含记录的行插入到 Access 数据库的表中，然后从那里运行查询。请在此处检查如何编写 SQL 代码：
SQL 语句帮助
如果您想获得更高级的如果您的文件很小，您的 VBA 可以通过使用 setter/getter 设置对象来运行搜索，并将记录存储到集合类中，然后使用您要查找的数据迭代它。
我在一些项目中这样做，我知道列表很小，当我真的只想读取数据并运行一些真正的基本计算（例如计算特定小部件的数量并将计数放在一份报告。集合类非常适合于此。以下是有关集合的更多信息的好链接：
Visual Basic 中的集合
祝你好运！
In your VBA project in Access click on Tools / References then find "Microsoft Excel 14.0 Object Library". This will give you access to all of the VBA Excel objects so you can do whatever you want to the spreadsheet.
As others have said running queries on the sheet is not an option. If you're used to thinking in terms of databases think about running a check on the template to make sure the headings match so you more than likely have a good file you're reading from. Then once you've verified the document setup SQL inserts on the lines with records on them into a table in an Access Database then run your queries from there. Check here for writing your SQL code:
SQL Statement Help
If you want to get more advanced in your VBA if your files are small you could run searches by setting up an object with setters/getters and store the records into a collection class then iterate across it with the data you're looking for.
I do this in some projects where I know the lists are small, gets away from reading/writing to tables when I really just want to read the data and run some real basic calculations like count the number of a particular widget and place the count on a report. Collection classes are great for this. Here's a good link for some more info on collections:
Collections in Visual Basic
Good luck!