views:

405

answers:

1

Using Access VBA, I want to open an XL file, do stuff, then close it without closing other XL files that are open.

Option 1: If the last line is "ObjXL.Application.Quit", that closes ALL open Excel files, not just the current one.

Option 2: If the last line is "ObjXL.Close", then the workbook closes, but that particular instance of XL stays open (i.e. Excel without a workbook).

So, how do I close an Excel workbook without closing any other workbooks that are open?

Sub x()
    Dim ObjXL As Excel.Workbook
    Set ObjXL = GetObject("C:\Reports\Adhoc Default.xls")
    ObjXL.Application.Visible = True
    ObjXL.Windows(1).Visible = True
    ObjXL.Worksheets(1).Activate
    DoStuff()
    ObjXL.Save
    Option1/2/3?
End Sub
+2  A: 

Heh, I answered my own question before anyone else:

Sub xx()
    Dim XLapp As New Excel.Application
    Dim ObjXL As Excel.Workbook
    Set ObjXL = XLapp.Workbooks.Open("C:\reports\adhoc default.xls")
    ObjXL.Application.Visible = True
    ObjXL.Windows(1).Visible = True
    ObjXL.Worksheets(1).Activate
    ObjXL.Save
    ObjXL.Close
    XLapp.Quit
End Sub
PowerUser