CODEWITHSUNDEEP
excelms-accessvba
SQLnRprobs
SQLnRprobs

Reputation: 77

Close specific Excel file opened by user from Access-VBA

I see version of how to do this with 

Dim ran as Excel.Applcation

but the version of Access I am using doesn't have Excel.Application as an option.

I have written the following code that runs, but does not close the file

Dim Path1 as String
Dim objXL As Object
Dim xlWB As Object
Path1 = "C:/....."
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(Path1)


xlWB.Close False
Set xlWB = Nothing
objXL.Quit
Set objXL = Nothing

Upvotes: 0

Views: 2726

Answers (1)

Erik A
Erik A

Reputation: 32642

You can use the following code to close all Excel files (already posted here): 

Public Sub CloseAllExcel()
    Dim obj As Object
    On Error GoTo ExitSub
    Dim i As Integer
    'There shouldn't be more than 10000 running Excel applications
    'Can use While True too, but small risk of infinite loop
    For i = 0 To 10000
        Set obj = GetObject(, "Excel.Application")
        obj.Quit
    Next i
ExitSub:
End Sub

But if we're going to close one specific one, we need some Win32 magic that I can't do, but hey, if you can't do something, you can find it on StackOverflow. Most code by Florent B found here

First, declare our Win32 functions

#If VBA7 Then
  Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" ( _
    ByVal hwnd As LongPtr, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long

  Private Declare PtrSafe Function FindWindowExA Lib "user32" ( _
    ByVal hwndParent As LongPtr, ByVal hwndChildAfter As LongPtr, _
    ByVal lpszClass As String, ByVal lpszWindow As String) As LongPtr
#Else
  Private Declare Function AccessibleObjectFromWindow Lib "oleacc" ( _
    ByVal hwnd As Long, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long

  Private Declare Function FindWindowExA Lib "user32" ( _
    ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
    ByVal lpszClass As String, ByVal lpszWindow As String) As Long
#End If

Then use them to get all running Excel application objects

Public Function GetExcelInstances() As Collection
  Dim guid&(0 To 3), acc As Object, hwnd, hwnd2, hwnd3
  guid(0) = &H20400
  guid(1) = &H0
  guid(2) = &HC0
  guid(3) = &H46000000

  Set GetExcelInstances = New Collection
  Do
    hwnd = FindWindowExA(0, hwnd, "XLMAIN", vbNullString)
    If hwnd = 0 Then Exit Do
    hwnd2 = FindWindowExA(hwnd, 0, "XLDESK", vbNullString)
    hwnd3 = FindWindowExA(hwnd2, 0, "EXCEL7", vbNullString)
    If AccessibleObjectFromWindow(hwnd3, &HFFFFFFF0, guid(0), acc) = 0 Then
      GetExcelInstances.Add acc.Application
    End If
  Loop
End Function

And then use that collection so we can check which one has the workbook open, and close it

Public Sub closeWorkbook(workbookPath As String)
    Dim excelInstances As Collection
    Set excelInstances = GetExcelInstances
    Dim excelApp As Object
    Dim excelWorkbook As Object
    For Each excelApp In excelInstances
        For Each excelWorkbook In excelApp.Workbooks
            If excelWorkbook.FullName = workbookPath Then
                excelWorkbook.Close False
            End If
        Next excelWorkbook
        If excelApp.Workbooks.Count = 0 Then
            excelApp.Quit
        End If
    Next excelApp
End Sub

And then, implement that close function

Dim Path1 as String
Path1 = "C:/....."
closeWorkbook Path1

Upvotes: 2

Related Questions