ActiveWorkbook.SaveCopyAs date format

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
482
Office Version
  1. 365
Platform
  1. Windows
I have this code that I've been working with this morning. As it is now, when I run it the Workbook is saved in a folder corresponding to the current year. Works great, however if a Workbook has already been saved the same day it will save over the top of the previous file. I'm trying to add a hh:mm:ss timestamp to the code so additional saves don't replace previous files.

Here's my code:

VBA Code:
Sub SaveWorkbook()

    Dim rootDirectory As String
    Dim folderToBeCreated As String
    Dim path As String
        
        rootDirectory = "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\"

        folderToBeCreated = Format(Now, "yyyy")

        path = rootDirectory & folderToBeCreated

        If Len(Dir(rootDirectory, vbDirectory)) <> 0 Then
            If Len(Dir(path, vbDirectory)) = 0 Then
                VBA.MkDir (path)

            Else
            End If
        Else
        End If

ChDrive "Z"
ChDir "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\"

ActiveWorkbook.SaveAs FileName:="\\GLC-SERVER\Pulte\Z_Pulte Master Archive\" & year(Date) & "\" & "Pulte Master " & Format(Now, "yyyymmdd") & ".xlsm", FileFormat:=52

End Sub

I tried this without success:

VBA Code:
ActiveWorkbook.SaveAs FileName:="\\GLC-SERVER\Pulte\Z_Pulte Master Archive\" & year(Date) & "\" & "Pulte Master " & Format(Now, "yyyymmdd hh:mm:ss") & ".xlsm", FileFormat:=52

I'm getting this error:

backup.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi, the ":" is in invalid character in the filename - so suspect that is what is causing the error you are seeing. Suggest you replace with a different separator within the time part of the filename
 
Upvote 0
It appears that the issue is the : character. If I use a . instead it works.
 
Upvote 0
Is it possible that my code can add an a (or b or c or d) after the yyymmdd to allow progressive saves?
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top