RPA Lessons Learned: Opening Excel Files on Local and Network Drives

Microsoft Excel is one of the most commonly used applications today. It is integral to all kinds of back-office processes involving the recording, storing and moving of data – and for that reason, tasks inside of Excel are some of the prime targets for leading robotic process automation (RPA) vendors, such as Automation Anywhere and BluePrism. Enterprise buyers often are interested in how RPA software can help them write automations that involve Excel inside of a larger process, whether it is copying data from one spreadsheet to another or keeping track of important data throughout a process. But automating Excel can introduce some obstacles not often thought of when initially designing and developing an automation.

One such issue happens when Automation Anywhere’s RPA solution uses its built-in Excel open command to open Excel files on local and network drives.

Automating Specific Tasks inside a Business Process

The built-in Automation Anywhere Excel open command does not explicitly require a file extension to open an Excel file. So when coding to open a file, you can provide the full file path and name without extension, C:\Users\XXXXXX\Desktop\Example, and Automation Anywhere will open the Excel file without error. This example makes use of a file stored on the user’s local drive. However, there are problems with this process when you attempt the same command with an Excel file stored on a network drive.

The same command that works to open an Excel file on a local drive fails to open the Excel file on a network drive although the file is present. There are many possible reasons why Automation Anywhere behaves this way, often related to: group policies, trusted locations and user profiles. These scenarios may work together to limit the Automation Anywhere Excel open command from functioning as you might expect. A specific set of policies and configurations may also limit local drive behavior. In all cases, it requires that you code defensively and follow best practices.

The best practice when opening an Excel file is to always explicitly call out the file extension. If the extension is known, it can be added to the file name in the configuration file used by the automation. If the extension is not known, you can use a loop command to find the extension and assign it to the file variable before providing the variable to the Automation Anywhere open command. This will ensure that Excel files open properly regardless of whether the file lives on a local or a network drive.

Additionally, by always explicitly calling out the file extension, you avoid confusion if multiple files have the same name or similar names. As an example, take the following two files, MyFile.xls and MyFile.xlsx. Both are valid files but could cause problems when a user issues an Excel open command. By default, there is a precedence when opening an Excel file, and in the example above, Automation Anywhere would open the .xlsx version, not the .xls version.

Making the Most of Your RPA Investment

To summarize:

• Always explicitly call out the file extension when issuing an Excel open command.
• If the file extension is not known, use a loop through a folder command to find the extension and assign it to the file variable.
• Request access to network drives early in the development process so that all commands can be fully tested before moving into user-assurance testing (UAT). This will limit the number of unforeseen issues in UAT and provide your client with peace of mind when testing.
• As an aside, Automation Anywhere will not open a file on a network drive if there is no file extension, regardless if Windows Explorer has the option to show extensions or not.

ISG helps companies maximize their automation investments by configuring solutions to solve their specific business problems. Contact us to discuss how we can help you.

About the Author

Jeff Horowitz is a senior consultant with over three and a half years of experience in information technology and process automation. His consulting experiences include work for such firms as Accenture and ISG (Information Services Group). His industry experience includes public sector work for the Department of Defense in addition to hospitality and agriculture. He holds a bachelor’s degree of science in Finance from the Farmer School of Business at Miami University.