Excel vba accessing workbook instances from different Excel apps

3
votes

So say you have two separate workbooks opened for write each in a separate Excel process, how to access the other workbook and make changes to it through VBA? Is there a way to cycle through all the Excel applications and see what workbooks they have opened? Help greatly appreciated!

2
excelvba
You can use the windows API to enumerate all windows and find those corresponding to an instance of excel, but there's no easy way to use the "handle" of the window and convert that to an application reference in VBA. Update: see here for one approach which might work for you stackoverflow.com/questions/2971473/… – Tim Williams
The second answer in stackoverflow.com/questions/2971473/ from ForEachLoop is the one to use. – brettdj

2 Answers

1
votes

Found a somewhat inelegant solution using AppActivate then SendKeys. SendKeys type a hotkey to a macro which would contain code to do whatever I want that workbook to do, such as close itself in an orderly fashion so the first app can open it.

0
votes

The following display the names of the open workbooks, the names of the sheets within them and the value of cell A1. This should be enough to get you going.

  Dim InxWB As Long
  Dim InxWS As Long
  For InxWB = 1 To Workbooks.Count
    With Workbooks(InxWB)
      Debug.Print "Workbook """ & .Name & """ contains the following sheets:"
        For InxWS = 1 To .Sheets.Count
          Debug.Print "    " & .Sheets(InxWS).Name
          Debug.Print "      Cell A1 contains: " & _
                                              .Sheets(InxWS).Cells(1, 1).Value
       Next
    End With
  Next