vba 将 Access 交叉表查询链接并导入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19727080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linking and importing an Access crosstab query into Excel
提问by Luke Hsu
How can I import a crosstab query into Excel and also link it?
如何将交叉表查询导入 Excel 并链接它？
These are my steps to import the data from the cross tab query.
Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select data.
这些是我从交叉表查询导入数据的步骤。
数据>>导入外部数据>>新建数据库查询>>MS Access数据源*>>浏览到我的数据库>>选择交叉表查询名称>>选择数据。
I need to link the query because there will be additional information to be inputted. E.g. This month is November and I need to enter the data received for this month into the system.
我需要链接查询，因为会有额外的信息需要输入。例如，本月是 11 月，我需要将本月收到的数据输入系统。
回答by xQbert
Instead of importing the data, In excel:
而不是导入数据，在 excel 中：
- From File Menu select data
- Select From Access object (table or query) you want to pull data from.
- Data loads in excel
- anytime you want updated data, right click on the data and select refresh or select refresh all if multiple links exist.
- 从文件菜单中选择数据
- 选择从要从中提取数据的访问对象（表或查询）。
- excel数据加载
- 任何时候您想要更新数据，右键单击数据并选择刷新或如果存在多个链接，则选择全部刷新。
This assumes the database is in the same physical location place every time and anytime you do a refresh, you have connection to this source.
这假设数据库每次和任何时候刷新都位于相同的物理位置，您可以连接到该源。
回答by George Murphy
For some reason Excel won't present a crosstab query in the list of tables/queries you can select, but it is possible to add it in without VBA, and it will update just like a normal table thereafter, you just have to type in some basic SQL in the connection properties.
出于某种原因，Excel 不会在您可以选择的表/查询列表中显示交叉表查询，但可以在没有 VBA 的情况下添加它，此后它将像普通表一样更新，您只需输入连接属性中的一些基本 SQL。
Instructions are for Excel 2016 & not tested in prior versions.
说明适用于 Excel 2016，未在先前版本中测试。
Create a data link to a different table or query in the same Access database as your Crosstab query (from the usual Data -> From Access).
Click anywhere in the newly shown table and from the Design tab click Properties to open the External Data Properties dialog.
Select the little button next to the "Name: " field, on the right to open the connection properties.
Go to the Definition tab and change the Command type to "SQL"
Enter a basic bit of SQL: SELECT * FROM YourCrossTabQueryName
Hit ok.
在与交叉表查询相同的 Access 数据库中创建指向不同表或查询的数据链接（从通常的数据 -> 从 Access）。
单击新显示的表格中的任意位置，然后从“设计”选项卡单击“属性”以打开“外部数据属性”对话框。
选择右侧“名称：”字段旁边的小按钮以打开连接属性。
转到定义选项卡并将命令类型更改为“SQL”
输入一些基本的 SQL：SELECT * FROM YourCrossTabQueryName
打好。
This should refresh the table with your crosstab shown.
这应该刷新表并显示交叉表。
回答by Tom
You can actually "cheat" with access and Excel and get it done without any code by wrapping your crosstab query in a non-crosstab query.
您实际上可以通过访问和 Excel 来“欺骗”，并通过将交叉表查询包装在非交叉表查询中而无需任何代码即可完成。
First, in Access make a new query "My Non-Crosstab Crosstab"and for the source of the query select your Crosstab query that Excel refuses to see. Output all columns in the table using the * field as the output.
首先，在 Access 中创建一个新查询“我的非交叉表交叉表”，并为查询源选择 Excel 拒绝查看的交叉表查询。使用 * 字段作为输出输出表中的所有列。
This new query will now contain everything in your Crosstab query, but it's not in itself a crosstab query.
这个新查询现在将包含交叉表查询中的所有内容，但它本身并不是交叉表查询。
From Excel do the steps for getting data from Access via query and the new query "My Non-Crosstab Crosstab" will be available for insertion!
从 Excel 执行通过查询从 Access 获取数据的步骤，新查询“我的非交叉表交叉表”将可用于插入！
回答by LeasMaps
I've found that you can't if the backend database is an Access accdb file
我发现如果后端数据库是 Access accdb 文件，则不能
"You should note here that while you can't currently import Crosstab queries from Access, you can import the data that supports the Crosstab as a Pivot Table report (after all, a Pivot Table is essentially Excel's version of an Access Crosstab query)."
“您应该在此注意，虽然您目前无法从 Access 导入交叉表查询，但您可以将支持交叉表的数据作为数据透视表导入（毕竟，数据透视表本质上是 Excel 版本的 Access 交叉表查询）。 ”
You will have to import the source data and then create a pivot table
您必须导入源数据，然后创建数据透视表
回答by LeasMaps
But you can do it with VBA:
但是你可以用 VBA 做到这一点：
Function ImportCrosstab(TheDB As String, TheCrosstab As String, TheSpreadsheet As String)
'requires a reference to the Microsoft Office Access Database Engine 12.0 Object Library
'e.g.ImportCrosstab "C:\Databases\Newdatabase.accdb", "Q_stage_crosstab", "CompliancebyStage"
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim ws As Worksheet
Dim strConnection As String
Dim j As Long
Dim xls As Object 'your Excel.Application with sheet activated
Set ws = ThisWorkbook.Worksheets(TheSpreadsheet)
Set db = OpenDatabase(TheDB)
Set qdf = db.QueryDefs(TheCrosstab)
Set rs = qdf.OpenRecordset
With ws
For j = 0 To rs.Fields.Count - 1
.Cells(1, j + 1).Value = rs.Fields(j).Name
Next
.Range("A2").CopyFromRecordset rs
End With
rs.Close
End Function
