Export Query with Dynamic Name

[thabounceisback]

I tried to write a macro that would export a set of queries to a dynamically named excel file referencing the date. Here is my code:

Function Export()
On Error GoTo Export_Err

    DoCmd.OutputTo acOutputQuery, "Bernie Sandoval", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Bernie_Sandoval_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "Cheryle Elswich", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Cheryle_Elswich_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Dan Walker", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Dan_Walker_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ken Westfall", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Ken_Westfall_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Monica Friedman", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Monica_Friedman_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ron Chacon", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Ron_Chacon_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ron Popp", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Ron_Popp_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Tom Farnsworth", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Tom_Frnsworth_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Vicki Stanga", "ExcelWorkbook(*.xlsx)", "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Vicki_Stanga_& Format(Date(), "yyyymmdd")", False, "", , acExportQualityPrint


Export_Exit:
    Exit Function

Export_Err:
    MsgBox Error$
    Resume Export_Exit

End Function

I get an error stating that I cannot create the file. Any ideas on what I am doing wrong?

 

[Isskint]

The file name argument is incorrect. you need to set the value of date NOT the formula. More likely the ampersand in the file name is not allowed.
EG
"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Bernie_Sandoval_& Format(Date(), "yyyymmdd")",

Should be;
"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Bernie_Sandoval_" & Format(Date(), "yyyymmdd"),

 

[nanscombe]

Your formatted dates should be outside of the strings.

Function Export()
On Error GoTo Export_Err

    DoCmd.OutputTo acOutputQuery, "Bernie Sandoval", "ExcelWorkbook(*.xlsx)", [COLOR="Red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Bernie_Sandoval_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "Cheryle Elswich", "ExcelWorkbook(*.xlsx)", [COLOR="Red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Cheryle_Elswich_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Dan Walker", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Dan_Walker_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ken Westfall", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Ken_Westfall_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Monica Friedman", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Monica_Friedman_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ron Chacon", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Ron_Chacon_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ron Popp", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Ron_Popp_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Tom Farnsworth", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Tom_Frnsworth_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Vicki Stanga", "ExcelWorkbook(*.xlsx)", [COLOR="red"]"C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\Vicki_Stanga_"[/COLOR] & [COLOR="RoyalBlue"]Format(Date(), "yyyymmdd")[/COLOR], False, "", , acExportQualityPrint


Export_Exit:
    Exit Function

Export_Err:
    MsgBox Error$
    Resume Export_Exit

End Function

By adding a couple of variables to this you could make it a lot more readable.

Function Export()
dim strDocRoot as String, strDocDate as String
On Error GoTo Export_Err

strDocRoot = "C:\Users\kamend\Documents\Data Clean Room\RigData\RigData\Weekly Assignments\"

strDocDate = "_" & Format(Date(), "yyyymmdd")

    DoCmd.OutputTo acOutputQuery, "Bernie Sandoval", "ExcelWorkbook(*.xlsx)", strDocRoot & "Bernie_Sandoval" & strDocDate, False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "Cheryle Elswich", "ExcelWorkbook(*.xlsx)", strDocRoot & "Cheryle_Elswich" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Dan Walker", "ExcelWorkbook(*.xlsx)", strDocRoot & "Dan_Walker" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ken Westfall", "ExcelWorkbook(*.xlsx)", strDocRoot & "Ken_Westfall" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Monica Friedman", "ExcelWorkbook(*.xlsx)", strDocRoot & "Monica_Friedman" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ron Chacon", "ExcelWorkbook(*.xlsx)", strDocRoot & "Ron_Chacon" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Ron Popp", "ExcelWorkbook(*.xlsx)", strDocRoot & "Ron_Popp" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Tom Farnsworth", "ExcelWorkbook(*.xlsx)", strDocRoot & "Tom_Frnsworth" & strDocDate, False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "Vicki Stanga", "ExcelWorkbook(*.xlsx)", strDocRoot & "Vicki_Stanga" & strDocDate, False, "", , acExportQualityPrint


Export_Exit:
    Exit Function

Export_Err:
    MsgBox Error$
    Resume Export_Exit

End Function