Saving in Excel with dynamic filename

[Eseke]

Saving in Excel with dynamic filename

I'm trying to make a macro that makes a save of a workbook.
So far I managed to get the code but now I wanted to make the filename dynamic.
But I haven’t got the skill to make such a macro so all help is welcome.

The code I have so far is:
ChDir "H:\personal"
ActiveWorkbook.SaveAs Filename:= _
"H:\personal\personal.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

 

[Andrzejek]

"Dynamic" filename - so it will change, right?
And you want it to change how?

Usually people do this by using Date or Time or both in a file name.

And I hope your file will be placed in "H:\personal\" and only "personal.xls" will be dynamic

Have fun.

---- Andy

 

[Eseke]

Yes it was going to be a date specific file name

 

[CBasicAsslember]

I usually construct the path and file name as a string to allow flexability.

sFileName = "personal.xls"
sPath = "H:\personal\" & sFileName

ActiveWorkbook.SaveAs Filename:= _
        sPath, FileFormat _

 

[Andrzejek]

If you want:
"H:\personal\personal.xls"

to be

"H:\personal\personal070123.xls" YYMMDD format

You may do:

ActiveWorkbook.SaveAs Filename:= _
"H:\personal\personal"[b] & Format$(Date, "YYMMDD") & "[/b].xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

Have fun.

---- Andy

 

[Eseke]

Thanks Andy.

But what do I do need to add to the code if I want to use it multiple times per day?

 

[AnotherHiggins]

Format(Date, "YYMMDD_HHMMSS")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Andrzejek]

Then you have to ask yourself:

Do I want to overwrite the file?
or
Do I want to keep it and have a Time in my filename?

What do you want to happen?

Have fun.

---- Andy

 

[SkipVought]

Format([B]NOW[/B], "YYMMDD_HHMMSS")

Skip,

_[red][/red]

 

[Eseke]

Thanks guys.

I wanted to keep the allready saved file.
But is it possible to have a systemtic number after the date? Like 20070127001

 

[AnotherHiggins]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Andrzejek]

Sure you can have it.

Before saving the file, you just need to check if you have any file saved today, and what is the last number in the file name.

If it is
20070127001
add 1 and you will have
20070127002
next time check, add 1 and have
20070127003

Next day, start from the
20070128001


Have fun.

---- Andy

 

[Eseke]

That was exactly what i had in mind Andy.
I have seen some macro's that can check if there is a file with that name. Will see how it will turn out.

But you have been a great help.


Thanks

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886