Multiple Instances of Excel

First time poster of this site.... be gentle

I am trying to work in multiple instances of excel, but am having no luck in using vb code in my activated instance. This is my code:

PHP Code: 

For Each process In GetObject("winmgmts:{impersonationLevel=impersonate}").InstancesOf("Win32_process ")
        If process.Name = "EXCEL.EXE" Then
            'MsgBox process.Handle
            count = 0
            hand = process.Handle
            AppActivate (hand)
            'SendKeys ("{DOWN}"), True
            For Each wkb In Workbooks
                If Windows(wkb.Name).Visible Then
                   book(count) = wkb.Name
                End If
                count = count + 1
            Next
        End If
    Next
MsgBox book(0) & book(1) & book(2) & book(3) & book(4) & book(5) & book(6)

End Sub 


I can send keystrokes to the activated app, but I can't seem to perform any coded functions on it. Instead it just performs the code on the instance that the code resides in.

Any help would be great..

Thanks.

yes, that's exactly what i want to do. control another workbook in another instance of excel.

Unfortunately, I can't control if the user creates a new instance, so I need to be able to operate on any spreadsheet that is open in Windows.

Could you give an example of the API in action?

thanks in advance

I'm only a beginner programmer, but it looks as though the routine requires knowing the name of the workbook in the other instance? I guess I didn't say that, I also won't know the name of the workbooks.

When the userform initializes, my hope is to retrieve all of the workbook names from all of the instances of Excel and put them in a combobox. The user then chooses which file they want to perform the script on.

Is there a way to retrieve the filenames of all the workbooks in all of the instances? Or is that impossible?

Thanks again....

I am out of it... you're using the workbook_name to compare, so obviously you are able to obtain the filename of any workbook... duh!

Unless you are wanting to create some code out of interest sake, don't worry about creating a routine for me. I'm going to give it a go with the info here.

I'll post what I come up with.


Thanks a million... I'll pay it forward

I'm a little puzzled as to why the string and IIF functions are necessary?

PHP Code: 

WindowTitle = String(512, Chr$(0))
              L = GetWindowText(hWnd, WindowTitle, 512)
              WindowTitle = IIf(L > 0, Left(WindowTitle, L), "") 


Wouldn't this do?

PHP Code: 


Private Declare Function GetWindowText _
  Lib "User32.dll" _
    Alias "GetWindowTextA" _
      (ByVal hWnd As Long) As Long
.
.
.
WindowTitle = GetWindowText(hWnd) 


BTW thank you for the info about string, etc. it's basically a translation thing between VB and API... I think?

I tried to activate a workbook and test it out with your code, exactly as pasted here, but when I try to perform a task on the Activated workbook, it still operates on the workbook that the VBA code is in

PHP Code: 

Private Sub CommandButton5_Click()
ActivateWorkbook "book2"
Range("A1").Value = 4
End Sub 


Is there a with activeworkbook needed? I've tried all different avenues and permutations, but I seem to be spinning my wheels.

Thanks in advance and thanks again.... all this info is very helpful.

bump....bump