View Full Version : Save the workbook with a time stamp in the same folder
winfy
08-25-2016, 07:02 PM
Hi all, I have the following code to save the workbook with a time stamp in the same folder. It seems to work when the workbook is not a macro-enabled, but doesn't if it is. Anyone have any tips on making it work for macro-enabled workbooks?
Sub snwb()
Dim thisWb As Workbook, d As Integer
Set thisWb = ActiveWorkbook
Workbooks.Add
d = InStrRev(thisWb.FullName, ".")
ActiveWorkbook.SaveAs Filename:=Left(thisWb.FullName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWb.FullName, d)
ActiveWorkbook.Close savechanges:=False
End Sub
thanks in advance
It seems to work when the workbook is not a macro-enabled, but doesn't if it is. Anyone have any tips on making it work for macro-enabled workbooks? Probably has to do with Macros in the Enabled book.
IMO, you are depending on "ActiveWorkbook" too much. Study this one for a bit.
Sub VBAEpress_SamT()
Dim thisWbName As String
Dim NewName As String
Dim d As Integer
Application.EnableEvents = False
Application.DisplayAlerts = False
thisWbName = ActiveWorkbook.FullName
d = InStrRev(thisWb, ".")
NewName = Left(thisWbName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWbName, d)
Workbooks(ThisWb).SaveAs Filename:=NewName
Workbooks(NewName).Close savechanges:=False
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Kenneth Hobs
08-25-2016, 07:53 PM
When pasting code, please do so between code tags. Click the # icon on a reply toolbar to insert the tags.
Obviously, you would need to set the input parameter for FileFormat. You can record a macro if you don't know the syntax for the type you want to SaveAs. Set the file extension too though that is likely not needed. When saving an exact copy rather than SaveAs, use SaveCopyAs. When you need to save as another FileFormat type, use well, SaveAs.
IF still stuck, post back and say what fileformat types you want to save file to and what the fileformat types are for the original workbooks.
Sam is just too fast for me...
winfy
08-25-2016, 08:07 PM
Hi SamT, thanks for your reply.
I tried to run the code and this error popped up - 'Invalid procedure call or argument' for this line
NewName = Left(thisWbName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWbName, d)
You mentioned this issue is there because of my other macros. How can I other macros affect it when they themselves are not running any errors?
Thanks in advance.
Kenneth Hobs
08-25-2016, 09:08 PM
I would guess that it erred because your file was not saved? That often happens when you make normal assumptions during tests. You can check if saved by:
Msgbox Thisworkbook.Saved
String parsing like that only works if your file naming convention is as you expect. FSO will be the more robust method.
Sub ken()
Dim thisWbName As String, d As Integer
thisWbName = ThisWorkbook.FullName
d = InStrRev(thisWbName, ".")
Debug.Print Left(thisWbName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWbName, d)
End Sub
Sub ken2()
Dim thisWbName As String, fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
thisWbName = ThisWorkbook.FullName
Debug.Print ThisWorkbook.Path & fso.GetBasename(thisWbName) & _
"\" & Format(Now, " yyyyddmm hhmmss.") & fso.GetExtensionName(thisWbName)
End Sub
Though ken2 would not error if file was not saved at least once, it will produce a non-useful string.
winfy
08-25-2016, 11:25 PM
Hi Ken, thanks for your reply. I tried the code. the message box comes back with TRUE, which means my workbook is saved, but still nothing happens. I am unsure why.
Sub M_snb()
activeworkbook.savecopyas replace(activeworkbook.fullname,".",format(now,yyyymmdd hhmmss.")
activeworkbook.close -1
End Sub
Please:
1. test the code
2. analyse any errors
3. read the helpfiles, the topics activeworkbook, savecopyas, fullname, replace, format and close)
4. amend the code if it gives any errors.
5. If your efforts are not successful ask a new question in which you indicate all what you have done to get the desired result.
6. debugging is one of the best methods to master a programming language
In other words: a forum is not a replacement for an extensive course or book on the basics of VBA.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.