What are Excel connections? In order for Foxtrot to interact with Excel, a connection must be established. For every Excel-specific action, the rule of the action is the connection letter to a specific Excel document. This way, Foxtrot knows which Excel document to interact with when performing the specific action as Foxtrot can work with multiple workbooks at the same time.
In this article, we will go through some of the basic concepts of working with and understand Excel connections.
Excel connections are established when:
- Running the Create Workbook action - this will establish a connection to a new workbook that Foxtrot creates
- Running the Open Workbook action - this will establish a connection to an existing workbook that Foxtrot opens
- Building and running any Excel action to an opened workbook that does not have any connection yet
If the name of the Excel file is consistent, then a connection may be created once, and that connection is saved within your Foxtrot project. There are some instances where this method may not be ideal:
- When a file name changes (but is predictable, usually contains a date structure)
- When the connection needs to be established midway through a script
- When the user wants to use the same actions for multiple workbooks
- In the case in which a file name may be predictable, however, setting a dynamic connection is essential for handling these types of examples.
Please note that file name in Excel connections is case sensitive.
Working with connections
As mentioned in the beginning of the article, connections to Excel workbook are automatically generated when running a Create Workbook or Open Workbook action, which are the typical (and recommended) actions to use when either creating new Excel documents or opening existing ones. In the following screenshots, we will work with the Create Workbook action, but the same applies for the Open Workbook action. If we create a new Create Workbook action:
Notice that you are asked to specify the connection letter. If this is the first Excel document in your Foxtrot project, you should set it to "A".
After running the action, Foxtrot will have created a new workbook. Notice how you are now able to view the list of Excel connections in the project pane of your Foxtrot.
If we repeat the above to create another Excel workbook, we should give this the next letter in line, which would be B. Again, notice how Foxtrot automatically establishes a connection to the workbook.
Now, the Excel connections will be what Foxtrot is using in the rules of the actions to know which of the workbooks to perform the action on. So, if we drag-and-drop to the first of the two workbooks, "Mappe1", Foxtrot will automatically in the rules of the target set it to the connection "A".
To view the rules of the target, click on "Rules". Notice how the connection is set to "A".
This is how Foxtrot knows that whenever it is executing an action with the Excel rule set to connection A, it is supposed to, in this case, engage with the workbook with the name "Mappe1". If we make a simple Send Value action and run it, then you can see that it sent the value to the correct workbook.
So, for example, if we change the name of the first document (by manually saving it as something else) and then attempt to run the Send Value action again, the action will fail as Foxtrot will not be able to find a workbook with the name "Mappe1".
But, if we go ahead and manually update the name of the workbook for the Excel connection "A", then the action will work again.
Of course, you would want to be able to set and update your Excel connections dynamically through a project, and you can do that by targeting the whole window of Excel in order to create connection-related actions. These actions are especially relevant when you need to work with an Excel document that you did not create using Create Workbook or open using Open Workbook, for example, if the document was opened after downloading it from a website, then you would use the Set Connection action to establish a connection to the workbook in order to be able to work with it.