MS Access VBA 将查询输出转换为 Excel 格式但不保存在任何地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24869036/
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 convert query output to Excel format but not saving anywhere
提问by user51819
I've been trying to use transfer spreadsheet methods but they appear to require an output path.
我一直在尝试使用传输电子表格方法，但它们似乎需要一个输出路径。
I just need to find out how to take a given query and simply "open up" an Excel file that contains the query output. I don't need the file actually saved anywhere.
我只需要找出如何进行给定的查询并简单地“打开”一个包含查询输出的 Excel 文件。我不需要实际保存在任何地方的文件。
回答by Brad
You can open up your file without saving it by creating an Excel instance (or grabbing an existing one) and using the CopyFromRecordsetfunction of the Excel.Rangeobject.
您可以通过创建 Excel 实例（或获取现有实例）并使用对象的CopyFromRecordset功能来打开文件而不保存它Excel.Range。
This assumes your data are in an ADO recordset. You need to have references to Microsoft Excel XX.0 Object Libraryand Microsoft ActiveX Data Objects X.X Library` (if you are using ADO. If you use DAO then use whatever DAO reference you need)
这假定您的数据在 ADO 记录集中。您需要引用Microsoft Excel XX.0 Object Library和 Microsoft ActiveX Data Objects XX Library`（如果您使用的是 ADO。如果您使用 DAO，则使用您需要的任何 DAO 引用）
I use this to grab an an Excel app or create a new one is Excel is not open already. I use WasANewInstanceReturnedto figure how I need to clean up the Excel resources at the end. (Obviously I don't want to quit Excel if it is being use by something else).
我用它来抓取一个 Excel 应用程序或创建一个新的 Excel 应用程序尚未打开。我WasANewInstanceReturned过去常常想，最后我需要如何清理 Excel 资源。（显然，如果 Excel 被其他东西使用，我不想退出 Excel）。
Function GetExcelApplication(Optional ByRef WasANewInstanceReturned As Boolean) As Excel.Application
If ExcelInstanceCount > 0 Then
Set GetExcelApplication = GetObject(, "Excel.Application")
WasANewInstanceReturned = False
Else
Set GetExcelApplication = New Excel.Application
WasANewInstanceReturned = True
End If
End Function
Then grab that instance
然后抓住那个实例
Dim ApXL As Excel.Application, WasANewInstanceReturned as Boolean
Set ApXL = GetExcelApplication(WasANewInstanceReturned)
Add a workbook
添加工作簿
Dim wbExp As Excel.Workbook
Set wbExp = ApXL.Workbooks.Add
Grab the first sheet
抓住第一张纸
Dim wsSheet1 As Excel.Worksheet
Set wsSheet1 = wbExp.Sheets(1)
Put your recordset's field names in the first row
将记录集的字段名称放在第一行
Dim fld As ADODB.Field
Dim col As Integer
col = 1
With wsSheet1
For Each fld In rst.Fields
.Cells(1, col).Value = fld.Name 'puts the field names in the first row
End With
col = col + 1
Next fld
End With
Then move the data just below the field names
然后将数据移动到字段名称下方
wsSheet1 .Range("A2").CopyFromRecordset rst
Voila! You have an excel file open, with your data that has not been saved anywhere!
瞧！您打开了一个 excel 文件，您的数据尚未保存在任何地方！
I usually set ApXL.ScreenUpdating = Falsebefore doing any of this and ApXL.ScreenUpdating = Trueat the end.
我通常ApXL.ScreenUpdating = False在做任何这些之前和ApXL.ScreenUpdating = True最后设置。
I'll let you stitch this together for your needs.
我会让你根据你的需要把它缝合在一起。
回答by Patrick Honorez
The file mustbe saved somewhere for Excel to open it.
If the dataset is small enough, you can use copy/paste (no file here). Otherwise, just use the %TEMP% folder for the file location.
该文件必须保存在某处，以便 Excel 将其打开。
如果数据集足够小，您可以使用复制/粘贴（此处没有文件）。否则，只需使用 %TEMP% 文件夹作为文件位置。
Edit:
One simple way to get the TEMP folder is to use =Environ("TEMP")
编辑：
获取 TEMP 文件夹的一种简单方法是使用=Environ("TEMP")
回答by DeLara
I open and export a query from access to excel. First I created a worksheet in excel and saved it. Then I created a module in the vba part of Access (2013):
我打开一个查询并将其从 access 导出到 excel。首先我在excel中创建了一个工作表并保存了它。然后我在 Access (2013) 的 vba 部分创建了一个模块：
Option Compare Database
' Testtoexporttoexcel'
Function ExportQuerytoExcel()
On Error GoTo ExportQuerytoExcel_Err
' Exports the query to excel to a sheet named Nameofyoursheet
DoCmd.TransferSpreadsheet acExport, 10, "nameofyourquery", "yourPath:\nameofyourworkbook", False, "Nameofyour worksheet"
ExportQuerytoExcel_Exit:
Exit Function
ExportQuerytoExcel_Err:
MsgBox Error$
Resume ExportQuerytoExcel_Exit
End Function
-----then add another function that says:
Option Compare Database
Function OpenExcelFromAccess()
'Opens Excel to the chart
Dim MYXL As Object
Set MYXL = CreateObject("Excel.Application")
With MYXL
.Application.Visible = True
.workbooks.Open "Yourpath:\nameofyourworkbook"
End With
'Application.Quit
End Function
hope this helps, this is my first time answering a question. Aloha
希望这有帮助，这是我第一次回答问题。阿罗哈
