Need Access to insert date in Excel filename when using Outputto 1

[RhondaJC]

I currently use a macro to run: first a make table query, then a filtered query, then it outputs to an excel file. I'd like Access to give this spreadsheet a unique name without any prompting by the user. I currently have it name the spreadsheet, "needtoorder.xls". I'd like Access to name it "needtoorder61902.xls" (today's date).

Here's the code I'm using:

'------------------------------------------------------------
' Daily_Need_to_Order_Report_to_John_H
'
'------------------------------------------------------------
Function Daily_Need_to_Order_Report_to_John_H()
On Error GoTo Daily_Need_to_Order_Report_to_John_H_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "7New Work Order Info", acNormal, acEdit
    DoCmd.SetWarnings False
    DoCmd.OutputTo acQuery, "Need To Order-Filtered", "MicrosoftExcel(*.xls)", "p:\JohnH\needtoorder.xls", False, ""
    DoCmd.SetWarnings False


Daily_Need_to_Order_Report_to_John_H_Exit:
    Exit Function

Daily_Need_to_Order_Report_to_John_H_Err:
    MsgBox Error$
    Resume Daily_Need_to_Order_Report_to_John_H_Exit

End Function

What is available to make Access generate a new file name?
I'm just a beginner with code, and I truly appreciate all the advice that's given on this forum.

Thanks in advance,
Rhonda

 

[grnzbra]

newfilename = "needtoorder" & format(Now(),"mmddyyhhnnss&quot)
you could also use & Date(), but if you ran it twice on the same day you would not get unique queries.

 

[RhondaJC]

Like I said, I'm new to code.

Where would I insert this?

Thanks,
Rhonda

 

[grnzbra]

I think it would be something like this:

DoCmd.OutputTo acQuery, "Need To Order-Filtered", "MicrosoftExcel(*.xls)", "p:\JohnH\needtoorder" & Format(Now(),"mmddyyhhmmss" & ".xls", False, ""

If it only runs once per day, you could use the Date() function instead of now and use the format "yymmdd" which would easily shot the date of the report and have it sort correctly.

 

[grnzbra]

OOPS!

that should be & Format(Now(),"mmddyyhhmmss&quot & ".xls",

 

[grnzbra]

I just tried it with a slight change which allowed me to control the formatting better

At the beginning of the function

Dim UniquePart as string


Then, just before the OutputTo line, add

UniquePart = Format(Now(),"yymmddhhmmss&quot

and finally change part of the OutputTo line to

...needtoorder" & UniquePart & ".xls",...

now you have better control over the formatting of the unique part.

 

[RhondaJC]

You sir, are my hero! That worked like a charm. Thank you very much!