Alt-Tab, Switch Focus, multiple instances of excel

[prudge]

It seems like no one has an answer for this. And I would think it's a fairly common issue.

Circumstance:
The user will open the files from their browser so this creates new instances of Excel.
The user will choose which file they wish to work on from a Combobox that has been populated with the filenames from all of the instances of Excel

Problem:
1. Find the filenames of the workbooks that are open in all of the instances of Excel
2. Switch the focus to another worksheet within another instance of Excel.
3. Perform the set of tasks on this workbook.

I have tried alot of different things with no success. And have searched till my hair is falling out.

Any help would be great!
Thanks

 

[xlbo]

Code in Excel is instance specific afaik.

Not sure your situation is overly common - tbh, whether a workbook opens in a new instance or not from the internet is down to local settings so is not consistent either...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hmmmmm???

I open several files in my Windows Explorer or Internet Explorer and they ALL open in the current instance of Excel. I can see each one list in View > Window > Switch Windows.

Are you certain that someone is not loading a new instance of Excel and then opening a workbook?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

Occasionally my Excel will flip out and kill stuff open downloaded files in separate instances. But doing it by default sounds like corrupted or improperly set extension management in Windows.

 

[VBAjedi]

Not sure about finding the filenames of the files open in other instances of Excel (maybe using FindWindow in the Windows API?), but once you have at least one filename from another instance I think you can use GetObject to access the workbooks open in that instance:

Dim xlObj As Object
  Set xlObj = GetObject("C:\96300Temp.xls")
  MsgBox xlObj.Application.Workbooks(1).Worksheets(1).Range("A1")

VBAjedi

 

[SkipVought]

I think you can use GetObject to access the workbooks open in that instance:

...but then you have to know the path & filename in order to do that.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[prudge]

It seems to be hit or miss. Some people are using Firefox, other are using Opera. I use opera and any spreadsheet always opens in a new instance.

I haven't really looked at the settings in each program, but asking the company to switch settings in whatever program their using will be difficult

As well, I know there is going to be a need to activate other programs for this project.

The method I came up with is to get the process ID for all processes of a certain type (EXCEL.EXE) and then activate the process using Appactivate(processid). But I am not sure of the syntax.

I found a couple of things on the net that are close using:

Private Sub CommandButton1_Click()
For Each process In GetObject("winmgmts:{impersonationLevel=impersonate}").InstancesOf("Win32_process ")
If process.Name = "EXCEL.EXE" Then
MsgBox process.<can't remember> ' display PID
End If
Next
End Sub

Once I have the process IDs I can activate each one? I think.

 

[SkipVought]

Check out faq707-4594.

You can see the actual properties of the process object.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[prudge]

That is totally cool, "watch window".... me likey

Ok, so far I am able to activate a particular instance and I can "sendkeys" to it, but when I try to do anything with VBA code it just does the code on my VBAcode based spreadsheet.

So this code will only return the filenames of the instance with the VBA.

Private Sub CommandButton1_Click()
Dim process As Object
Dim hand As Integer
Dim wkb As Workbook
Dim book(19) As String
Dim count As Integer


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

Not sure what to do...

 

[nodrog77]

Prudge,

I could be wrong here but wouldn't you have to get some sort of handle on each Application object for each process.

All of the other objects such as Windows and Workbooks would be children of that Application Object (they just can be referenced without the Application object qualifier).

Lea