asked on

Transfer Access query to existing Excel spreadsheet save a new file without changing existing file.

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.

Query: qryChecksSentToAP
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"

als315

You can't save file with slash in name:
ChecksSentToAPmm/dd/yyyy.xlsx
You can use dots or underline
ChecksSentToAPmm.dd.yyyy.xlsx
ChecksSentToAPmm_dd_yyyy.xlsx
https://msdn.microsoft.com/en-us/library/windows/desktop/aa365247(v=vs.85).aspx

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Using dots as separators in a filename is not good practice. Antivurus software may see the file as having double extension and quarantine it when shared.

A good format that I have used us yyyymmdd

When the files are sorted in Windows explorer they will always be in chronological order.

Another option for the model would be to run it from Excel and have Excel pull the data from Access and then save as a new file with current date.

The data can be populated using a data connection query then the vba would be fairly simple; refresh query, save file.

Annmv888

ASKER

Thank you so much this is exactly what I needed!