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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:48:49  来源:igfitidea点击:

MS Access VBA Export Query results

ms-accessvbaexport

提问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

更多信息:http: //msdn.microsoft.com/en-us/library/bb214134.aspx