garyrobbins
asked on
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"><UserInterfaceMac ro MinimumClientDesignVersion ="14.0.000 0.0000"><S tatements> <Action Name="ExportWithFormatting "><Argumen t Name="ObjectType">Query</A rgument><A rgument Name="ObjectName">Daily AR Trending</Argument><Argume nt Name="OutputFormat">ExcelW orkbook(*. xlsx)</Arg ument><Arg ument Name="OutputFile">A:\1. Robbins, John\Daily Reports\Daily AR Trending\Daily Info</Argument></Action></ Statements ></UserInt erfaceMacr o></UserIn terfaceMac ros>
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMac
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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")
A:\1. Robbins, John\Daily Reports\Daily AR Trending\ fnFileNameAndDate = Daily AR Info & Format (Date(), "yyyy-mm-dd")
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:This generated an Excel file with the name I passed as the BaseName to the function.
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:This generated an Excel file with the name I passed as the BaseName to the function.
ASKER
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
ASKER
Like this?