Microsoft Access VBA declared public Excel objects in standard module, are erroneously closing after running subroutine in form module
Posted on 2015-01-11
Variables declared in standard module named basExcel:
Public objExcelAp As Excel.Application
Public objExcelWb As Excel.Workbook
Form module subroutine to set objects and reads excel spreadsheet:
Set objExcelAp = New Excel.Application
Set objExcelWb = objExcelAp.Workbooks.Open(sFileName)
End Sub (objects close here after the end sub executes)
After this routine ends, the excel objects are closing without my code making that happen. I need them to stay open and be closed later with my vba code after other unrelated procedures are executed in another form module.
There is more detail in these procedures but I trimmed for visibility.