Export Query to Excel in VB

[NFL]

Below is a code that works and what I like to do is every time I run the
command the data will be created in a new worksheet? If yes, what will the
code look like? This code is executed from a form. This query will run
every month and will only collect data from the current month.

Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim SavePath As String
Dim strExport As String

SavePath = "C:\MyFolder\MyFilename.xls"
strExport = "MasterQry"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strExport,
SavePath, True


Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

Thank you!

 

[Pendragon]

You could add the year and month to the filename so that every time
TransferSpreadsheet is run, it's saving to a different filename.

Dim strFolder as String
Dim strFile as String

strFolder = "C:\MyFolder\"
strFile = "MyFilename" & Trim(Format(Year(Date), "yyyy")) &
Trim(Format(Month(Date), "mm")) & ".xls"
SavePath = strFolder & strFile