Loop Through Processes

[Oorang]

I have a need to loop through every workbook open without closing them. (From Access) I was able to do that fairly easily with a for each workbook in Excel.Workbooks setup. However I realized that if there is more than one Excel Process open, I am only looping through the first Excel.Application object returned. Which means I am not truely checking everything that could be open. Which leads to the question... How do you loop through all workbooks in all Excel processes? I tried WMI but I could not figure out how to coerce a WMI Process into an Excel Application object. I'm Stumped.

 

[Zos474]

Thank you Jaafar for this very useful code. For those who, as in my company, have 32 bit versions of Office installed on 64 bit Windows you will need to change the VBA7 compiler constant to Win64. Until I did that Excel kept on crashing because it was trying to use the wrong variable types, etc. So e.g. change #If VBA7 Then... to #If Win64 Then...

 

[Jaafar Tribak]

Thank you Jaafar for this very useful code. For those who, as in my company, have 32 bit versions of Office installed on 64 bit Windows you will need to change the VBA7 compiler constant to Win64. Until I did that Excel kept on crashing because it was trying to use the wrong variable types, etc. So e.g. change #If VBA7 Then... to #If Win64 Then...

Thanks for letting us know.

 

[Zos474]

Correction: In working with this further, I now understand that the only change needed to the code for Excel 32 bit running on 64 bit Windows, are the pointer location constants. The rest of Jaafar's code with the VBA7 compiler constants are good as-is, just change the constants section to:

#If Win64 Then
Private Const vtbl_EnumRunning_Offset As Long = 9 * 4 * 2
Private Const vtbl_EnumMoniker_Next_Offset As Long = 3 * 4 * 2
Private Const vtbl_Moniker_GetDisplayName_offset As Long = 20 * 4 * 2
#Else
Private Const vtbl_EnumRunning_Offset As Long = 9 * 4
Private Const vtbl_EnumMoniker_Next_Offset As Long = 3 * 4
Private Const vtbl_Moniker_GetDisplayName_offset As Long = 20 * 4
#End If

I did still, despite the change, have one very old add-in (XNumbers / XNumb56) that kept on crashing Excel when I ran the routine, no matter what I did. I don't actually use that any more, so just removed it and now all OK.

 

[Jaafar Tribak]

Correction: In working with this further, I now understand that the only change needed to the code for Excel 32 bit running on 64 bit Windows, are the pointer location constants. The rest of Jaafar's code with the VBA7 compiler constants are good as-is, just change the constants section to:

#If Win64 Then
Private Const vtbl_EnumRunning_Offset As Long = 9 * 4 * 2
Private Const vtbl_EnumMoniker_Next_Offset As Long = 3 * 4 * 2
Private Const vtbl_Moniker_GetDisplayName_offset As Long = 20 * 4 * 2
#Else
Private Const vtbl_EnumRunning_Offset As Long = 9 * 4
Private Const vtbl_EnumMoniker_Next_Offset As Long = 3 * 4
Private Const vtbl_Moniker_GetDisplayName_offset As Long = 20 * 4
#End If

I did still, despite the change, have one very old add-in (XNumbers / XNumb56) that kept on crashing Excel when I ran the routine, no matter what I did. I don't actually use that any more, so just removed it and now all OK.

Good to know .

Thank you .