Error 91 displays after Application Quit instruction

Question

I have built an Add-In which is intended to be downloaded from the www. The Add-In will land up in the user computer's Downloads folder.

On being activated, the first phase of the Add-In's activty is to copy itself into the user's '/Microsoft/AddIns' folder using SaveAs. Then then "parent" Add-in closes itself and quits Excel. (On restarting Excel the "child" Add-In will be loaded and active.)

The code for this is

Sub CheckInstall()

'Several lines of code before this.
'They have been tested and seem to work well.

MyNewfileNm = TestBase & GCSAPPNAME

If IsInstalled(MyNewfileNm) Then

    Application.DisplayAlerts = False
    ThisWorkBook.SaveCopyAs MyNewfileNm
    Application.DisplayAlerts = True

    MsgBox "We're done, and Excel will close." & Chr(13) & _
    "On reopening you will find 'ACBA Mapping' loaded and active in the ADD-INS tab."
    
    
    Excel.Application.Quit
    ActiveWorkbook.Close False


Else
        ThisWorkBook.SaveCopyAs MyNewfileNm
    
            If ActiveWorkbook Is Nothing Then
                Workbooks.Add
                Set oAddIn = Application.AddIns.Add(MyNewfileNm, False)
                oAddIn.Installed = True
            Else
                Set oAddIn = Application.AddIns.Add(MyNewfileNm, False)
                oAddIn.Installed = True
            End If
            
    MsgBox "We're done, and Excel will close." & Chr(13) & _
    "On reopening you will find 'ACBA Mapping' loaded and active in the ADD-INS tab."
    
    Excel.Application.Quit
    ActiveWorkbook.Close False
End If


End Sub

This processes and copies itself as expected, but before the Application.Quit completes I get an Error Code 91. On clicking the error message, it simply resumes the instruction code.The result is precisely as expected.

However, I must either solve the problem generating the error message or suppress the error message. For the time being I cannot do either.

I'd be grateful for a solution.

Why are you trying to close the workbook *after* quitting Excel? — BigBen, Aug 04 '21 at 19:41
If you quit excel with an unsaved workbook open, it will ask you whether you want to save it. In this instance, the answer is 'No'. The workbook was only opened to facilitate the installation process of the copied Add-In. — Stephen Allen, Aug 04 '21 at 20:54
Yes, I thought so too, but, on testing, Excel.Application.Quit failed to quit completely. It seems that the order of the code instructions is necessary. — Stephen Allen, Aug 04 '21 at 22:06
Perhaps try [this approach](https://stackoverflow.com/questions/3628252/closing-excel-application-using-vba), changing `ThisWorkbook` to `ActiveWorkbook`. — BigBen, Aug 04 '21 at 22:09
I'm fairly sure that won't work. In this context ThisWorkbook refers to the "parent" Add-In which is not strictly an ActiveWorkbook. But I will try it. — Stephen Allen, Aug 04 '21 at 22:27
Have you tried setting `Application.DisplayAlerts = False` then `Application.Quit`? Remove the line that close workbook. And since you have intention to close the application on both branches of `If` statement, take it out and place it after ` End If`. — Raymond Wu, Aug 04 '21 at 23:57
Raymond - I think you may have drawn attention to the underlying problem. In the first portion of the IF statement I don't re-install the Add-In. This had been set to FALSE earlier in the routine. I'll test it in due course. — Stephen Allen, Aug 05 '21 at 06:10

score 0 · Answer 1 · answered Aug 05 '21 at 11:20

This turned out to be a silly mistake on my part. The revised code below runs without an error. The only difference is the elimination of the instruction to close the ActiveWorkbook in the first portion if the primary IF statement. There was no open workbook available to be closed at that juncture.

Sub Check Install()
'Several lines of code before this.
'They have been tested and seem to work well.

MyNewfileNm = TestBase & GCSAPPNAME

If IsInstalled(MyNewfileNm) Then

    Application.DisplayAlerts = False
    ThisWorkBook.SaveCopyAs MyNewfileNm
    Application.DisplayAlerts = True

    MsgBox "We're done, and Excel will close." & Chr(13) & _
    "On reopening you will find the " & NewMappingVersion & " version of 'ACBA Mapping' loaded."
    
    
    Excel.Application.Quit
'    In principle we haven't initiated a Workbook so there is no need to close it.
'    ActiveWorkbook.Close False


Else
        ThisWorkBook.SaveCopyAs MyNewfileNm
    
            If ActiveWorkbook Is Nothing Then
                Workbooks.Add
                Set oAddIn = Application.AddIns.Add(MyNewfileNm, False)
                oAddIn.Installed = True
            Else
'               This shouldn't be necessary, but leave it for the time being.
                Set oAddIn = Application.AddIns.Add(MyNewfileNm, False)
                oAddIn.Installed = True
            End If
            
    MsgBox "We're done, and Excel will close." & Chr(13) & _
    "On reopening you will find 'ACBA Mapping' version " & NewMappingVersion & " loaded and active in the ADD-INS tab."
    
    Excel.Application.Quit
    ActiveWorkbook.Close False
End If


End Sub

The line to set `oAddIn` and `oAddIn.Installed = True` seems to be redundant in the `If..Else..End If` statement. You can simplify it to `If ActiveWorkbook Is Nothing Then Workbooks.Add` in a line then both lines to set `oAddIn` and `oAddIn.Installed = True` so total 3 lines — Raymond Wu, Aug 05 '21 at 13:22
@RaymondWu I thought so too, but on testing, it generated an error. The code in the 'Answer' is copied from Jan Karel Peterse with some minor amendments. — Stephen Allen, Aug 05 '21 at 16:30
@Stephan That couldn't be but it's a small thing anyway, as long its working. — Raymond Wu, Aug 05 '21 at 16:37

Error 91 displays after Application Quit instruction

1 Answers1