VBA Error '91' on ActiveWorkbook.Save

Steven_Smith

New Member
Joined
Apr 5, 2016
Messages
4
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?

Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
Do you need the final save?
 
Upvote 0
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.
 
Upvote 0
Can't you set Saved to True to do that?
Code:
ActiveWorkbook.Saved = True
 
Upvote 0
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.
 
Upvote 0
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:
Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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