I have a workflow that will utilize a macro within an excel template used in many different projects.
Is it possible to structure a re-useable workflow that will accept either a workbook variable being passed OR a string with the workbook path being passed?
In some projects the workbook in question may already be open. In that case, I’d prefer to just pass the workbook variable over so I can keep the workbook open. In other cases, the workbook won’t be opened or initialized yet. In those cases, I would specify the workbook to be used as string/file path.
Is this feasible using something as simple as an if activity to check the argument value? Any downsides I’m not thinking of doing things this way? Should I instead always be passing a string only or a workbook only?
On a similar note, how to handle it if some of the parent workbooks would require a password, while others don’t?
I’m starting to think this might need to be something that is built into each workflow separately instead of having a master workflow that gets invoked instead…
For the first question, about the situation whether the workbook is already opened: simply use the ‘Excel application scope’. If the workbook is not opened, UiPath will open it and execute the activities in that scope. If the workbook was already open, then it will execute the activities. In both cases you can then use the output workbook variable of that ‘Excel application scope’ activity for your next activities.
For the password protected workbooks: you can use the ‘Invoke VBA’ activity to check whether a workbook has a password. Here you go:
Function Main() as Boolean
Main = ActiveWorkbook.HasPassword
You can now use the output variable, which is a boolean, for a if activity. In case the workbook was password protected (true), you can make a workflow to insert the password etc.
Thanks for the response Bolletje.
If a workbook is opened, then I pass the string to the excel application scope, I believe it opens the workbook again in read-only mode. I’d like to avoid this if possible. Also, the password(s) I was worried about are for opening the workbook themselves, not for unprotecting the workbook.
I was thinking of structuring the workflow as follows:
- In arguments of file path string, password string. In/out workbook variable
- If workbook variable is nothing, THEN Excel application scope with file path string and output workbook variable, ELSE (nothing put in here)
- Excel application Scope using existing workbook variable and continue rest of process. Also put in password string. If no password is passed, then it will still open workbooks without a password (tested this part myself on just a single workbook though).
Does anyone see any flaws in going with this approach?
For both cases, whether workbook is already opened or not, you can use the Excel Application Scope and the file path as the input. If the workbook is already opened it won’t do anything, it remains in the current state and will not close the workbook after the activities have been processed. I have just tested it, and it won’t open it in read-only mode.
So the if statement in your listed item 2 is not needed.