VBA Error '91' on ActiveWorkbook.Save

[Steven_Smith]

Hi All,
Long time user, first time poster here. I put that down to may other people having the issues I have before I do and then the Gurus here sorting them out in a very well written way so thanks for that.

I however have an issue I have been unable to find the resolution to. I recently wrote a sub procedure which is called by Workbook_Open(). It was meant to create a new copy of the file on the users PC, so that they didn't end up overwriting my template on SharePoint (they couldn't anyway as they only have read-only rights, but they want it to be straightforward into editing).

So, the macro creates an editable copy of the template with a new name based on a reference number that they provide via an input box and then saves to the desktop. I then switch all the application switches back to normal and try to save again so they don't end up opening and finding task bars missing, etc. However, when the final save happens it throws out Run Time Error '91' : Object Variable or With Block not Set.

Any ideas?

Sub SavetoPC()
    iceRef = InputBox("Which ICE/CR reference is this extraction pack for?")
    
    If iceRef = "" Then
        MsgBox ("You have failed to enter a valid ICE reference, this workbook will now close")
        Application.ThisWorkbook.Close SaveChanges:=False
    End If
    
    With Application
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .DisplayStatusBar = False
        .EnableAnimations = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    ThisWorkbook.Worksheets("JobPack").Range("B3").Value = iceRef
    
    DTAdd = CreateObject("WScript.Shell").specialfolders("Desktop")

    fileName = iceRef + "_ExtractionPack"
    fullPath = DTAdd + "\" + fileName + ".xlsm"
    
    ChDir DTAdd
    ThisWorkbook.SaveAs fileName:=fullPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .DisplayStatusBar = True
        .EnableAnimations = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With

   ActiveWorkbook.Save
End Sub

 

[BobUmlas]

I imagine you can't save while in the workbook_Open event - sort of a conflict of interests! My suggestion would be to use
Application.OnTime Now, "SaveMe"
and in the Sub SaveMe do the Activeworkbook.Save

 

[Norie]

Do you need the final save?

 

[Steven_Smith]

I need it so that Excels application settings get reset and saved in case the user doesn't save after this happens. I have removed it for now on the assumption they will save it as they will have filled it in.

It does work if you have the template saved to your PC and open it from there, but when opened from SharePoint it throws the error.

 

[Norie]

Can't you set Saved to True to do that?

ActiveWorkbook.Saved = True

 

[Steven_Smith]

I suppose I could set Saved to False, this would then prompt the user to save upon exit if they hadn't already. Just a little more streamline if I manage to save it automatically without user intervention.

 

[Steven_Smith]

I imagine you can't save while in the workbook_Open event - sort of a conflict of interests! My suggestion would be to use
Application.OnTime Now, "SaveMe"
and in the Sub SaveMe do the Activeworkbook.Save

This doesn't work either. However, I believe I have uncovered the reason. Thank you to all who responded.

ActiveWorkbook is not available in the Workbook_Open event when Protected View was loaded, so I have changed the code as such:

Sub SavetoPC()
    iceRef = InputBox("Which ICE/CR reference is this extraction pack for?")
    
    If iceRef = "" Then
        MsgBox ("You have failed to enter a valid ICE reference, this workbook will now close")
        Application.ThisWorkbook.Close SaveChanges:=False
    End If
    
    With Application
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .DisplayStatusBar = False
        .EnableAnimations = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    ThisWorkbook.Worksheets("JobPack").Range("B3").Value = iceRef
    
    DTAdd = CreateObject("WScript.Shell").specialfolders("Desktop")

    fileName = iceRef + "_ExtractionPack.xlsm"
    fullPath = DTAdd + "\" + fileName
    
    ChDir DTAdd
    ThisWorkbook.SaveAs fileName:=fullPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .DisplayStatusBar = True
        .EnableAnimations = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Workbooks(fileName).Save
End Sub

As you can see I reference the new workbook by it's name instead. It seems to be due to Protected View as if it doesn't enter Protected View it works with the original method. Who doesn't love a quirk!