[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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>
0
garyrobbins
Asked:
garyrobbins
  • 4
  • 4
2 Solutions
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now