Adding date to file name when saving and over writing a existing f

[Nils Titley]

I want to add a date at end of the file name. How do I do that?

Also I want to over write a file if it exist at this location but I don't
want a box to pop up.

Dim WBNew As Workbook

WBNew.SaveAs "C:\Temp\Lift Logger\Lift Logger Process.xls"

Look like this: Lift Logger Process 031908.xls

Thanks

 

[Joel]

Sub writebook()
Dim WBNew As Workbook
Application.EnableEvents = False
String_Date = Format(Date, "mmddyy")
WBNew.SaveAs "C:\Temp\Lift Logger\Lift Logger Process" & _
String_Date & ".xls"
Application.EnableEvents = True

End Sub

 

[Dave Peterson]

Dim NewName as string
NewName = "C:\temp\lift logger\lift logger process " _
& format(date, "mmddyy") & ".xls"

application.displayalerts = false
wbnew.saveas filename:=newname, fileformat:=newname
application.displayalerts = true

========
If you didn't know the name of the workbook, you could strip the .xls (and
mmddyy if you had it), too.

 

[Nils Titley]

Dave,

wbnew.saveas filename:=newname, fileformat:=newname

It worked if I removed the " ,fileformat:=newname". What is that doing and
why won't it work with it?

Thanks

 

[Chip Pearson]

The "fileformat" parameter tells Excel in what format to save the file, such
as in Text, Comma Separated Values, Excel95 and so on. Most of the time you
can omit this and save the file as a normal Excel xls file.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Nils Titley]

Thanks Joel, Dave and Chip for your help.

The "fileformat" parameter tells Excel in what format to save the file, such
as in Text, Comma Separated Values, Excel95 and so on. Most of the time you
can omit this and save the file as a normal Excel xls file.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

It was a stupid typo.

I meant this:

wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal

(I like being specific.)

 

[Nils Titley]

Dave

The date is working well but now I want to add time to the file. I thought
I could do this but it doesn't like it. What do I have to do, please?

NewName = "C:\temp\lift logger report\Lift Logger Process Report " _
& Format(Date, "ddmmyy") & Time & ".xls"

Thanks

 

[Dave Peterson]

NewName = "C:\temp\lift logger report\Lift Logger Process Report " _
& Format(now, "yyyymmdd_hhmmss") & ".xls"

Notice that the code uses Now instead of Date.

I like the yyyymmdd_hhmmss format (descending order of units of time). But you
could change it to what you like.

Dave

The date is working well but now I want to add time to the file. I thought
I could do this but it doesn't like it. What do I have to do, please?

NewName = "C:\temp\lift logger report\Lift Logger Process Report " _
& Format(Date, "ddmmyy") & Time & ".xls"

Thanks

 

[Nils Titley]

Dave

Thank works but the time looks weired. I had trouble recognizing it. Can't
I make it look more like time? Say 12:11:30 ? There must be a better way?

Thanks for your help.

 

[Dave Peterson]

You can't use :'s in file names. You could use dashes or underscores.

Format(now, "yyyymmdd_hh-mm-ss")

Personally, I like that first suggestion's format.

More options...

Format(Now, "yyyymmdd_hh-mm-ss AM/PM")
or
Format(Now, "yyyymmdd_hh-mm-ssAM/PM")
(if you don't like the 24 hour time)

Dave

Thank works but the time looks weired. I had trouble recognizing it. Can't
I make it look more like time? Say 12:11:30 ? There must be a better way?

Thanks for your help.