Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existing f
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existing f
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 "Nils Titley" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existing f
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
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" wrote in message ... 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
Thanks Joel, Dave and Chip for your help.
"Chip Pearson" wrote: 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" wrote in message ... 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
It was a stupid typo.
I meant this: wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal (I like being specific.) Nils Titley wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
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" wrote: It was a stupid typo. I meant this: wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal (I like being specific.) Nils Titley wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
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. Nils Titley wrote: 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" wrote: It was a stupid typo. I meant this: wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal (I like being specific.) Nils Titley wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
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" wrote: 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. Nils Titley wrote: 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" wrote: It was a stupid typo. I meant this: wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal (I like being specific.) Nils Titley wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding date to file name when saving and over writing a existi
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) Nils Titley wrote: 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" wrote: 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. Nils Titley wrote: 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" wrote: It was a stupid typo. I meant this: wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal (I like being specific.) Nils Titley wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seeing existing file names when saving | Excel Discussion (Misc queries) | |||
Date format/Writing to a file problem | Excel Discussion (Misc queries) | |||
Ranges, adding a column to a named print range then saving the file | Excel Programming | |||
sharing violation while saving file after adding this macro | Excel Programming | |||
Saving over an existing file (updating an HTM file) | Excel Programming |