Check if workbook file is open in another instance of Excel and close if so

Hi,

See the file associated with post #2 in the following thread (API references coded for 32 bit Excel only): http://www.excelforum.com/excel-prog...instances.html

The file contains the Macro DisplayDataForAllOpenExcelInstances() below that displays data about each open Excel File in all instances of Excel which should help you get started. There is also a Macro in the file that closes all open Excel Files in All Instances of Excel, except the file that is running the code. Please NOTE that Macro DisplayDataForAllOpenExcelInstances() overwrites the contents of Sheet 'Main' in the 'Active Workbook'. A runtime error will occur if Sheet 'Main' DOES NOT EXIST.

Option Explicit
'Reference: cytop (Thank you) http://www.ozgrid.com/forum/showthread.php?t=182853
 
'The following declarations are for 32 bit Excel only
         
Private Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
 

Sub ClearA21ToA9999()
  Sheets("Main").Range("A21:A9999").ClearContents
End Sub


Sub DisplayDataForAllOpenExcelInstances()
  'This displays information about all open instances of Excel
  'Reference: cytop (Thank you) http://www.ozgrid.com/forum/showthread.php?t=182853
  
  Dim xlApp As Object
  Dim wb As Object
  Dim ws As Object
  
  Dim iCountInstances As Long
  Dim iCountWorkbooks As Long
  Dim iRow As Long
  Dim hWndXL As Long
  
  'Clear the output area
  Call ClearA21ToA9999
  
  'Set the row number to the row before the first output row
  iRow = 20
  
  'Output a Start message
  iRow = iRow + 1
  Sheets("Main").Cells(iRow, 1) = "DisplayDataForAllOpenExcelInstances() started on " & Now() & "."
       
 'Find the first Excel Window
  hWndXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
     
  'Loop until there are no more Open Instances of Excel
  While hWndXL > 0
         
    'Increment counter
    iCountInstances = iCountInstances + 1
         
    'Print Instance & Handle to Debug window
    iRow = iRow + 1
    iRow = iRow + 1
    iRow = iRow + 1
    Sheets("Main").Cells(iRow, 1) = "Instance #" & iCountInstances & ":  Handle: " & hWndXL
         
    'Get a reference to it
    If GetReferenceToXLApp(hWndXL, xlApp) Then
    
      'Clear the Workbook count
      iCountWorkbooks = 0
             
      'Iterate through the workbook
      For Each wb In xlApp.Workbooks
      
        'Increment the count of Workbooks in this instance
        iCountWorkbooks = iCountWorkbooks + 1
         
        'Output Workbook name
        iRow = iRow + 1
        iRow = iRow + 1
        Sheets("Main").Cells(iRow, 1) = "Workbook " & iCountWorkbooks & ": " & wb.Name
                 
        'List worksheets in the Workbook
        For Each ws In wb.Worksheets
           iRow = iRow + 1
           Sheets("Main").Cells(iRow, 1) = "Workbook " & iCountWorkbooks & ": " & wb.Name & _
                                           "     Sheet: " & ws.Name
        Next ws
              
      Next wb
    End If
         
    'Find the next Excel Window
    hWndXL = FindWindowEx(0, hWndXL, "XLMAIN", vbNullString)
         
  Wend
     
End Sub

Lewis

Originally Posted by mc84excel

Question as per title ...

1. In total, the topic is not entirely clear, because it is not known whether it is an open file on the local computer, or whether it is an open file from e.g. a company network. Depending on where you open the file, different ways to handle these situations will apply

2. "Open ...For Input ..."

This method is not credible (reliable ?), see 'Open' statement description in Microsoft context help:

"Important - In Binary, Input, and Random modes, you can open a file using a different file number without first closing the file. In Append and Output modes, you must close a file before opening it with a different file number",

so using 'Input' for 'Open' statement you can open an already open file and declare (state, tell) that it has not been opened before.

3. A brief "web query" shows interesting solutions that can be customized to this topic.

A. Solution of Peter Thornton - lists all Excel instances nicely:
https://social.msdn.microsoft.com/Fo...forum=exceldev

B. "Check Whether an Excel File Is Open on a Network"
http://www.java2s.com/Code/VBA-Excel...onaNetwork.htm
and in many, many places ...
... on the forum something would also be found, but I admit that I was not looking here ...