All I have code that Im using for user to click a button and export data to excel. It works great. But I have the path to their c drive hard coded. Is there a way to code the path so each time they export the data; it changes the name of the spreadsheet? As it stands now; if they export data today and don't change the name of the spreadsheet before they export data again; it will over write the existing spreadsheet. Heres the code:
Code:
stDocName = "C:\Reports\Reports.xls"
' Set the value of the parameter.
Select Case Me.cboDivision.Column(1)
Case Is = "SP"
strnewquery = "qrySPReports"
Case Is = "LTL"
strnewquery = "qryLTLReports"
Case Is = "DTF"
strnewquery = "qryDTFReports"
Case Else
strnewquery = ""
MsgBox "You didn't select a report. Please try again", vbExclamation, "No Valid Report"
End Select
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strnewquery, stDocName
MsgBox "Report ready. Please check your Report folder on C drive. Also, please be sure to change the name before running it again."
Thanks