Sample Workflow: SharePoint, Excel, Access Integration

image for article

Enterprise application integration is today’s new normal for businesses. So, for this month’s sample workflow, we would like to demonstrate how to launch data integration between different applications in order to share information between multiple departments.

We are huge advocates of moving data across applications and platforms. Many companies use Microsoft SharePoint and a storage database such as Oracle or SQL to manually share their data between departments and organizations. Instead of relying on this time-consuming practice, AutoMate can process and integrate this same information with ease. While application integration easily works between SharePoint and Oracle, sometimes pesky Excel and Access still need to be included in that process. So for this example we will look at the application integration between SharePoint, Excel and Access.

Data Integration

The workflow objects in this sample include two event triggers: a schedule event to run daily at 7:00 AM and a Hot Key event generated when pressing “Ctrl+Shift+B”.  The “SharePoint Download File”* task downloads an Excel Worksheet called “parts.xlsx” from the SharePoint site to a local folder called C:\test.  If the file download is successful, the information is extracted directly from Excel and imported into a Microsoft Access database with SQL query actions in the “Import Excel into Access”* task.  If the SharePoint download or “Import Excel into Access” task generates an error, the “Send Workflow Failure Email” task sends an email to the administrator and the workflow ends.  Once the workflow runs successfully, an email notification is sent to the Administrator, stating the task is complete. 

As mentioned earlier, although SharePoint, Excel and Access are used in this workflow, AutoMate and AutoMate BPA Server can be used to integrate a multitude of other applications.  We have more than 525+ activities available for automation.  Additionally, the SQL Query actions used for importing the information into Access also applies to SQL or Oracle in the exact same way, utilizing an insert query statement. 

To download this workflow and apply it to your environment, click here.  

*The task can also be used in any version of AutoMate 9.