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?
 
als315Connect With a Mentor Commented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.