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"
Annmv888Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
0
als315Commented:
You can use this code:
Dim pathf As String, fname As String, fnameo As String
pathf = "\\corpfs01\global\DATA\OIPC\Access\DB0175 - Weight Watchers\Sent to AP\"
fname = "ChecksSentToAP"
'file names with underlines
fnameo = fname & Format(Date, "mm_dd_yyyy") & ".xlsx"
If Len(Dir(pathf & fname & ".xlsx")) > 0 Then
    FileCopy pathf & fname & ".xlsx", pathf & fnameo
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
    "qryChecksSentToAP", pathf & fnameo, True, "APChecks"
Else
    MsgBox "Original file is missing", vbOKOnly
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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.
1
Annmv888Author Commented:
Thank you so much this is exactly what I needed!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.