Use Microsoft Power Automate to automatically export SYNCHRO Pro 4D data to an Excel Spreadsheet

I have been playing around with Power Automate (rebranded from MS Flow) to set up a few interesting but essential automated workflows to improve my productivity. This service enables you to connect various applications and services together in a cloud-based, code-free environment. I can finally connect my Google family calendar to my work Outlook calendar! One would imagine this to be a simple process within the tools themselves, but sometimes the most obvious thing isn’t necessarily easily available. I know a lot of you will say the same for SYNCHRO as well!

Power Automate has introduced a new process for establishing automated workflows for legacy desktop applications by performing Robotic Process Automation, using Power Automate Desktop. This enabled me to finally use this technology for more professional use, which may benefit the SYNCHRO community at large. I can think of a few mundane and repetitive tasks within SYNCHRO Pro that can be performed through unattended automation. 

I will briefly talk about setting up UI Flows to schedule an automatic export of updated SYNCHRO 4D data to Microsoft Excel. The immediate use case scenario I am exploring is a connection to Power BI Reports and Dashboards.

 

Note: You can find more information about Power Automate here - https://docs.microsoft.com/en-us/power-automate/ui-flows/desktop/introduction

 

To get started, you can create a Flow through the web interface - https://us.flow.microsoft.com/en-us/

There are a couple of options for recording the steps of UI Flow, either directly through a Windows Recorder or installing Power Automate Desktop. I found it much easier to set up successful automation using the flexibility and customizability provided by the latter, and hence that will be the focus of this post. This is not meant to be a tutorial of any sort but just an exposure to the possibilities and my findings in this exploration.

Note: You can directly create a new UI Flow from within Power Automate Desktop as well.

Once you have a flow created, you can start editing it by selecting an action from the Actions pane. The foremost process would be to run the SYNCHRO Pro application. You can use the ‘Run application’ action and enter the path of where Pro is installed - "C:\Program Files\Bentley\SYNCHRO\Pro\Synchro.exe"

You have the option to either set up all the actions within the Main flow or create Subflows for differentiable parts of whole processes. I found this to be useful in managing my flows and testing smaller sections to easily identify and resolve issues. I established the following structure -

Main – Opens an instance of SYNCHRO Pro and run different subsequent Subflows.
OpenProject – Opens the relevant project through the SP file.
ExportExcel – Exports and overwrites an existing Excel spreadsheet.

  

You can manually select various actions from the standard list and enter the parameters manually to set up an automation, but a quicker way is to record the whole process using the Desktop recorder. This records snapshots of the user interface, mouse positions, movements and clicks, and keyboard inputs to set up the actions. All these steps can be edited and refined after the recording – an exercise I found essential to make the automation work! 

Through numerous testing, I discovered that many of the actions need to be performed in a specific sequence and enter additional actions outside of the recording in order to avoid any errors. For example, if I run the action to open SYNCHRO Pro, I need the automation to wait for Pro to complete the loading before jumping on the next action. If not, then the next mouse-click may be looking for a specific UI, which hasn’t yet loaded or selected.

Thus, as the foremost action for OpenProject subflow, I used ‘Wait for window’ and selected the Window from the list of Windows UI elements (screenshots of open windows) that were recorded during the Desktop recorder session.

  

Another example of having additional actions would be to ensure that the UI I am trying to work with is actually selected. If I have a window open to open a new file from the browser, then I want my automation to know that my clicks are not for the Pro UI but the overlaid new Open windows. I can use the action ‘Focus Window’ for this exact scenario.

To make these Flows more universal and easily deployable across multiple projects, I was able to set up a few Input Variables. Instead of manually entering the location, the name of the SYNCHRO Pro file that you want to open, or the name of the exported Excel file, you can set those values as a variable. When initiating or scheduling these flows, you get the option to enter these variables and the automation will run based on those inputs.

Even though it is easy to record the whole process by simply clicking the mouse on the ribbon interface of SYNCHRO Pro for all actions, I found that this drastically increases the scenarios that may lead to a failed automation. Having different setups with multiple screens, screen resolutions, ribbon configurations, etc. can throw off the reference positions of the recorded actions. Hence you should use as many keyboard shortcuts as possible.

Here are some of the standard windows shortcuts that I used –

Ctrl+O > Open files
Alt+D >  Edits the location address

I also set up some custom keyboard shortcuts in SYNCHRO Pro –

Ctrl+Shift+E > Export Excel
Shift+L > Open/Close Layouts

 

The following images show all the Actions that I set up in order to open a SYNCHRO Pro project, and then export an Excel spreadsheet -

 OpenProject -

ExportExcel -

 

Going back to the web interface of Power Automate, I can setup up a new Scheduled Flow, with a recurrence frequency and selecting which UI Flow from Power Automate Desktop I want to schedule.

Power Automate Desktop has a lot of more advanced Actions that I see potentially using for various other SYNCHRO Pro workflows, including OCR, Image recognition, email notifications, etc.

As a challenge, I will ask you to work on automating opening a pre-defined Layout within SYNCHRO Pro, before exporting to Excel. This will enable you to export all the necessary data required for further data analysis by connecting Excel to Power BI. I would encourage the passionate SYNCHROnians to suggest or explore various possible scenarios that can utilize automation within SYNCHRO Pro and reach out to this community to share their ideas and experiences!