Excel vba accessing workbook instances from different Excel apps

Question

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!

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 http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel — Tim Williams, Feb 02 '12 at 00:38
The second answer in stackoverflow.com/questions/2971473/ from *ForEachLoop* is the one to use. — brettdj, Feb 02 '12 at 09:57

score 1 · Accepted Answer · answered Feb 02 '12 at 19:14

1

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.

answered Feb 02 '12 at 19:14

Wes

1,183
3
23
51

This may be a solution, but not one that you would want to use on a PC that a user is working on. The risk of it going sideways is pretty high when you use SendKeys. Nevertheless, it is another solution, and it may be appropriate in some instances. – rohrl77 Jul 11 '19 at 06:53

score 0 · Answer 2 · answered Feb 02 '12 at 00:13

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

His workbooks are in different instances of Excel: this would only work for workbooks in the same instance. — Tim Williams, Feb 02 '12 at 00:21

Excel vba accessing workbook instances from different Excel apps

2 Answers2