"Unhandled exception has occurred...Object reference not set to an instance of an object" after running a macro.

[mrnacar]

Why am I getting the following error message after running my macro. Is there anything wrong with my macro? It seems my macro runs completely, but once it's done, I get the following error which is annoying.

Sub Auto_Open()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Dim year4 As String
Dim year2 As String
year4 = Range("B2").Value
year2 = Range("B3").Value
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    Workbooks.Open Filename:="\\pfcoffice3\" & year4 & " Reports\PFC\COLREC" & year2 & "\Recovery\SCC_Vendor_Template.xlsx"
    Application.Run "ssgenallquerydetail"
    ActiveWorkbook.Save
    ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
Application.Quit
End Sub

 

[RoryA]

You shouldn't be getting a .Net error from a VBA macro. What does ssgenallquerydetail do?

 

[mrnacar]

You shouldn't be getting a .Net error from a VBA macro. What does ssgenallquerydetail do?

It executes a query within an application called "Spreadsheet Server Suite" made by insightsoftware. It seems it's only isolated to one machine because I ran the same exact macro on another machine with no .Net error after it completed. Any experts in here to resolve this .Net error?

 

[mrnacar]

I created a simple macro and added Application.Quit at the end of my simple macro and still get the .Net error. I'm assuming it has something to do with running Application.Quit in vba? However, when I manually closed excel, I don't get the .Net error.

 

[mrnacar]

I added "MsgBox "Macro Completed!" right before Application.Quit to create some sort of delay and I don't get the .Net error. Now I'm trying to figure what is the difference between this machine and the other machine that doesn't get the .Net error that does not a require a delay before Application.Quit and so far, the only difference I see is the Spreadsheet Server Suite version. The one with no issues has version 24.2 and the one with the issue has version 23.1 I may just need to upgrade or add a delay right before Application.Quit I may have answered my own question but if anyone else has a better solution, please feel free to post.

 

[mrnacar]

FYI: adding

Application.Wait Now + #0:00:01#

before Application.Quit solved my problem.