MS Access VBA将查询输出转换为Excel格式，但不保存到任何地方

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文件。我不需要文件保存在任何地方。

3 个解决方案

#1

You can open up your file without saving it by creating an Excel instance (or grabbing an existing one) and using the CopyFromRecordset function of the Excel.Range object.

您可以打开您的文件，而不需要创建一个Excel实例(或抓取一个现有的实例)，并使用Excel的CopyFromRecordset函数。对象范围。

This assumes your data are in an ADO recordset. You need to have references to Microsoft Excel XX.0 Object Library and 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对象库和Microsoft ActiveX数据对象X。X库’(如果您正在使用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 WasANewInstanceReturned to 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还没有打开。我使用wasanewinstancereturn来计算如何在最后清理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 = False before doing any of this and ApXL.ScreenUpdating = True at the end.

我通常设置ApXL。screen update = False，然后再做这些和ApXL。屏幕更新=最后为真。

I'll let you stitch this together for your needs.

我让你把它缝在一起以满足你的需要。

#2

The file must be 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")

编辑:获取临时文件夹的一种简单方法是使用= environment(“临时”)

#3

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):

我从对excel的访问中打开并导出一个查询。首先，我在excel中创建了一个工作表并保存了它。然后我在Access的vba部分创建了一个模块(2013):

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

希望这能有所帮助，这是我第一次回答问题。阿罗哈

#1