 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Adding 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 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 > I want to add a date at end of the file name. How do I do that? > [quoted text clipped - 8 lines] > > Thanks 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. > I want to add a date at end of the file name. How do I do that? > [quoted text clipped - 8 lines] > > Thanks  SignatureDave Peterson 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 > Dim NewName as string > NewName = "C:\temp\lift logger\lift logger process " _ [quoted text clipped - 20 lines] > > > > Thanks 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. SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) > Dave, > [quoted text clipped - 32 lines] >> > >> > Thanks 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 [quoted text clipped - 36 lines] > >> > > >> > Thanks It was a stupid typo. I meant this: wbnew.saveas filename:=newname, fileformat:=xlworkbooknormal (I like being specific.) > Dave, > [quoted text clipped - 33 lines] > > > > Dave Peterson SignatureDave Peterson 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 > It was a stupid typo. > [quoted text clipped - 41 lines] > > > > > > 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 > [quoted text clipped - 55 lines] > > > > Dave Peterson SignatureDave Peterson 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. > NewName = "C:\temp\lift logger report\Lift Logger Process Report " _ > & Format(now, "yyyymmdd_hhmmss") & ".xls" [quoted text clipped - 63 lines] > > > > > > 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 > [quoted text clipped - 74 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.