CODEWITHSUNDEEP
excelms-accessvba
datakritter
datakritter

Reputation: 600

VBA: How to export two queries to Excel in the same instance of Excel?

In MS Access, I have VBA code that creates two spreadsheets and then opens them. However, my code opens these spreadsheets in different instances of Excel. The spreadsheets refer to one another, and so they need to open in the same instance to work properly. Opening the files together manually from the desktop (in a single instance) works fine (the spreadsheets can find the data in their partner).

How can I ask them to open in the same instance? 

DoCmd.OutputTo acOutputQuery, "CrossTab", "ExcelWorkbook(*.xlsx)", "C:\Users\10331654\Desktop\CrossTab.xlsx", True, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "MyQuery", "ExcelWorkbook(*.xlsx)", "C:\Users\10331654\Desktop\MyQuery.xlsx", True, "", , acExportQualityPrint

Upvotes: 0

Views: 96

Answers (1)

datakritter
datakritter

Reputation: 600

Okay - found the answer. You have to create an instance of excel manually, and then open the worksheets within that:

        Dim ApXL As Object
        Dim xlWBk1 As Object
        Dim xlWBk2 As Object

DoCmd.OutputTo acOutputQuery, "CrossTab", "ExcelWorkbook(*.xlsx)", "C:\Path\To\File1", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "MyQuery", "ExcelWorkbook(*.xlsx)", "C:\Path\To\File2", False, "", , acExportQualityPrint

Set ApXL = CreateObject("Excel.Application")
            Set xlWBk1 = ApXL.Workbooks.Open(C:\Path\To\File1)
            Set xlWBk2 = ApXL.Workbooks.Open(C:\Path\To\File2)

Upvotes: 1

Related Questions