Practice Problem 1

Objective

The objective of this practice problem is for the user to demonstrate an understanding of the Excel commands discussed in the previous section by creating an automation that utilizes said commands. The commands covered in the previous section were the Create Excel Application, Close Excel Application, Save Workbook, and the Save Workbook As commands. In the future, these commands may be implemented into other practice problems.

Scenario

An employee of the ABC company is working on a project that requires an Excel workbook containing important information of some of the employees at the company to be opened, renamed, and saved to an appropriate folder. The company’s file structuring system is changing, and the current files will no longer be usable if a change to the file names does not occur.

Prerequisites

The user must use a pre-existing Excel workbook file (.xlsx) OR create a unique file for the solution to use before starting work on the solution. The Excel file must also be closed when the process begins. Below is a screenshot of the Excel file that was created for this problem, feel free to recreate it or create a unique file. Feel free to delete this file when finished with the exercise, although this will mean that the automation cannot be run again.

Practice Problem Requirements

The user must create an automation that performs the following tasks:

Creates an Excel Instance with a unique name (User may decide instance name, but it should not be the default instance name)
Opens an Excel workbook (either a blank or an existing file) that is visible while the automation is running.
Saves a copy of the file with a different name in a separate folder (the user can decide which folder the file is saved to.)
Closes the Excel Instance once the other commands have been completed.

Problem Difficulty: Very Easy
Solution will be provided on the next page with a brief explanation.

Solution

The code for the solution has been provided below.

As a quick summary:

Step 1: The Create Excel Application command must be called first because it tells the automation that it needs to work with an Excel file instance. The variable created for the Excel Instance Name in the solution code was ‘vRenamingExercise’. The Visibility and Close Instances fields can both be set to yes. This command also allows the user to specify whether it will be opening an existing document or creating a new one and allows the developer to specify which file to open and its location in the event of the former.

Step 2: The Save Workbook As command will allow the specified Excel Instance to be saved with whatever the user designated as the name of the file in the specified file location.

Step 3: The Close Excel Application command will allow the specified Excel Instance to be closed as it is no longer needed. The Save Workbook field is set to No because the file has already been saved.

Lets stay in touch!