How to get linked Excel workbooks to work together dynamically?

[Anonymous]

My situation is below which I have simplified: I have several "Parent" workbooks that are dependent on a "Child" workbook that has been linked to it. The "Parent/Child" linkage looks like this:

Workbook #1 (Parent) --Link-- Workbook #1.2 (Child)

Workbook #2 (Parent) --Link-- Workbook #2.2 (Child)

Workbook #3 (Parent) --Link-- Workbook #3.2 (Child)

Workbook #4 (Parent) --Link-- Workbook #4.2 (Child)

The linkage is only a "One-to-One" relationship. (if you will) Meaning there will never be a situation in which Workbook #1 would be linked to Workbook #3.2.

The Process:

Step 1) Open the Parent Workbook

Step 2) Go to the Data Tab > Edit Links > Click on the Child Workbook in the pop-up dialog box > Click Open Source

Step 3) Wait for the Child Workbook to open and refresh the entire workbook so the formulas update with the current data

Step 4) Come back to Parent Workbook and refresh the entire workbook so the formulas update with the current data

Step 5) Print to PDF all tabs in the Parent Workbook

Step 6) Close both Workbooks

If the Child Workbook is not refreshed, the Parent Workbook will only show #VALUE! errors everywhere because the formulas are dependent on there being refreshed numbers in the Child Workbook.

My Problem:

In its current state, my flow is only dynamic up until the Child Workbook has been opened. After that, everything is basically put together via If and Else If conditions and certain actions (like Wait for Window or Set Window State) that will only execute if the title of the window is open. Example: "Set Window State" to Maximized will only execute if "Child Workbook - Excel" title is open and focused on. The Conditional setup (nightmare) looks like this:

If the %SelectedParentWorkbook% equal to (=) Parent Workbook #1, Find Window Title: "Child Workbook #1.1 - Excel" and set Window State to maximized.

Else If the %SelectedParentWorkbook% equal to (=) Parent Workbook #2, Find Window Title: "Child Workbook #2.1 - Excel" and set Window State to maximized.

Etc....for the remaining two Parent Workbooks.

With this setup, the flow does not work consistently because of some issue with it recognizing the Child Workbook title. (I think)

My Current Setup:

I need to entire flow to be dynamic, and I am halfway there. Its dynamic in the sense that the flow starts with a Display Select from List Dialog box. The user clicks on the file, let's say, Parent Workbook #3 and the flow kicks off by opening Parent Workbook #3 and executing several subflows based on the information captured in the Flow Variables. The first half of the flow will act the same regardless of if you select Parent Workbook #1 or Parent Workbook #4.

My Goal:

I would like to have the second half of the flow be just as dynamic as the first. Meaning, I want the flow to know that if the user selects Parent Workbook #4, Child Workbook #4.1 has to be used later downstream thus allowing the subflows to capture all the relevant information about Child Workbook #4.1 in the Flow Variables to make everything dynamic. I don't want to have to specifically spell everything out with If and Else if conditions because the flow is too messy and in inconsistent.

So, if I were to go back to what I mentioned above about the Wait for Window or Set Window State actions:

Instead of recognizing the window by the Child Workbook title word-for-word, I would rather it recognize the window based on the Child Workbook title name stored in a Flow Variable, let's say, %ChildWorkbookTitle%. It would look like this:

Wait for Window %ChildWorkbookTitle% to Become Focused.

Set Window State %ChildWorkbookTitle% to Maximized.

I know it's a long post, but I thank you for taking the time to read through it and welcome any and all assistance.

[MichaelAnnis]

1) Launch Excel - open parent workbook

2) Run Excel Macro

3) Save and close parent

Bot done

Have the macro do all the work, open child, refresh child, save and close child, refresh parent.

[Anonymous]

I agree running a Macro is the path of least resistance, I am hesitant to create a Macro for two reasons:

1) These workbooks are big, and several people use them monthly to make real decisions related to our business. These workbooks are rolled over and ran every month to create an internal report so I am concerned that putting a macro in them might cause problems down the road. 

2) Currently, I am in the process of developing several flows to automate our entire monthly close process. Since I am the primary developer, owner, and user of these flows, it's imperative that I develop them, keeping in mind, that they will be passed along to the next person who takes my job due to natural career progression. I cannot assume that the next person will have the skills necessary to troubleshoot any issues with a Macro.

With that said, I believe there is a solution to this problem, but I have exhausted all options. Any additional assistance is greatly appreciated.

Thank you!

[MichaelAnnis · Accepted solution]

Probably won't answer all your questions, but will help on the "my problem" section above:

Instead of nested IFs, use Switch -> Case which will allow you to determine an outcome based on a single parameter:

Another help will be with Windows UI elements.  Assuming you know the titles to all the workbooks and are holding them as variables, you don't need "Name" in the UI element selectors, remove "Name" from the Window Selector, that way, it will point to anything in the front active Excel Window, such as Data -> Edit -> Refresh, and you won't have to pick those selectors multiple times for each workbook.

[Anonymous]

Doesn't solve the problem 100% but it gets me close enough. Thank you (again) for your assistance! I really appreciate your help.