VBA - SaveCopyAs hidden

Question

Everytime I save my workbook I need to save the same workbook, but Hidden.

Now I have this code

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    ThisWorkbook.Saved = True
    On Error Resume Next
    If Not (Left(ThisWorkbook.Name, 2) = "Z_") Then
        Application.DisplayAlerts = False
        ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Z_" & ThisWorkbook.Name
        SetAttr ThisWorkbook.Path & "\Z_" & ThisWorkbook.Name, vbHidden
        Application.DisplayAlerts = True
    End If
    SetAttr ThisWorkbook.Path & "\Z_" & ThisWorkbook.Name, vbHidden
End Sub

However it works only every second time. First time I save WB, it creates the file and make it hidden, but when I save it second time it deletes the hidden file.

When I use SaveAs with overwrite property it saves file and activate it, but I dont want that.

Why is that? How to solve this please?

Answer 1

When you have a problem like this, the first this to do is to comment out On Error Resume Next and Application.DisplayAlerts = False. That will give you far more information on what's going on.

In your case, you had a problem with preexisting files. Try this instead:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim HiddenFileName As String

    HiddenFileName = ThisWorkbook.Path & "\Z_" & ThisWorkbook.Name

    ThisWorkbook.Saved = True

    On Error Resume Next
    If Not (Left(ThisWorkbook.Name, 2) = "Z_") Then

        Application.DisplayAlerts = False

        ' Unhide and delete existing file
        SetAttr HiddenFileName, vbNormal
        Kill HiddenFileName

        ' Save new copy and hide it
        ThisWorkbook.SaveCopyAs HiddenFileName
        SetAttr HiddenFileName, vbHidden
        Application.DisplayAlerts = True
    End If
    SetAttr HiddenFileName, vbHidden
End Sub

It unhides and deletes the file, before creating it again. This is a bit dirty, since it doesn't test if it exists before, but relies on On Error Resume Next.