Running an excel macro in PAD

(0) Share

Report

Posted on by Silasdp

I'm trying to apply a macro stored in one .xlsm file onto another .xlsx file in PAD.

The flow so far is very simple. I launch both files and have ticked "load macro" for the .xlsm file.

Both files opens up fine, but I get a standard macro error message.

Overall flow:

.xlsm launch parameters:

Run excel macro parameters:

I believe that I'm not writing the correct parameter for the "Macro" line, but can't find any useful documentation for syntax.

The name of the actual macro within the .xlsm file is "BadDebtCalc"

Does anyone have information on what to do here?

Thank you

/Silas

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Should be something similar to this. This is from WinAutomation, predecessor of PAD.

You should do following
(1) Launch Excel action > it should produce Excel_Instance variable as output. In your case you have it named MacroSheet
(2)Run Macro action > INPUTS: Excel_Instance & BadDebtCalc (here you can have Macro parameters as per my example)

Was this reply helpful? Yes No
Silasdp 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Michael for you reply.

That did not work for me, but I just want to make sure that I have all the variables correct.

$ExcelInstance$ is my .xlsx where I want to run the macro.
$MacroSheet$ is the .xlsm that contains the macro name BadDebtCalc.

From what you wrote I got to the following parameters, that gave the same error.

What am I missing?

/Silas

Was this reply helpful? Yes No
Silasdp 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Update:

So I'm able to run the macro within its own file (the .xlsm file), but I can see that my .xlsx file (where I want to run the macro) can not see the macro from the other file. This happens only when PAD opens the files and not if I manually open the files. In that case excel can easily see macros from other files.

So it this point I need a way for the two excel files to see each other. Any input on this?

Thank you!

/Silas

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Silasdp now I understand your issue. As far as I know, there is no way to reference where the macro should be executed. You need to make amends to your macro so that It can identify the correct Workbook where it should be executed. If you have dynamic workbook names, you can always provide the workbook name (or workbook path) as a macro input. Do not forget to launch the target workbook either by your macro or in the same instance. The VBA code could be something like:

Sub BadDebtCalc (WbkPath As String)

Workbooks.Open WbkPath

'//Your code goes here//

End Sub

Was this reply helpful? Yes No
ajden 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Does this issue also apply to macros stored in a Personal Macro Workbook?

Was this reply helpful? Yes No
SpencerM3 10 on at

Like
a
(0)

Report
Copy link

Link copied!

I am running into a similar issue. What I’ve found is that the Excel file I want the macro run on is in another instance of Excel. Macro’s have to run on Excel worksheets in the same instance, best I can tell.

so my question is, how do we tell Excel to open the workbook with the macro, then open the next workbook in the same excel instance? Without coding the steps to use the UI to find the file and open it?

I’ve tried Attach to existing Excel but it’s not working for me. Not sure if understand that function though.

Was this reply helpful? Yes No
fraenK 2,125 on at

Like
a
(0)

Report
Copy link

Link copied!

When you open an Excel files you'll get an instance, use this instance variable to run the macro. If you open another Excel file you'll get another instance which should go into a different instance variable.

Was this reply helpful? Yes No
Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Silas, I was running into this same issue and created a solution. Unfortunately, when using the "Launch Excel" command, it will ALWAYS open a new (Windows) instance of Excel. The second workbook cannot see the macro in the first workbook which I believe you figured out by reading the replies. Therefore, we must open the second workbook inside of the same (Windows) instance as the first one; this takes a few more steps.

1. Use "Launch Excel" to open the Macro Excel Workbook to %ExcelInstance%
2. Use "Click UI element in window" to click on File
3. Use "Click UI element in window" to click on Open
4a. The easiest way is to pin workbook2 to the top of your Open options and use the "Click UI element in window" to click on the filename. A more thorough approach (that would work for anyone with that workbook not pinned) would be the following:
4b. Use "Click UI element in window" to click on Browse -> Use a "Wait Image" to find the dialog box that opens, I usually select the "File Name:" to the left of the flashing cursor -> Use "Send Keys" to send the document path/filename and press Enter. ex. C:\Documents\accounts.xlsx{Return}
Now the new Excel workbook will be open and will be in the same (Windows) instance as your macro workbook and will have access to all the macros.
5. Use "Attach to running Excel" addressing the workbook2 and attaching it to the original Power Automate Instance %ExcelInstance% Warning: whatever workbook is the last workbook attached to %ExcelInstance% will be the only workbook that the bot will read/write to. ALSO: if you have an "Attach to running Excel" command that already is the active workbook to %ExcelInstance%, the bot will fail claiming the file does not exist. So feel free to bounce back and forth between workbooks, but you cannot have a redundant "Attach to running Excel" command or it will kill your bot.
Now your workbook2 is open, it is active to %ExcelInstance%, and can see the macros from your macro workbook.
6. Use "Run Excel macro" to run the requested macro on the active workbook (workbook2).
In this case, Excel instance is %ExcelInstance% and Macro is the same format that the workbook2 sees the macro as if you went to View -> Macros, and selected it yourself: '[WorkbookFilename]'![MacroName].
Here is an example from one of mine: 'Month End Checklist Rev 02.17.20_macros.xlsm'!Clear_All

Was this reply helpful? Yes No
Jedikevin 15 on at

Like
a
(1)

Report
Copy link

Link copied!

I also had this issue, and found a solution. Additionally my problem was that the file name for the object file I wanted to use a macro on was dynamic.

Solution:

Using 'Launch Excel' I opened the file with the macro stored in it, as the 'excelinstance'.
Use 'Get Files in Folder' to find out the dynamic name of the file I want to use the macro on
Use 'Write to Excel worksheet' to insert the file name/path into a cell (in the excel file with the macro)
My VBA code refers to that cell and opens the new dynamic file as the first part of its process. So to activate it: Use 'Run Excel Macro'

Example VBA code:

Dim filename As String
filename = Range(A2) 'A2 being the cell I sent the dynamic file name to
Workbooks.Open filename

I found this approach worked for me. Both files are within the same instance and the macro worked on the newly opened file not the wrong one.

Was this reply helpful? Yes No
Silasdp 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Michael.

I didn't get the attach to running excel to work. Kept stating that the file didn't exist, even though I hadn't jumped between workbooks or had other a attach to running excel in my flow.

Good thing though, it seemed to work fine without it 🙂

Silas

Was this reply helpful? Yes No