Using CSV or text files are a great way to give input or instructions to TCL macros in Surpac, however, it’s often a hassle to work in the CSV format. Not only will you have to worry about maintaining two versions of the same data (.xlsx and .csv), saving the Excel sheet into CSV will lose its formulas, references and formatting. You also have to save, taking the steps to save-as a CSV every time you make an update to the spreadsheet.

To get around these inconveniences, lately I have been using TWAPI in my Surpac macros, to interface directly with Excel, and I would like to show you how.

 

STEP 1:
I will first assign variables for the name of the excel file, as well as the range of cells you want to read from the spreadsheet.

TWAPI will need the absolute path of this file, but for simplicity’s sake in inputs, I usually just give the relative path from the working directory.

 

 

There are a lot of ways get the absolute path of a file, my preference is to use the commands file join which will combine file names with the application appropriate divider, and pwd which returns the current working directory.

 

 

I need to prepare the macro to connect with Excel. Here the TCL command package require is used to ensure the interpreter is prepared with the appropriate package, twapi. I will also use a twapi command, comobj, to create a COM instance which will connect the macro to Excel.

 

 

An optional line which I have included here uses the function DisplayAlerts to stop Excel from posting any alerts to the user. This isn’t typically a concern when we are only reading from the spreadsheet, but may be crucial when using the macro to write to an existing spreadsheet.

 

 

STEP 2
The preparation is all complete, so now I can open the open the Excel file and drill down the hierarchy to get the range of cells we’re interested in. The Excel file is opened as a workbook, and the workbook contains multiple sheets.

 

In this case, I am simply using the first sheet, or tab, in the excel workbook. Using a specific tab, is as simple as indicating its place in the order of tabs as in integer in the line which assigns the variable sheet.

 

 

An advanced alternative to this is to use a simple for loop along with the commands count and Name to search for a tab with a given name in Excel.

 

 

 

STEP 3

I’ve found the cells I want so now I just need to grab them and assign them to a new variable.

 

 

 

STEP 4

Before I carry on, I want to take the time to close out everything that has been initialized with the excel file. If this step is skipped, the macro will still run and work as expected, however, it will keep an instance of Excel running in the background and after several cycles through the macro, will start to affect the performance of your computer.

 

 

This is simply accomplished by working through the hierarchy of the excel file and using the destroy command. You also need to use quit on the application.

 

 

 

STEP 5:

Now I want to look at the values it returned from the sheet. Since the range I specified was a two dimensional table, it will return the values back to me as a list of lists.

The first level of the list will return the entire row from the range and within each of these rows, another list is defined for the column values.

The original data from the spreadsheet was a simple table with 5 rows and 2 columns.

 

 

Inspeciting the text of the data returned from the sheet, we can see the values from the columns are stored as lists, in a list of the rows.

 

 

An easy way to retrieve individual values is to use the lindex command embedded in itself to specify the location row and column positions inside of the table, keeping in mind that lists start counting the first value at 0.

 

 

Putting all of these components back together, let’s see what the completed macro looks like.

 

 

 

It’s a good idea, anytime you make a new macro that uses an Excel file to check the task manager to see if you’ve missed destroying one of the Excel components.

Also, this chunk of code fits very well inside of a TCL procedure which would keep the body of your macro neat and tidy and all you need to pass to the procedure is the file name and range, and it will return the list of values from the spreadsheet.