My best attempt so far has been to use DoCmd.TransferSpreadsheet acExport in order to export four separate queries to an excel workbook. These four sheets feed a fifth sheet that has formulas built into it. However, I need the queries to replace the content last export on each sheet, not create new sheets. I do not want to create a linked file as I need to be able to share the contents of the excel file with others, some of which probably do not have Access.
Here is the code I have so far: Any ideas?
Private Sub Command78_Click()
Dim strFullyQualifiedFilename As String
strFullyQualifiedFilename = "C:\[Destination String].xlsx"
DoCmd.TransferSpreadsheet acExport, , "qryExpenseActual_ExcelExport", strFullyQualifiedFilename
DoCmd.TransferSpreadsheet acExport, , "qryExpenseBudget_ExcelExport", strFullyQualifiedFilename
DoCmd.TransferSpreadsheet acExport, , "qryIncomeActual_ExcelExport", strFullyQualifiedFilename
DoCmd.TransferSpreadsheet acExport, , "qryIncomeBudget_ExcelExport", strFullyQualifiedFilename
DoCmd.TransferSpreadsheet acExport, , "lktEventsQ_ExcelExport_EventFiltered", strFullyQualifiedFilename
DoCmd.TransferSpreadsheet acExport, , "qryPercentageofIncome_ExcelExport_EventFilter ed", strFullyQualifiedFilename
MsgBox "File has been exported to " & strFullyQualifiedFilename
End Sub