Invoke VBA on all open Workbooks

Help
1

Hello All,

I am trying to invoke VBA or Execute a macro that will run on all workbooks open on the users computer. The issue is that when I run within the excel application scope, the only workbook picked up by Application.workbook in the VBA is the one referenced by the excel application scope. I want to avoid using open application activity to manually run the VBA because I want to use UiPath to pass an argument through the VBA. Let me know if you have any ideas!

2

Do you have access to modify the macro? If so, just wrap it in a for each loop.
Dim wb as Workbook
For each wb in Application.Workbooks
<Your original code here>
Next wb

1 Like
3

Have you tried using Invoke Code and, as a first step within that, creating a list of Open Workbook objects you can then cycle through and apply your script to each?

4

Hello @Dave ,

I do and this is the methodology I used. However, for some reason when it is run within the excel application scope, the only workbook recognized by Application.Workbooks is the workbook referenced by the excel application scope!

To further clarify, lets say all I want to do is present the name of every open Excel workbook in a message box. I would have:

Sub Message_Name()
Dim wb as workbook
     For Each wb in Application.Workbooks
          msgBox wb.name
     Next wb
 End Sub

This would not return the names of all open workbooks, rather it would only return the name of the workbook in the excel application scope.

5

@ronanpeter I thought about doing this, but how would I go about creating a list of open workbook objects without using VBA? Is there a way to do this using UiPath?

6

Attached is an example which invokes code on a selected workbook to reformat selected cells on a selected file.

If you can adapt to get a list variable of open workbook objects you could adapt the code, place a For Each loop against that list and make the changes you need.

FormatCells.xaml (6.5 KB)

7

Separate Excel.exe Instances can’t really see each other in the Iterate Workbooks.

Add a Breakpoint somewhere in your workflow.

Open a Command prompt and type the below command 1 instance is good and two or more means you have separate instances.

tasklist /FI "IMAGENAME eq excel.exe"

Image Name                     PID Session Name        Session#    Mem Usage
========================= ======== ================ =========== ============
EXCEL.EXE                    12100 Console                    1    109,076 K
EXCEL.EXE                     1228 Console                    1    107,352 K