Link to home
Start Free TrialLog in
Avatar of Jay Williams
Jay Williams

asked on

Access VBA to close a specific open Excel file.

How can I specify closing one of several open Excel files from Access?  Access (triggered by an Excel macro) is turning around refresh data from an Excel front end.  I already have the sheet saved, and I want Access to close it completely before opening the template and saving it again as the same name.  I had it working, but the turnaround was closing and quitting every other open Excel file.  This won't work for the users. Here's my code:
Public Sub RefreshPicks()
    On Error Resume Next
    Dim ObjXL As Excel.Application
    Set ObjXL = GetObject("C:\Users\CurrentSheet.xlsm", "Excel.Application")
        If Not (ObjXL Is Nothing) Then
            Debug.Print "Closing XL"
            ObjXL.Application.DisplayAlerts = False
            Debug.Print "XL not open"
        End If
    Dim sPath As String, xlFile As String
    sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\Refresh\"
    xlFile = Dir(sPath & "*.xlsm")
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults As QueryDef
    Dim qdfResults2 As QueryDef
    Dim qdfResults3 As QueryDef
    Dim qdfResults4 As QueryDef
    Dim rsResults As DAO.Recordset
    Dim rsResults2 As DAO.Recordset
    Dim rsResults3 As DAO.Recordset
    Dim rsResults4 As DAO.Recordset
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\__MOST_REPORT\Reservation Data\PickListTemplate.xlsm")
    Set qdfResults = CurrentDb.QueryDefs("OpenRsrvtnsQ")
    Set rsResults = qdfResults.OpenRecordset()
    Set qdfResults2 = CurrentDb.QueryDefs("TrackingQ")
    Set rsResults2 = qdfResults2.OpenRecordset()
    Set rsResults3 = CurrentDb.OpenRecordset("FactoryDeletedT")
    Set qdfResults4 = CurrentDb.QueryDefs("WorkingTasksQ")
    Set rsResults4 = qdfResults4.OpenRecordset()
    Debug.Print xlFile
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM ProcessingT"
    DoCmd.RunSQL "DELETE * FROM WorkingTasksT"
    DoCmd.TransferSpreadsheet acImport, 10, "ProcessingT", sPath & xlFile, True, "PickList!A2:V5000"
    DoCmd.TransferSpreadsheet acImport, 10, "WorkingTasksT", sPath & xlFile, True, "WorkingTasks!A1:V5000"
    DoCmd.OpenQuery "RemoveProcessingDuplicatesQ"
    DoCmd.OpenQuery "UpdatePicksQ"
    DoCmd.OpenQuery "UpdateClosePicksQ"
    DoCmd.OpenQuery "UpdateWorkingTasksQ"
    DoCmd.OpenQuery "CloseRsrvtnsInWHtransTQ"
    DoCmd.OpenQuery "PurgeClosedInProcessingTQ"
    DoCmd.OpenQuery "UpdateSlocQ"
        If DCount("*", "OffListQ") > 0 Then
            DoCmd.OpenQuery "FactoryDeletedQ"
            DoCmd.OpenQuery "CloseFactoryDeletedQ"
        Else: End If
        If DCount("*", "TrackedTodayQ") = 0 Then
            Call Snapshot 'Appends TrackingT data
        Else: End If
XL.Visible = True
    wbTarget.Sheets("PickList").Range("A3").CopyFromRecordSet rsResults
    Set rsResults = Nothing
    XL.Run "SetFormatting"
    wbTarget.Sheets("Tracking").Range("A2").CopyFromRecordSet rsResults2
    Set rsResults2 = Nothing
    wbTarget.Sheets("FactoryDeleted").Range("A3").CopyFromRecordSet rsResults3
    Set rsResults3 = Nothing
    wbTarget.Sheets("WorkingTasks").Range("A2").CopyFromRecordSet rsResults4
    Set rsResults4 = Nothing
    XL.Run "SetFormatting"
    Set wbTarget = Nothing

    DoCmd.SetWarnings True
    XL.Run "SaveAll"

End Sub

Open in new window

Avatar of Norie

Which workbook do you want to close and when do you want to close it?
Avatar of Jay Williams


C:\Users\CurrentSheet.xlsm as soon as it starts Access.  Do I need to split the Access code? Use one procedure to close C:\Users\CurrentSheet.xlsm and then call the rest of it in another procedure?
If you just wanted to close that specific workbook you could use this.
ObjXL.Workbooks("CurrentSheet.xlsm").Close SaveChanges:=False ' or True

Open in new window

Maybe I'm not putting it in the right place.
Hi Jay

Try substituting it for Line 8 in the code you posted.

Is GetObject working to fine the instance of Excel with the open WorkSheet?  GetObject can be a bit quirky at times.

Best wishes,
Graham Mandeno [Access MVP since 1996]
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, guys! I wasn't ignoring you, just getting yanked around a little. :-)  I'll take your advice and dig in this afternoon.
Just curious, can you use GetObject to return a reference to the workbook itself rather then the instance of Excel the workbook is open in?

Maybe something to to with the class argument of GetObject?
* then = than - need to get a bigger/better phone.