|
Saving a file with date and time as filename
I would like to create a macro to save a worksheet as a file with date and
time as filename. |
Saving a file with date and time as filename
Sub SaveSht()
Folder = ThisWorkbook.Path OldName = ThisWorkbook.Name 'remove extension OldName = Left(OldName, InStr(OldName, ".") - 1) NewName = OldName & Format(Date, "_MM_DD_YY") & ".xls" ActiveSheet.Copy ActiveWorkbook.SaveAs Filename:=Folder & "\" & NewName ActiveWorkbook.Close End Sub "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. |
Saving a file with date and time as filename
Hi,
I wrote this code a few years ago and have been using it ever since, it works well except I would like a way of changing the current directory within the code but I have to use the 'File', 'Save As', to change the current directory (maybe someone knows a good way to change the curdir within the code): ---------------------------------------------------------------------- Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @ 10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir Response = MsgBox("Current path is: " & Currpath & " is this OK?", vbYesNo) If Response = vbNo Then MsgBox "Change to the Appropriate Drive and Folder before continuing.." End End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False MsgBox "File Saved as: " & Currpath & "\" & SavedFilename End Sub --------------------------------------------------------------------------- See what you think.... Simka "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. |
Saving a file with date and time as filename
Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://spreadsheetpage.com/index.php...g_a_directory/ If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Simka wrote: Hi, I wrote this code a few years ago and have been using it ever since, it works well except I would like a way of changing the current directory within the code but I have to use the 'File', 'Save As', to change the current directory (maybe someone knows a good way to change the curdir within the code): ---------------------------------------------------------------------- Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @ 10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir Response = MsgBox("Current path is: " & Currpath & " is this OK?", vbYesNo) If Response = vbNo Then MsgBox "Change to the Appropriate Drive and Folder before continuing.." End End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False MsgBox "File Saved as: " & Currpath & "\" & SavedFilename End Sub --------------------------------------------------------------------------- See what you think.... Simka "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. -- Dave Peterson |
Saving a file with date and time as filename
See if you like this. I used the GetSaveAsFilename method selecting your
filename as the defult path and filename. then the user can choose any directory or filename he chooses. I also put in the code if the person hits cancel on the pop up window it will default back to your original filename. Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir fileSaveName = Application.GetSaveAsFilename( _ InitialFileName:=Currpath & SavedFilename, _ fileFilter:="Text Files (*.xls),*.xls") If fileSaveName = False Then fileSaveName = Currpath & "\" & SavedFilename End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs _ Filename:=fileSaveName, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved as: " & fileSaveName End Sub "Simka" wrote: Hi, I wrote this code a few years ago and have been using it ever since, it works well except I would like a way of changing the current directory within the code but I have to use the 'File', 'Save As', to change the current directory (maybe someone knows a good way to change the curdir within the code): ---------------------------------------------------------------------- Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @ 10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir Response = MsgBox("Current path is: " & Currpath & " is this OK?", vbYesNo) If Response = vbNo Then MsgBox "Change to the Appropriate Drive and Folder before continuing.." End End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False MsgBox "File Saved as: " & Currpath & "\" & SavedFilename End Sub --------------------------------------------------------------------------- See what you think.... Simka "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. |
Saving a file with date and time as filename
Good try Joel but,
I have noticed a few things that need amending: 1. Open a new worksheet and attempt to save the file using this routine and instead of pressing 'Save', press 'Cancel', the file saves as oppossed to cancel the routine. 2. When I run this routin and see the 'save' dialogue box, have a look in the Filetype textbox, on my Excel it shows 'Save as Type: Text Files (*.xls)' - this should be: 'Save as Type: Microsoft Office Excel Workbook (*.xls)' (or doesn't it matter). 3. If I run this routine several times, on my Excel the routine crashes after several attemps to save. Simka "Joel" wrote: See if you like this. I used the GetSaveAsFilename method selecting your filename as the defult path and filename. then the user can choose any directory or filename he chooses. I also put in the code if the person hits cancel on the pop up window it will default back to your original filename. Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir fileSaveName = Application.GetSaveAsFilename( _ InitialFileName:=Currpath & SavedFilename, _ fileFilter:="Text Files (*.xls),*.xls") If fileSaveName = False Then fileSaveName = Currpath & "\" & SavedFilename End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs _ Filename:=fileSaveName, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved as: " & fileSaveName End Sub "Simka" wrote: Hi, I wrote this code a few years ago and have been using it ever since, it works well except I would like a way of changing the current directory within the code but I have to use the 'File', 'Save As', to change the current directory (maybe someone knows a good way to change the curdir within the code): ---------------------------------------------------------------------- Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @ 10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir Response = MsgBox("Current path is: " & Currpath & " is this OK?", vbYesNo) If Response = vbNo Then MsgBox "Change to the Appropriate Drive and Folder before continuing.." End End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False MsgBox "File Saved as: " & Currpath & "\" & SavedFilename End Sub --------------------------------------------------------------------------- See what you think.... Simka "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. |
Saving a file with date and time as filename
I started with a Text SAVEAS and modified it. It just a string for the user
to see from fileFilter:="Text Files (*.xls),*.xls") to fileFilter:="Excel Files (*.xls),*.xls") I didn't know what you wanted to do if the operator hit cancel. to quit make change below from If fileSaveName = False Then fileSaveName = Currpath & "\" & SavedFilename End If to If fileSaveName = False Then msgbox("Cannot Save file - Exiting Sub") exit sub End If "Simka" wrote: Good try Joel but, I have noticed a few things that need amending: 1. Open a new worksheet and attempt to save the file using this routine and instead of pressing 'Save', press 'Cancel', the file saves as oppossed to cancel the routine. 2. When I run this routin and see the 'save' dialogue box, have a look in the Filetype textbox, on my Excel it shows 'Save as Type: Text Files (*.xls)' - this should be: 'Save as Type: Microsoft Office Excel Workbook (*.xls)' (or doesn't it matter). 3. If I run this routine several times, on my Excel the routine crashes after several attemps to save. Simka "Joel" wrote: See if you like this. I used the GetSaveAsFilename method selecting your filename as the defult path and filename. then the user can choose any directory or filename he chooses. I also put in the code if the person hits cancel on the pop up window it will default back to your original filename. Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir fileSaveName = Application.GetSaveAsFilename( _ InitialFileName:=Currpath & SavedFilename, _ fileFilter:="Text Files (*.xls),*.xls") If fileSaveName = False Then fileSaveName = Currpath & "\" & SavedFilename End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs _ Filename:=fileSaveName, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved as: " & fileSaveName End Sub "Simka" wrote: Hi, I wrote this code a few years ago and have been using it ever since, it works well except I would like a way of changing the current directory within the code but I have to use the 'File', 'Save As', to change the current directory (maybe someone knows a good way to change the curdir within the code): ---------------------------------------------------------------------- Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @ 10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir Response = MsgBox("Current path is: " & Currpath & " is this OK?", vbYesNo) If Response = vbNo Then MsgBox "Change to the Appropriate Drive and Folder before continuing.." End End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False MsgBox "File Saved as: " & Currpath & "\" & SavedFilename End Sub --------------------------------------------------------------------------- See what you think.... Simka "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. |
Saving a file with date and time as filename
This may have solved the SaveAs part and as regards to what should the
routine do if the user pressed 'Cancel'? I would have thought that the routine should cancel the save. But instead of: If fileSaveName = False Then msgbox("Cannot Save file - Exiting Sub") exit sub End If I would have written: If fileSaveName = False Then msgbox("File not Saved - Exiting Sub") exit sub End If Try running your routine over and over 8 or more times, it seems to change folders by itself and occassionally crashes. I still prefer my routine, as soon as you change the curdir using the SaveAs/Cancel it saves it over and over in the chosen folder changing the filename at each save + mine doesn't crash and it's possible to exit out without saving if the user selects No at the curdir dialogue. Simka. "Joel" wrote: I started with a Text SAVEAS and modified it. It just a string for the user to see from fileFilter:="Text Files (*.xls),*.xls") to fileFilter:="Excel Files (*.xls),*.xls") I didn't know what you wanted to do if the operator hit cancel. to quit make change below from If fileSaveName = False Then fileSaveName = Currpath & "\" & SavedFilename End If to If fileSaveName = False Then msgbox("Cannot Save file - Exiting Sub") exit sub End If "Simka" wrote: Good try Joel but, I have noticed a few things that need amending: 1. Open a new worksheet and attempt to save the file using this routine and instead of pressing 'Save', press 'Cancel', the file saves as oppossed to cancel the routine. 2. When I run this routin and see the 'save' dialogue box, have a look in the Filetype textbox, on my Excel it shows 'Save as Type: Text Files (*.xls)' - this should be: 'Save as Type: Microsoft Office Excel Workbook (*.xls)' (or doesn't it matter). 3. If I run this routine several times, on my Excel the routine crashes after several attemps to save. Simka "Joel" wrote: See if you like this. I used the GetSaveAsFilename method selecting your filename as the defult path and filename. then the user can choose any directory or filename he chooses. I also put in the code if the person hits cancel on the pop up window it will default back to your original filename. Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir fileSaveName = Application.GetSaveAsFilename( _ InitialFileName:=Currpath & SavedFilename, _ fileFilter:="Text Files (*.xls),*.xls") If fileSaveName = False Then fileSaveName = Currpath & "\" & SavedFilename End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs _ Filename:=fileSaveName, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved as: " & fileSaveName End Sub "Simka" wrote: Hi, I wrote this code a few years ago and have been using it ever since, it works well except I would like a way of changing the current directory within the code but I have to use the 'File', 'Save As', to change the current directory (maybe someone knows a good way to change the curdir within the code): ---------------------------------------------------------------------- Sub SaveAsNewFileName() 'Save the current file with a date and time file name as '2008-01-16 @ 10-48-57.xls' Filname1 = Now SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss") Currpath = CurDir Response = MsgBox("Current path is: " & Currpath & " is this OK?", vbYesNo) If Response = vbNo Then MsgBox "Change to the Appropriate Drive and Folder before continuing.." End End If oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Saving File..." & SavedFilename Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False MsgBox "File Saved as: " & Currpath & "\" & SavedFilename End Sub --------------------------------------------------------------------------- See what you think.... Simka "AAn" wrote: I would like to create a macro to save a worksheet as a file with date and time as filename. |
| All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com