What is the role of Robotic Process Automation in Digital Transformation?
What is the role of Robotic Process Automation in Digital Transformation?
I was recently asked a few questions that got me thinking what is the role of Robotic Process Automation in Digital Transformation? So what prompted that? Well, I had the privilege of presenting globally to my VMware colleagues as part of the internal RADIO conference.
“RADIO is VMware’s unique, internal, three-day technical conference that brings together more than 1,400 of our worldwide engineers. We share innovative ideas, learn about technical strategies and projects happening across VMware research and development and allow our developers to network and have fun with their peers.
The heart and soul of RADIO is the Research Program, where proposals are reviewed and selected to become research talks and poster sessions. This event has been VMware’s biggest innovation catalyser for the past 13 years. Engineers walk away with new ideas, different perspectives and new contacts, which help them solve some of our thorniest challenges.”
My three minute presentation provided a whistle stop overview of how I’ve used Robotic Process Automation (RPA) to automate many of my day to day tasks. I’ve only really scratched the surface of what is possible with RPA, I’ve been using it to produce reports, track communications, audit actions and copy files.
However, I obviously struck a chord with many of my colleagues, who got in touch after my presentation to ask how they could get started with RPA. I included the below links for those wanting to start learning about RPA and what I’ve been doing, at the end of my presentation and shared this with my colleagues.
Then the question came through that made me wonder about what the fundamental reality of ‘Digital Transformation‘ is for many?
Defined by the wikipedia article I’ve linked to above Digital Transformation is defined as;
“Digital Transformation is the use of new, fast and frequently changing digital technology to solve problems. It is about transforming processes that are non digital or manual to digital processes”
Which I think is a nice way of putting what DT/DX should be, although I would disagree that a technology needs to be ‘new, fast and frequently changing’ in order to play a role in digital transformation. At it’s heart DT should be focused on using technology to solve problems, expose data, gain insight and work smarter. However, almost in the same breath the text goes on to state…
“…digital transformation is cloud computing. It reduces reliance on user owned hardware and increases reliance on subscription based cloud services. Some of these digital solutions enhance capabilities of traditional software products (e.g. Microsoft Office compared to Office 365) whilst others are entirely cloud based (e.g. Google Docs).”
Putting aside the valid arguments that the above source seems to state that cloud computing is a place rather than a cloud being defined as, on-demand self-service, broad network access, resource pooling, rapid elasticity with measured service. Nothing listed there about location and who owns the hardware is irrelevant.
Anyway, in my opinion there is a disconnect that is surfaced by these sentences appearing so closely together in the same article. Ostensibly, transforming process has very little to do with reducing a reliance on hardware, and much more to do with the transformational potential created by opening up access to data and breaking down a complex process to raw functions. It’s this function based approach (often referred to as serverless) that holds transformational potential. What does an IaaS move to the cloud do to transform processes outside of the IT department?
The Question
“I am working on a project where we need to extract data from Word documents to Excel, so it can be reported on. Do you think a robot could help?”
Well by the definition this is Digital Transformation, someone is asking for help in “transforming processes that are non digital or manual to digital processes”.
Undoubtedly a robot could help. We could build a robot process that opened up each document, used OCR integrations to extract data and then use string manipulation to capture the data being requested. However, the most important part of any RPA project has to be in recognising the correct tool for the job. Just because you can build a robot to do something does not mean you should. In this circumstance, we could achieve the desired goal with VBA.
Looking at the Word documents to be imported, the information that needs to be gathered is stored in Word content controlled locations. That’s important as it means that we can use existing VBA methods to extract the data from Word to Excel.
The Solution
Starting with a blank workbook, with developer mode enabled, I wrote a simple module to look at a directory and capture the file names stored within it. For no other reason then it’s good to know where we are supposed to be capturing information from. This module requires the ‘Windows Scripting Runtime‘ reference to function.
Sub getfilenamesinexcel()
Dim fso As Scripting.FileSystemObject
Dim fsofolder As Scripting.Folder
Dim fsofile As Scripting.File
Set fso = CreateObject("scripting.filesystemobject")
Set fsofolder = fso.GetFolder("C:\WordDataForExport")
ce = 2
For Each fsofile In fsofolder.Files
Range("A" & ce).Value = fsofile.Path
ce = ce + 1
Next fsofile
End Sub
To make things straightforward, I linked this to a big button to click to execute the module.
Our next module is going to extract the data from content controlled zones in the word document. This module requires the ‘Microsoft Word 16 Object Library‘.
Sub grabWordData() Dim wdApp As New Word.Application Dim myDoc As Word.Document Dim CCtl As Word.ContentControl Dim myFolder As String, strFile As String Dim myWkSht As Worksheet, i As Long, j As Long myFolder = "C:\WordDataForExport" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWkSht = ActiveSheet ActiveSheet.Cells.Clear i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row strFile = Dir(myFolder & "\*.docx", vbNormal) While strFile <> "" i = i + 1 Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) With myDoc j = 0 For Each CCtl In .ContentControls j = j + 1 myWkSht.Cells(i, j) = CCtl.Range.Text Next myWkSht.Columns.AutoFit End With myDoc.Close SaveChanges:=False strFile = Dir() Wend wdApp.Quit Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing Application.ScreenUpdating = True End Sub
Again to make things straightforward I created a button on a new sheet that linked to the module. When executed all of the data on the Word content controlled locations is copied to our sheet ready to be interrogated, merged, appended and reported against.
Summary
There we are, no need for a robotic process. Two VBA modules and a sprinkling of logic and we have helped in ‘transforming processes that are non digital or manual to digital processes’.
No sight of the cloud or APIs, no mention of serverless or functions, no need for ‘new, fast and frequently changing digital technology’. Just a bit of patience, understanding, openness in collaboration and a now 27 year old technology, VBA.
Thanks
Simon