Move between Separate Instances of Excel

[MacroAlan]

I figured out how to bring two or more spreadsheets into the same instance of Excel and populate a dropdown with the names of all the workbooks. Everything was working great!

Now I have discovered that the user opens a separate instance of Excel with a spreadsheet from SharePoint. The name of the file apparently increments every few days. I was even able to populate my dropdown with the names of all workbooks in all instances:

Sub BeginCompileDates()
    Dim xlApp   As Application
    Dim wkB     As Workbook
    On Error GoTo ErrorHandler

    For Each xlApp In GetExcelInstances()
        With usrGetFileName
            .cmdFileNames.AddItem xlApp.ActiveWorkbook.Name [COLOR=#CC0000]'wkB.Name[/color]
        End With
    Next
    usrGetFileName.Show
    Exit Sub
ErrorHandler:
    MsgBox "Please report " & Err.Number & vbCrLf _
        & Err.Description, vbCritical, "Uh oh!"
End Sub

What I need to be able to do now is select the instance that does not contain my code and read/search for certain information. This next bit should list the tabs in the selected workbook; not my main workbook.

Private Sub cmdFileNames_Change()
    Dim TabNm       As String
    Dim Z           As LongPtr
    Dim xlAp2       As Excel.Application
    If Me.cmdTabs.ListCount > 0 Then
        Me.cmdTabs.Clear
    End If
cont:
    Set xlAp2 = GetObject(Me.cmdFileNames.Value).Application
  Debug.Print xlAp2.ActiveSheet.Name & " *" [COLOR=#CC0000]'gets name in THIS workbook[/color]

    For Z = 1 To Sheets.Count
        Me.cmdTabs.AddItem Sheets(Z).Name
    Next Z

End Sub

What do I need to do to select the actual workbook from the dropdown? And how do I move between the workbooks while I am searching for the data to be reported??



Alan

 

[SkipVought]

Hi

You'll need to use the GetObject() method to set an application object for that instance of that workbook. Then apply that instance to that workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]