Append Date to filename in macro

[CFL]

Hello!

I am trying to use a macro to export to excel using a export map. I want to
add todays date to the file name, so it doesnt overwrite existing files, is
this possible?

Or, perhaps a pop-up to ask me the file name? when project discovers a file
of the same name, it says "overwrite?" if you click cancel the macro fails,
with no option to enter an altenative filename.

Please help!
Christina

 

[Jan De Messemaeker]

Hi,

Both are possible; you have to modify teh VBA code of the macro.
You will find this in a module in the VB editor (Alt+F11)
If you find it, paste it into a message here, I'll try to suggest the
necesary changes.
HTH

 

[CFL]

Thanks Jan,
the vb is: -

Sub TestOutput()
' Macro TestOutput
' Macro Recorded Wed 15/02/06 by Christina Lovelock.
FileSaveAs Name:="D:\Documents and Settings\chlo\My Documents\Test
Files\BusReqResourcePlan TestBaseLine.xls", FormatID:="MSProject.XLS5",
map:="BaseVsActual2"
End Sub

 

[Jan De Messemaeker]

Hi Christina,
Here's the completed code:

Sub TestOutput()
' Macro TestOutput
' Macro Recorded Wed 15/02/06 by Christina Lovelock.
dim address as string
dim lng as integer
address="D:\Documents and Settings\chlo\My Documents\Test
Files\BusReqResourcePlan TestBaseLine.xls"
lng=len(address)
address=left(address,(lng-4)) & cstr(date) & ".xls"
FileSaveAs Name:=address, FormatID:="MSProject.XLS5",
map:="BaseVsActual2"
End Sub

Don't forget to put things back on one line where necessary.
HTH

 

[CFL]

Hi Jan, sorry to bother you again, but i get the following error,

Run-time error '1004':
Project cannot find the path "D:\Documents and Settings\chlo\My
Documents\Test Files\BusReqResourcePlan TestBaseLine15/02/2006.xls".

.... i know I can't find it, I want it to create it! also, I suspect I doesnt
like "/" in the file name, any way to remove or have alternate format?

Thanks so much,
CFL

 

[Jan De Messemaeker]

Hi,

You must be right

Add the following
dim Datext as string
datext=cstr(day(date)) & "-" & cstr(month(date)) & "-" & cstr(year(date))

and in the formula address=... replace ctr(date) by datext

HTH

 

[CFL]

You are a genius !

Thanks so much,
Christina

Hi,

You must be right

Add the following
dim Datext as string
datext=cstr(day(date)) & "-" & cstr(month(date)) & "-" & cstr(year(date))

and in the formula address=... replace ctr(date) by datext

HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
+32-495-300 620

 

[Jan De Messemaeker]

Thank you so much, Christina.
And... you're welcome.