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

Microsoft AccessMicrosoft ExcelVBA

Avatar of undefined
Last Comment

8/22/2022 - Mon

Which workbook do you want to close and when do you want to close it?
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jay Williams

Maybe I'm not putting it in the right place.
Graham Mandeno

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]
Gustav Brock

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jay Williams

Thanks, guys! I wasn't ignoring you, just getting yanked around a little. :-)  I'll take your advice and dig in this afternoon.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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.