0

reference source of original code: Having multiple Excel instances launched, how can I get the Application Object for all of them?

The Excel Workbook that I'm trying to open is created by a database application for making changes. Here is the code I'm using. The issue is that it creates a read-only copy. I need to be able to update (via a Macro from a different Workbook) the original Workbook. I appreciate any help I may get - Thank you.

Sub Test2XL()

        Dim Source_Table As Range, Source_Rows As Integer, Source_Columns As Integer, WbTCName As String
        Dim WbTC As Workbook

  Dim xl As Excel.Application
  Dim i As Integer

  For Each xl In GetExcelInstances()
    Debug.Print "Handle: " & xl.Application.hwnd
    Debug.Print "# workbooks: " & xl.Application.Workbooks.Count
    For i = 1 To xl.Application.Workbooks.Count
        Debug.Print "Workbook: " & xl.Application.Workbooks(i).Name
        Debug.Print "Workbook: " & xl.Application.Workbooks(i).FullName
        Debug.Print "Workbook path: " & xl.Application.Workbooks(i).Path
        If Left(xl.Application.Workbooks(i).Name, 3) = "tc_" Then  ' Find Workbook of Interest
            WbTCName = xl.Application.Workbooks(i).FullName
            On Error Resume Next
            Set WbTC = Workbooks(WbTCName)
            On Error GoTo 0
            If WbTC Is Nothing Then
                Set WbTC = Workbooks.Open(WbTCName)
            End If
        End If
    Next i
  Next
  Set xl = Nothing


End Sub
RS7
  • 1
  • Seems like you'd need to close the already-open copy before you open it in the instance where your code is running. Or just work with it in the other Excel instance, since you have a reference to it. – Tim Williams Apr 16 '20 at 07:03
  • Thank you Tim - My goal was to Activate the original instance, however WbTC is Nothing. Closing the workbook is the mechanism that the app uses to update the database. I'm using the following statement to address that shortcoming: set TC_range=xl.Application.Workbooks(i).Sheets(1).Range("a1:z2000") I know this is not very elegant, but it is working. Again, thank you! – RS7 Apr 25 '20 at 05:03

0 Answers0