使用 VBA 将数据从 MS Access 导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5679625/
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
Exporting data from MS Access to Excel using VBA
提问by Pablo
I have a table in MS Access, which has the following data to be exported to excel
我在 MS Access 中有一个表，其中有以下数据要导出到 excel
- Release numbers
- Test cases
- Results
- 版本号
- 测试用例
- 结果
After exporting to Excel I want to have distinct release numbers as rows starting from A2 and distinct test case name as columns starting from B1. There might be couple thousands records. Then each cell will be set to result tag. Additionally will need some fancy coloring/bordering stuff.
导出到 Excel 后，我希望将不同的版本号作为从 A2 开始的行，并将不同的测试用例名称作为从 B1 开始的列。可能有几千条记录。然后每个单元格将被设置为结果标签。此外将需要一些花哨的着色/边界的东西。
The question - is it possible to do this using VBA in Access and if yes what is the way to go? Any hint, sample, example, resource would be appreciated... I've googled but the most thing I came accross is DoCmd.TransferSpreadsheetor DoCmd.OutputTowhich I believe will not do what I want. Saw some examples with CreateObject("Excel.Application")but not sure what are limitations and performance using this way.
问题 - 是否可以在 Access 中使用 VBA 来做到这一点，如果是的话，该怎么做？任何提示，示例，示例，资源都将不胜感激......我已经用谷歌搜索过，但我遇到的最多的事情是DoCmd.TransferSpreadsheet或者DoCmd.OutputTo我认为不会做我想做的。看到了一些示例，CreateObject("Excel.Application")但不确定使用这种方式有哪些限制和性能。
回答by Shawn
I don't know if it would work for your case, but you might try adding the VBA code to an Excel document rather than the Access database. Then you could refresh the data from the Excel file and add the formatting there much easier. Here is one example:
我不知道它是否适用于您的情况，但您可以尝试将 VBA 代码添加到 Excel 文档而不是 Access 数据库。然后，您可以刷新 Excel 文件中的数据并在其中更轻松地添加格式。这是一个例子：
(Or see other examples at http://www.exceltip.com/exceltips.php?view=category&ID=213)
（或在http://www.exceltip.com/exceltips.php?view=category&ID=213 上查看其他示例）
Again, it may not work for your case, but it may be an option to consider. Essentially, instead of pushing from Access, you would pull from Excel.
同样，它可能不适用于您的情况，但它可能是一个可以考虑的选择。本质上，不是从 Access 推送，而是从 Excel 拉取。
回答by Fink
Yes, there are many cases when the DoCmd.TransferSpreadsheet command is inadaquate.
是的，在很多情况下 DoCmd.TransferSpreadsheet 命令不合适。
The easiest way is to reference the Excel xx.x Object model within Access (Early Binding). Create and test your vba export function that way. Then once you are satisfied with your output, remove the Excel object model reference, then change your objects to use use Late Binding using CreateObject. This allows you to easily have other machines that are using different versions of Excel/Access to use it just the same.
最简单的方法是在 Access（早期绑定）中引用 Excel xx.x 对象模型。以这种方式创建和测试您的 vba 导出功能。然后，一旦您对输出感到满意，请删除 Excel 对象模型引用，然后使用 CreateObject 更改您的对象以使用后期绑定。这使您可以轻松地让其他使用不同版本 Excel/Access 的机器以相同的方式使用它。
Here is a quick example:
这是一个快速示例：
Sub ExportRecordsetToExcel(outputPath As String, rs As ADODB.Recordset)
'exports the past due report in correct formattig to the specified path
On Error GoTo handler:
Const xlUP As Long = -4162 'excel constants if used need to be referenced manually!
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim row As Long
If rs.BOF And rs.EOF Then
Exit Sub 'no data to write
Else
rs.MoveFirst
End If
row = 1
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False 'toggle for debugging
Set oBook = oExcel.Workbooks.Add 'default workbook has 3 sheets
'Add data to cells of the first worksheet in the new workbook.
Set oSheet = oBook.worksheets(1)
Do While rs.EOF = False
oSheet.range("A" & row).value = rs.Fields("MyField").value
'increase row
row = row + 1
Loop
oBook.SaveAs (outputPath)
'tidy up, dont leave open excel process
Set oSheet = Nothing
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing
Exit Sub
handler:
'clean up all objects to not leave hanging processes
End Sub
