Macro: OutputTo file naming Question

[RCMetrics]

When using the "outputTo" ...

Object Type is a query

Would like the output file name to add the current date automatically and not overwrite the existing files.

So if I set the output file name to be Report.xls

I would like any following report generated by the macro to be:

i.e.

Report20051108.xls
Report20051109.xls

Could be date, number... anything...I just want to know how to set an increment of 1 or current date to the file name.

Let me know.

Thanks

 

[Joe4]

I don't think you can do it via a Macro, but you can do it via VBA.

Highlight your macro and from the Tools menu, select Macro, then Convert Macros to Visual Basic. This will convert your Macro to VBA.

Then, in VBA, you can make your report name a variable that you attach the date onto the end of. Then save the VBA and run it from a command button on a form, or from the RunCode Macro command.

 

[RCMetrics]

Could you show me how... I'm a bit familiar with VB but not a pro...

here's the current vb code for this macro:

'------------------------------------------------------------
' Exportfile
'
'------------------------------------------------------------
Function Exportfile()
On Error GoTo Exportfile_Err

DoCmd.OutputTo acQuery, "Report by period", "MicrosoftExcelBiff8(*.xls)", "J:\RC Metrics\Report by period.xls", False, "", 0
DoCmd.OutputTo acQuery, "Outsourcers Count of Sales", "MicrosoftExcelBiff8(*.xls)", "J:\RC Metrics\Outsourcers Count of Sales.xls", False, "", 0


Exportfile_Exit:
Exit Function

Exportfile_Err:
MsgBox Error$
Resume Exportfile_Exit

End Function

 

[Joe4]

Here is what it would look like to just report you first file with a date stamp:

Function Exportfile()
    
    Dim myOutputFile1 As String
    myOutputFile1 = "J:\RC Metrics\Report by period" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.OutputTo acQuery, "Report by period", "MicrosoftExcelBiff8(*.xls)", myOutputFile1, False, "", 0

End Function

I'll leave it to you to add your second file. You can place it in this same function.