Answered

Pass parameters from PAD to Excel Macro

(0) Share

Report

Posted on by Community Power Pla...

I've built a flow that parses hundreds of files for a person's first and last name, and a date of an occurrence:

John Deer 11/19/2021

I need to write these data into an Excel workbook but I can't just drop them in. I need to run a macro on the workbook to find the instance of John Deer's name and then move over a dozen cells and plop the date in.

I can activate my macro through PAD, but I don't know how to pass variables between PAD and Excel. In VBA Outlook, you just fully qualify your macro target and then include parameters.

'testbook.xlsm'!TARGETMACRO(someparam1, someparam2,...)

Guidance is deeply appreciated.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

MichaelAnnis 5,727 Moderator on at

Like (1)

Report

2 options:

Option 1. Write the table to another sheet in the Excel workbook. The macro should implement a SUMIFS formula matching the first and last name and pulling over the date needed. I like SUMIFS vs a Vlookup because it does not error out, the result is just 0 if not found. If you already have dates that can’t be overwritten, then you should calculate the SUMIFS in a different column, and say , if SUMIFS is 0, then use previous value (from the correct column). The last step of the macro would be to copy your SUMIFS column and paste values over your actual date column, delete the SUMIFS column and finally, delete the data tab that you added.

Option 2. This would be a “for each” on your list in PAD. For each CurrentItem, PAD writes the current name and date to a specific range in Excel and runs the Macro, the macro finds the row and writes the date to the proper row. Then PAD starts the next name.

Option 1 is by far going to be faster on both the PAD side and the VBA Macro side because it will be one time for PAD to write the data and one VBA Macro.

Was this reply helpful? Yes No
Verified answer

VJR 7,635 on at

Like (2)

Report

Hi @Anonymous

The official documentation for Run Excel Macro says to separate the name and the parameters using a semi colon ;

If this is the macro...

Then pass the parameters as below

You can pass a PAD variable as follows
askname;%VariableNameHere%

Which when called from PAD will result as follows

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

Like (0)

Report

For anyone in the future who finds this particularly sticky, here's another example of a macro name and some params passed...

Sheet1.testmacro; %FirstName%; %LastName%; %NoteDate%

Sheet1 is where the macro is stored. PAD wouldn't let me target the macro without otherwise fully qualifying where it is. The second piece, "testmacro" is the name of the macro itself. Whatever parameters you have established in your macro's declaration "Sub testmacro (-some params-)" need to be given a corresponding value too otherwise PAD will throw an error.
In addition, for some reason, you also need to add a space after your semicolon ;

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

Like (0)

Report

I had briefly considered simply porting all of the data into the Excel book that the data needs to go to anyway. The reason I didn't pursue this option is that I have previous experience from another macro project of mine where I had need to take values from an Outlook email and pass them through OL VBA to Excel VBA via params. Without the use of params, my older macro project never would've come together.

I also wanted to do all of my data sorting and preparations in the same transfer process from PAD to Excel. By directly passing my vars into params I can just do the operation from PAD directly to Excel and then have Excel figure out where to drop the vars.

If'n I had like, 100+ vars, I definitely would not want to bungle with trying to pass all of them as params into Excel. Simply dropping them as a table range and then modifying to my needs would be succulent.

Thank you for the advice! I'm certain I'll need it some time in the future.

Was this reply helpful? Yes No