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
            ObjXL.Workbooks.Close
            ObjXL.Quit
        Else
            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
'Process
    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

'ClearProcessingT
    DoCmd.SetWarnings True
    XL.Run "SaveAll"
    Access.Quit

End Sub

Open in new window

Jay WilliamsOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Which workbook do you want to close and when do you want to close it?
0
Jay WilliamsOwnerAuthor Commented:
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?
0
NorieVBA ExpertCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jay WilliamsOwnerAuthor Commented:
Maybe I'm not putting it in the right place.
0
GrahamMandenoCommented:
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]
0
Gustav BrockCIOCommented:
You are not closing the workbook and you have to extremely strict in opening and closing objects in Excel.
See my comments here:

http://www.experts-exchange.com/Programming/Microsoft_Development/Q_28286853.html#a39627500

Note too, that Sheets and WorkSheets are not the same.

If you follow this, Excel will close, and your first part of the code will be moot.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay WilliamsOwnerAuthor Commented:
Thanks, guys! I wasn't ignoring you, just getting yanked around a little. :-)  I'll take your advice and dig in this afternoon.
0
NorieVBA ExpertCommented:
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?
0
NorieVBA ExpertCommented:
* then = than - need to get a bigger/better phone.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.