Perform actions on a specific workbook selected from a file.

[Anonymous]

Greetings!

My situation is below which I have simplified because I have been able to design a flow that accomplishes most of what I want. I'm trying to simplify it.

I have 7 pre-built Excel workbooks stored in the same folder. Every month on the same day, I do the same exact five steps (see below) on all 7 workbooks.

The process would look like this:

Step 1) Open folder

Step 2) Open Excel workbook #1

Step 3) Refresh entire Excel workbook #1

Step 4) Print to PDF tabs 1 to 10, and tab 15 and 20.

Step 5) Close Excel workbook #1

Repeat Steps 1 to 5 (exactly as above) for Excel workbook #2, Excel workbook #3, Excel workbook #4, Excel workbook #5, Excel workbook #6 Excel workbook #7.

I have been able to build my flow so that it opens to any workbook I want and execute steps 1 through 5 above. My flow is very messy though because it consists of several subflows. Each sublfow uses the same "If then" and "Else if then" conditions to execute the same five steps.

I started with building 7 separate flows for each workbook and then I combined them all into one flow via the "If then" and "Else if then" conditions.

My question:

Since I have combined all 7 flows into 1, is there a way to tell my flow once from the beginning "Hey execute steps 1 through 5 for me regardless of if I open workbook #2 or workbook #6"?

Put another way: "If I chose workbook #1 OR workbook #3 OR workbook #5 perfom steps 1 to 5".

My goal is to simply the subflows so that it doesnt have the same "If then" and "Else if then" conditions on every subflow. I want the flow to excute steps 1 through 5.

[MichaelAnnis]

If all 5 steps are the same for all 7 workbooks, I would have 1 set of subflows, and on my main would look like this:
"Get Files in Folder" to %Files%

"ForEach" CurrentItem in Files:

Do all Subflows

[Anonymous]

Thank you for the reply! I appreciate you taking the time to assist me. Would you mind posting a screen shot of what this would look like as I am still learning how to use PAD?

[MichaelAnnis]

When creating your subflows, make sure any time you have to point to the "filename", you point to the variable %CurrentItem%.  Example - Launch Excel:

If you are using Windows UI Elements, make sure the selector is generic enough to incorporate all workbooks, for example the Refresh Button:
Let's say your 7 spreadsheets were all titled Month End Close 1 (2, 3 and so on).  When you first capture the UI element, the selector will be to Month End Close 1.xlsx only.  You can change this, so that it will be captured on all of them, and you don't have to select each separately. Follow these steps:

1) Go to your UI Elements (in the top right):

2) Hover the mouse over the "Window" Selector, and left click the 3 dots

3) Left click "Edit Selectors"

4)Hover over the text displayed, and click the 3 dots again

5) Left Click "Edit Selector"

#6 was edited 7.26.21

6) Look for the "Name" box, and manually edit the text under value to something that incorporates all the excel files.  For my example, that would be "Month End Close" and then change the "Equal To" operator to "Contains".

If you have any trouble, let me know.

[Anonymous]

Thank you for this information! I appreciate it. This makes sense but one thing I would like the flow to have is a Display Dialog Box that allows the user to pick which file they want to run the flow on and then have the flow run the sub-flows based on their selection. The subflow actions are all the same regardless of which file they want to run the flow on. Where would I incorporate that into the flow? Below is a screenshot of my current flow. As you can see, the Display Dialog Box will open, the user selects which file from the drop-down menu and then I have several If and Else If conditions performing the same action. How can I use a loop like you have shown me above, to run each subflow based on what the user selects from the drop-down men?

[MichaelAnnis]

If you only want 1 workbook ran at a time, I would use a dialog box to establish the workbook.  I am not at my computer, so I can’t check, but if dialogue box has a drop down option, I would use that; otherwise, you could use a number input to dictate which workbook. Then you can define the workbook as a variable based on the selection. To do this, you would use  the “Switch” and “Case” commands to define the workbook variable based on the user selection.

If you want more than 1 workbook ran as an option then I would add a True/False dialog box for each workbook, so they could select only the ones they want ran. Then have the bot say “If true, add file to list” for each dialog box variable, and then loop through the workbooks as we had discussed before. 

Good luck.

[Anonymous]

Would you be able to provide me with another screenshot of what that would look like? I tried following along but I dont think my actions are ordered correctly.

[MichaelAnnis]

Get files to the list %Files%

In Display "Select List from Dialog", you have them choose from the list of files.

This is what it will look like for the user...they simply check off the workbooks they want to run:

Then you set up the For Each item in %selectedindexes%...for my example, this would be [0] and [1].

That will give you "Current Item", so it will run [0] first, and then [1] second in my example.

For Launch Excel, tell it %Files[CurrentItem]%, so the first time, it would be Files[0] and the second time, it would be Files[1].

Good luck.

[Anonymous]

Beautiful - you are such a great help! Sorry but I have one last question: is there a way to rename the list of files in the drop down so that it would show something easier to read like "Workbook #1", "Workbook #2", etc instead of the file paths?

I also do not want to give the user the option to select more than one file to open. When I adjust that setting in the Display Select from list dialog box, I get an error message. Any fix for that?

Would I have to create input variables with the file path and then set variable with the name i want?

[MichaelAnnis]

Instead of %Files% in the dialog box, try %Files.Name%.

[Anonymous]

Not sure what you mean by that, could you clarify?

Instead of running this as a loop, I need it to run once. I want the user to have to choose to run another workbook which would execute the same actions and I accomplished this by using an If condition. I'm almost there: see screenshot below to see what my workspace looks like.

Workspace set up:

Display Select from list dialog which is where the drop-down menu comes in:

My If condition:

Finally, my Launch Excel action:

This set up gets me to where I want to be: grabs the folder files, displays a drop-down menu of files to choose from, the user clicks on which ever file they want, and it opens the excel workbook.

Now the only remaining issue I have is: below is a screenshot of what the drop-down menu options look like. Long ugly file paths. How can I get the drop-down menu to display a clean looking name such as "Workbook #1, Workbook #2, Workbook #3, etc." instead of a list of file paths?

[MichaelAnnis]

Adding ".Name" after Files[#] will pull in only the file name and not the whole path:

The allow multiple selection box will determine if you allow them to run 1 workbook or multiple workbooks.  If you are only running 1 workbook, then you don't need a For Each nor an If statement.  Simply do a Launch Excel opening the selected index:

If you are all done, please thumbs up and accept as solution the best response that answers the original question.

Thank you

[Anonymous]

Adding the .Name" after Files[#] works but is there a way to drop the file name extension? In this case, is there a way to remove the ".xlsm" from the drop-down menu?

[MichaelAnnis · Accepted solution]

Not to my knowledge; however, if those 7 names are always the same, you can just list them manually:

When ran, I selected workbook 4, and that still made the same %SelectedIndex% = 3, that we would expect from the previous version.

[Anonymous]

That didnt work for me but that's ok. We got close enough. Thank you again for your assistance! I sincerely appreciate you taking the time to help me with this!

[MichaelAnnis]

@Anonymous , this came across my feed, and I have an update. There is a property called “.NameWithoutExtension”. I think this is what you were looking for.