Macro: OutputTo file naming Question

RCMetrics

Board Regular
Joined
Oct 28, 2005
Messages
95
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
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
 
Upvote 0
Here is what it would look like to just report you first file with a date stamp:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,222,382
Messages
6,165,651
Members
451,983
Latest member
Raph24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top