Save the workbook with a time stamp in the same folder

[winfy]

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

[SamT]

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]

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]

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]

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]

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.

[snb]

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.