My Auto-Save vba is saving to the wrong location and I don't know why

[jmpatrick]

Here's file path my code:

    ChDir "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\" & year(Date) & "\"

    ActiveWorkbook.SaveCopyAs FileName:="Pulte Master " & Format(Now, "yyyymmdd") & ".xlsm"

I want the archived file path to look like this: \\GLC-SERVER\Pulte\Z_Pulte Master Archive\2022\Pulte Master 20220415.xlsm

Unfortunately what I'm getting is this: \\GLC-SERVER\Pulte\Pulte Master 20220415.xlsm

Here's the whole macro:

Sub SaveWorkbook()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    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\" & year(Date) & "\"

    ActiveWorkbook.SaveCopyAs FileName:="Pulte Master " & Format(Now, "yyyymmdd") & ".xlsm"
     
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

What am I missing?

 

[Fluff]

Try it like

ActiveWorkbook.SaveCopyAs FileName:=path = & "\Pulte Master " & Format(Now, "yyyymmdd") & ".xlsm"

 

[jmpatrick]

Try it like

ActiveWorkbook.SaveCopyAs FileName:=path = & "\Pulte Master " & Format(Now, "yyyymmdd") & ".xlsm"

I'm getting Compile error: Syntax error

 

[Fluff]

Remove the = sign after path.

Also you would be better of changing the name of your variable. You should never use VBA keywords for variables or procedure names.

 

[jmpatrick]

Remove the = sign after path.

Also you would be better of changing the name of your variable. You should never use VBA keywords for variables or procedure names.

So, change all instances of path to something like zpath?

 

[Fluff]

That should be fine.
You can also get rid of this line as it doesn't do anything

ChDir "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\" & year(Date) & "\"

 

[jmpatrick]

That should be fine.
You can also get rid of this line as it doesn't do anything

ChDir "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\" & year(Date) & "\"

OK, suggested changes made. Archived files now save to correct file. However, the script was altering the file path on the active workbook which is strange. What I did to fix this is add code to set the Hyperlink Base before and after the save procedure. Works fine now, but I'm really not sure why. Here's the final code:

Sub SaveWorkbook()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ActiveWorkbook.BuiltinDocumentProperties("Hyperlink Base") = "\\GLC-SERVER\Pulte\"

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

        folderToBeCreated = Format(Now, "yyyy")

        zpath = rootDirectory & folderToBeCreated

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

            Else
            End If
        Else
        End If
      
    ChDrive "Z"
      
    ActiveWorkbook.SaveCopyAs FileName:=zpath & "\Pulte Master " & Format(Now, "yyyymmdd") & ".xlsm"
    
    ActiveWorkbook.BuiltinDocumentProperties("Hyperlink Base") = ""
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Thanks!

 

[Fluff]

Glad you sorted it & thanks for the feedback.