CODEWITHSUNDEEP
excelms-accessvba
mgzgrtn
mgzgrtn

Reputation: 31

Exporting the results of an Access 2013 select query into an Excel 2013 sheet without creating a new worksheet or breaking references

This problem is fairly simple but it's baffling me, especially since this code works perfectly fine in a different application I have made, so I suspect there is some hidden setting somewhere that's causing the problem.

I need to transfer the results of an Access select query into a copy of a template Excel sheet. This template has a few other sheets in it that refer to the sheet that will receive the Access query data, which then do some formatting and calculations with it. All I need is for this single query's results to be transferred to the correct sheet, with no formatting or anything. Overwriting the whole sheet is fine too.

My problem is that, instead of copying to the sheet "qryLineItemsExportBuffer", it creates a new sheet, named "qryLineItemsExportBuffer1" and puts the query results there - so all the references in the workbook are looking in the wrong place. I also cannot use the DoCmd.TransferSpreadsheet [Range] parameter to define a destination sheet, because this does not work when exporting, only while importing.

Here is my code - it 1. opens the template, 2. saves a copy with a new name, 3. transfers the query result, and then 4. opens the copy and recalculates/rebuilds links:

Private Sub cmdExportInvoiceRequestForm_Click()

Dim instExcelTemplate As Object
Dim xlsxInvoiceRequestTemplate As Excel.Workbook
Dim xlsxInvoiceRequestNewName As String
Dim xlsxInvoiceRequestCopy As Excel.Workbook

    xlsxInvoiceRequestNewName = Application.CurrentProject.Path & "\" & CustomerName & "-InvoiceRequest-" & Format(Date, "dd-mm-yyyy") & ".xlsx" 'create the name for the new invoice report
    MsgBox xlsxInvoiceRequestNewName 'user is shown the name and date
    MsgBox Application.CurrentProject.Path 'user is shown the destination folder

    'start an Excel instance and open the template workbook.
    Set instExcelTemplate = CreateObject("Excel.Application")
    Set xlsxInvoiceRequestTemplate = instExcelTemplate.Workbooks.Open(Application.CurrentProject.Path & "\Templates\CustomerInvoiceRequestFormTemplate.xlsx") 'open the invoice report template
    xlsxInvoiceRequestTemplate.SaveAs FileName:=(xlsxInvoiceRequestNewName) 'save the template as a new file in the correct directory
    instExcelTemplate.Workbooks.Close 'close the Excel instance.

    'transfer the qryLineItemsExportBuffer results to the newly created workbook.
    DoCmd.TransferSpreadsheet acExport, 10, "qryLineItemsExportBuffer", xlsxInvoiceRequestNewName
    'start an excel instance and open the new workbook.
    Set instExcelCopy = CreateObject("Excel.Application")
    Set xlsxInvoiceRequestCopy = instExcelCopy.Workbooks.Open(xlsxInvoiceRequestNewName)
    instExcelCopy.CalculateFullRebuild 'recalculate all values and rebuild links in the workbook. This is necessary because it does not do this when opened manually. Can be done in Excel with CTRL+SHIFT+ALT+F9.
    xlsxInvoiceRequestCopy.Save 'save the copy
    instExcelCopy.Workbooks.Close 'close the Excel instance.
    Set xlsxInvoiceRequestCopy = Nothing
    Set instExcelCopy = Nothing
    xlsxInvoiceRequestNewName = ""


End Sub

I also cannot put any code into the Excel template itself, so I can't make it copy the data over from the new sheet.

What am I missing?

Upvotes: 0

Views: 754

Answers (1)

mgzgrtn
mgzgrtn

Reputation: 31

I've found the correct way to use the DoCmd.TransferSpreadsheet method to export the results of Access queries to a specific sheet or range in an Excel worksheet, without creating new sheets. The documentation for this method strongly suggests that you cannot set a Range parameter for exports to Excel from Access, and I have not found any other sources online which mention this capability. But in fact, you can effectively set a destination range for DoCmd.TransferSpreadsheet by using Excel's Name Manager.

Let's say we want to transfer the results of Access query 'qryItemsExport' to a sheet in a workbook, named 'DestinationWorkbook.xlsx', in the same folder as the Access database.

First, go into the workbook's name manager and set a named range called 'qryItemsExport' where you'd like the data to go. Then, use the following VBA in the Access project:

Dim xlsxDestinationWorkbook as String

xlsxDestinationWorkbook = Application.CurrentProject.Path & "\" & "DestinationWorkbook.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryItemsExport", xlsxDestinationWorkbook

The first line declares the workbook name variable as a string. The second line assigns the workbook name variable to the version currently in the same folder as the Access project. The third line exports the data from the query to the destination workbook, for an Excel 2010+ format. Because the command attempts to export to the destination 'qryItemsExport', this goes to the previously named range and places the data in that location, starting in the upper-left-hand-corner. It does not create new sheets or rename existing ones, so references to the sheet being copied to will stay intact.

Upvotes: 3

Related Questions