CODEWITHSUNDEEP
excelvba
Wes
Wes

Reputation: 1259

Excel vba accessing workbook instances from different Excel apps

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!

Upvotes: 3

Views: 1748

Answers (2)

Wes
Wes

Reputation: 1259

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.

Upvotes: 1

Tony Dallimore
Tony Dallimore

Reputation: 12413

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

Upvotes: 0

Related Questions