I need the current date added to the excel doc name when i export from Access

I have a macro that auto export and saves as excel but I want to add the current date to the file name.  

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Query</Argument><Argument Name="ObjectName">Daily AR Trending</Argument><Argument Name="OutputFormat">ExcelWorkbook(*.xlsx)</Argument><Argument Name="OutputFile">A:\1. Robbins, John\Daily Reports\Daily AR Trending\Daily Info</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>
garyrobbinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
I don't use macros (almost never but I'm sure that will be changing as I delve into 2013 Access Web Apps).

You might be able to call a function to return the name you want to give the file.  To make it flexible, the function might look like:

Public Function fnFileNameAndDate(BaseName as string) as string

    fnFileNameAndDate = BaseName & Format(Date(), "yyyy-mm-dd")

End Function
0
garyrobbinsAuthor Commented:
A:\1. Robbins, John\Daily Reports\Daily AR Trending\fnFileNameAndDate = BaseName & Format(Date(), "yyyy-mm-dd")


Like this?
0
Helen FeddemaCommented:
Yes, though you might want to put a space or dash before the date.  You can use various formats, so long as they don't include slashes, which are not allowed in file names.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

garyrobbinsAuthor Commented:
It is not working.  This what i am trying to save the file as.

A:\1. Robbins, John\Daily Reports\Daily AR Trending\ fnFileNameAndDate = Daily AR Info & Format (Date(), "yyyy-mm-dd")
0
Dale FyeCommented:
Gary,

I just tested the technique I recommended above, calling a function as the Output File, although I did have to change my function to add the file extension as follows:

Public Function fnFilename(BaseName As String) As String

    fnFilename = BaseName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"

End Function

Here is what my macro settings look like:macro settingsThis generated an Excel file with the name I passed as the BaseName to the function.
0
garyrobbinsAuthor Commented:
Dale,

Where are you entering the below information?

Public Function fnFilename(BaseName As String) As String

    fnFilename = BaseName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"

End Function

Sorry about the multiple threads,
0
Dale FyeCommented:
Gary,

Don't worry about the multiple threads, there is a learning curve to sites like EE.  The problem with multiple threads is that multiple experts could be looking at the different threads and wasting their time when another expert has already answered the question in the other thread.

To create a user defined function that can be called from anywhere in your application, you must create a code module.

You can open the VB Editor window by pressing [Ctrl-G] from the Access window.  When you do that, you will see a window that looks something like:VBEif you right click in the "Project" window (upper left), you will see an option for "Insert", click on that, then select the "Module".  This will create a code module.  All you need to do is copy that function (everything from Public down through End Function) and paste it in the code section (top right) (where I have the GetFiscalDate function in my first image above).  

Then click "Debug => Compile from the menu to make sure the function compiles properly.

And finally, click the Save icon on the menu or select File=>Save from the menu.

Then, going back to your macro, you have to call the function by putting an equal sign in front of the function and then, because the function expects a string, you must enclose the name you want to use in quotes:

Output File: =fnFileName("A:\1. Robbins, John\Daily Reports\Daily AR Trending\Daily AR Info")

When your macro runs, it should create a file:

A:\1. Robbins, John\Daily Reports\Daily AR Trending\Daily AR Info_2014-07-24.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garyrobbinsAuthor Commented:
Dale

You ROCK, Working GREAT!   I learn so many things that I will be able to apply else where.

Can I create multiple Public Functions on the same module?
0
Dale FyeCommented:
Glad to help.

Yes, you can put multiple functions or subroutines in a single code module, but I like to keep the modules somewhat focused on a particular subject.  If you look at the  image above, you will see modules for date functions, text functions, menus, commandbars, ...

That organization makes it easier to find what you are looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.