MS Access VBA 导出查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1446608/
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
MS Access VBA Export Query results
提问by zSynopsis
I need help coming up with a method to allow a user to export a query's results to an xls file on a button click event. I've tried using an Output To macro, but it doesn't work for a query containing 30,000+ records.
我需要帮助提出一种方法，允许用户在按钮单击事件上将查询结果导出到 xls 文件。我试过使用输出到宏，但它不适用于包含 30,000 多条记录的查询。
Thanks in advance
提前致谢
回答by Tim Lentine
You might want to consider using Automation to create an Excel spreadsheet and populate it on your own rather than using a macro.
您可能需要考虑使用自动化来创建 Excel 电子表格并自行填充它，而不是使用宏。
Here's a function I have used in the past to do just that.
这是我过去使用的一个函数来做到这一点。
Public Function ExportToExcel(FileToCreate As String, ByRef rst As ADODB.Recordset)
'Parms: FileToCreate - Full path and file name to Excel spreadsheet to create
' rst - Populated ADO recordset to export
On Error GoTo Err_Handler
Dim objExcel As Object
Dim objBook As Object
Dim objSheet As Object
'Create a new excel workbook; use late binding to prevent issues with different versions of Excel being
'installed on dev machine vs user machine
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Add
'Hide the workbook temporarily from the user
objExcel.Visible = False
objBook.SaveAs (FileToCreate)
'Remove Worksheets so we're left with just one in the Workbook for starters
Do Until objBook.Worksheets.Count = 1
Set objSheet = objBook.Worksheets(objBook.Worksheets.Count - 1)
objSheet.Delete
Loop
Set objSheet = objBook.Worksheets(1)
rst.MoveFirst
'Use CopyFromRecordset method as this is faster than writing data one row at a time
objSheet.Range("A1").CopyFromRecordset rst
'The UsedRange.Rows.Count property can be used to identify the last row of actual data in the spreadsheet
'This is sometimes useful if you need to add a summary row or otherwise manipulate the data
'Dim lngUsedRange As Long
'lngUsedRange = objSheet.UsedRange.Rows.Count
'Save the spreadsheet
objBook.Save
objExcel.Visible = True
ExportToExcel = True
Err_Handler:
Set objSheet = Nothing
Set objBook = Nothing
Set objExcel = Nothing
DoCmd.Hourglass False
If Err.Number <> 0 Then
Err.Raise Err.Number, Err.Source, Err.Description
End If
End Function
回答by JeffO
Can you use VBA?
你会用VBA吗？
Intellisense will help you, but get started with:
Intellisense 将为您提供帮助，但请从以下方面入手：
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "my_query_name", "C:\myfilename.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "my_query_name", "C:\myfilename.xls"
Note: you may have a different Excel version "my_query_name" is the name of your query or table you'll need to set the file location to the appropriate location\name .extension
注意：您可能有不同的 Excel 版本“my_query_name”是您的查询或表的名称，您需要将文件位置设置为适当的位置\名称 .extension
More Info: http://msdn.microsoft.com/en-us/library/bb214134.aspx
