I’m looking for help with a file I created by exporting a query from Access to a specific range in an existing Excel file. I am trying to do everything from a button (on click event) in Access.
Excel Spreadsheet Location: \\corpfs01\global\DATA\OIPC\Access\DB0175 - Weight Watchers\Sent to AP
Excel Spreadsheet Name: ChecksSentToAP.xlsx
Excel Named Range: APChecks ($A1:$Q200)
Workbook Name: APChecks
I’m not a programmer and at first I was able to overlay the file each time but now I have to retain them. The code below transfers data from an Access query to a named range in an existing workbook. I have formulas inside this Excel spreadsheet that will calculate once the data is transferred. My problem is I need the spreadsheet to be saved as ChecksSentToAPmm/dd/yyyy.xlsx(this is done weekly and needs to be retained) and no longer be overlayed and the original (ChecksSentToAP) to remain blank with just the formulas so it can be used each time. Any help I can get would be greatly appreciated.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
"qryChecksSentToAP", "\\corpfs01\global\DATA\OIPC\Access\DB0175 - Weight Watchers\Sent to AP\ChecksSentToAP.xlsx", True, "APChecks"