RPA Part 2: Introduction to Using UiPath and Excel

This blog explores the integration of UiPath and Excel, highlighting two robust tools that streamline processes and elevate productivity within today’s digital landscape. In this comprehensive exploration, we’ll delve into the fundamental concepts of UiPath, a leading Robotic Process Automation (RPA) platform, and Excel, a ubiquitous spreadsheet software, highlighting how their integration can revolutionize workflow automation and data management. Whether you’re an experienced professional seeking to enhance workflows or a newcomer fascinated by the possibilities of these technologies, let’s delve into the integration of UiPath and Excel.

What is UiPath?

UiPath is one of the world’s leading RPA products.  When you think of C# and development using Microsoft’s .NET platform, you immediately think of Visual Studio or Visual Studio Code.  When you mention RPA to a developer or power-user, UiPath is the first tool that most often comes up.

UiPath is a leading software platform that specializes in Robotic Process Automation (RPA). RPA involves the use of software robots or “bots” to automate repetitive, rule-based tasks within business processes. UiPath provides a user-friendly environment for designing, deploying, and managing these automation workflows. It allows organizations to streamline operations, increase efficiency, and reduce human error by automating tasks across various applications and systems.

Historically in the Microsoft development universe we were presented with multiple tools that would get you from your project’s starting point to the end; however, each of those tools would represent different levels of expertise and features.  Let’s look at how business automation using SharePoint was done historically.  Usually, we could bucket those development efforts into three different channels:

1. UI Only Development.  You would develop your functionality limiting yourself to the webparts and tools available inside of your browser and occasionally supplement that with embedded JavaScript using a content editor webpart.
2. UI Development combined with [gasp] InfoPath and SharePoint workflows.  Using these tools would allow you to leverage a much more flexible form designer with control logic and actionable business logic with workflows.
3. Visual Studio development. This was used to create fully code based custom webparts and workflows giving you ultimate control of the UI and business logic of your project.

Each one of these historical channels offered increasing levels of control, but also increasing levels of expertise requirements for the developer.  Often a project was started in the first channel due to cost and personnel experience only to hit a functional glass ceiling when the business stakeholders required features that couldn’t be provided by the given technology.  This often led to uncomfortable discussions with the stakeholders as the work performed in each channel didn’t convey easily to the next higher channel incurring costly rewrites.

This dynamic still exists today with UI, Power Apps and SharePoint Foundation development – but that’s a topic for another day. When it comes to business automation and the reduction of repetitive human tasks one tool really stands out.

Enter UiPath.

UiPath is a unique tool in that it basically takes .NET development into the no-code swim lane while still allowing those familiar with .NET to use code snippets or even build their own actions in Visual studio.  In short, this tool expands through ALL of the channels; and the work developed conveys as the complexity of the situation increases.

Excelling at excel

As a developer assigned to a business unit that deals with managing a lot of transient data that needs to be imported from some cloud-based site, remote reporting tool, or even directly from data provided in emails, we would struggle with the balance between level of effort and efficiency.  A process that may need to be repeated on a daily or weekly basis for the period of six months to a year, then be retired, may not be a candidate for an extensive development cycle.

Before UiPath these tasks would typically be done in Python, a convenient and easy scripting language.  Unfortunately, not everyone is a code-level developer.  Maintenance and requirement changes required that same high level of expertise which introduced a level of risk into the process.

UiPath provides us with the means to automate these processes, interact with a multitude of integrations such as Excel, Outlook, SharePoint, OneDrive, and the file system; just to name a few.

Let’s take a look at one UiPath’s most prevalent features, the capture of data from a website, and combine it with its ability to manipulate Excel sheets.

Getting The Data for UiPath

Our first step is to get the data we want to put in our excel sheet for UiPath.  We’ll use the screen scraping utility titled ‘Table Extraction’ to get some form of data, in this case the detailed hourly meteorological data from Dulles International Airport (IAD) located here:  https://w1.weather.gov/data/obhistory/KIAD.html

We’re going to fire up a totally blank UiPath project and click on the ‘Table Extraction’ button in the toolbar.  Assuming that we have a browser loaded and pointed to the above URL the focus of your machine should switch over to that browser and a Table Extraction wizard should be displayed [Figure 1].

This wizard assists you in capturing the data in the table by allowing you to select the columns, assign column names, and preview the results.  Since this is a pretty standard HTML table element, we can let the wizard do almost all of the hard work for us.  This may not always be the case but never fear, the wizard is fairly intelligent when it comes to helping you determine what is extracted from a screen scrape.

We’ll click on the ‘Select Data to Extract’ and using the tool highlight the first element in the first row.  Because UiPath can see the table, table headers, and all of the rows, it will ask you if you’d like it to just go ahead and extract the entire table.  So let’s go ahead and do that.

As you can see in Figure 2 the Table Extraction wizards was able to find 72 rows and their appropriate headers.

The first column looks odd, right?  Let’s go ahead and clean that up by clicking the gear next to the first column and change it from its vertical representation to simply ‘Date’ [Figure 3].

With this tool we can change the import column headers, the type of data that the system should import, as well as remove any unneeded columns.  Now that we’ve corrected the first column name, we’re going to keep everything as it is, save it, and bring that data back into UiPath.

We now have two steps in our UiPath sequence.  One that loads a browser that points to the correct URL, and one that extracts the data. [Figure 4]

The important thing to pay attention to here is where and how UiPath is storing that data.  UiPath stores everything internally as a .NET Data Table, which is useful to know if you’re a developer and want to write code integrations for that data.  Right now, what we really need to do is figure out what UiPath is calling this data so we can refer to it later.  In programmer speak we call this a variable name and it’s defined in the properties window of the Extract Table Data Task.

If we click on the little plus below our “Extract Table Data” activity, we’ll be present with the ability to browse all of UiPath’s installed activities.  Because we are looking to do something with the table data we have, let’s go ahead and type ‘table’ in the activities explorer to see all of the options available to us.

As you can see, we can do a myriad of things with our data table.  We can push it to excel, export it to CSV, or push it to Google sheets or Azure. 

For right now we’re going to write the DataTable to Excel.  Using Excel, and most other Office applications, requires us to add a process scope activity.  This activity handles all of the background setup required to interact with Excel on your workstation.

We’ll add that activity, as well as the ‘Use Excel File’ activity to our sequence so that we can work with the Excel file.  In this case I’ll just be using a blank Excel workbook I’ve put on my hard drive. [Figure 6]

Now that we’ve got the two wrapper activities dropped into our sequence and picked the Excel file we’ll be using, we are ready to perform the export into excel.

In the ‘Do’ section of our ‘Use Excel File’ activity we’re going to add the activity named ‘Write DataTable to Excel’.  In the options, enter the name of variable where we imported our website data, ‘Extract DataTable’, as well as enter the location in the sheet where we want the data to be placed.  You can see that by hitting the ‘+’ next to the variables we can explore the workbook and choose our location from an intelligent menu. [Figure 7]

It’s time to hit the play button titled ‘Debug File’ and watch the magic.  You can watch UiPath do its thing as it loads a browser, navigates to the desired webpage, and copies the data into Excel.  If you blink you might miss it though!

Here’s the end result [Figure 8]:

The integration of UiPath and Excel signifies a dynamic fusion that transcends traditional boundaries in workflow automation and data management. By combining UiPath’s robust RPA platform with Excel’s multifaceted spreadsheet capabilities, organizations can unlock a new realm of efficiency and innovation. UiPath, with its intuitive interface and extensive automation capabilities, empowers users to streamline complex processes with ease, reducing manual intervention and minimizing errors. Excel, on the other hand, serves as a cornerstone in data organization and analysis, providing users with unparalleled flexibility in manipulating and visualizing data sets. Together, UiPath and Excel form a symbiotic relationship, where UiPath orchestrates automated workflows while Excel serves as a repository for structured data and insights. This integration not only revolutionizes traditional workflows but also propels organizations towards greater agility and competitiveness in today’s rapidly evolving digital landscape. As businesses embrace this synergy, they can harness the full potential of UiPath and Excel to drive innovation, optimize operations, and achieve unparalleled levels of productivity and success.

i3solutions automates processes with UiPath

i3solutions specializes in leveraging the synergies between cutting-edge technologies like UiPath and Excel to drive transformative changes in workflow automation and data management. With its expertise in implementing RPA solutions powered by UiPath, i3solutions helps organizations streamline their operations by automating repetitive tasks, reducing operational costs, and enhancing accuracy. By integrating UiPath’s intuitive interface and extensive automation capabilities with Excel’s powerful data analysis features, i3solutions enables clients to unlock new levels of efficiency and innovation.

Moreover, i3solutions provides tailored consulting and implementation services to ensure seamless integration between UiPath and Excel within your organizational ecosystem. Our team of experienced professionals works closely with you to identify key pain points, design customized automation workflows, and optimize data management processes using Excel’s versatile spreadsheet functionalities. Through comprehensive training and support, i3solutions empowers organizations to harness the full potential of UiPath and Excel integration, driving sustainable growth and competitive advantage in today’s rapidly evolving digital landscape. With i3solutions as a strategic partner, businesses can embark on a journey towards enhanced productivity, agility, and success through the seamless integration of UiPath and Excel.  Contact us today to discuss your process automation initiatives.

Up Next: Manipulating Data with UiPath and Excel

Now that we have a single data set, we will configure UiPath to do some data manipulations, move the data into a master sheet ignoring any duplicates, and set it up on an automation schedule so that we don’t have to interact with the system at all.

Keep an eye out for the continuation of this guide, where we’ll unveil practical tips on how UiPath can revolutionize your Excel processes, making data management simpler and unleashing fresh possibilities for your organization. With UiPath and Excel forming an unbeatable combination, your approach to spreadsheet operations is about to undergo a revolutionary shift.

Scott Singleton

See Full Bio