Excel Use in LabVIEW

Why Use Excel?

One of LabVIEW's best features is its ability to easily take user input from its UI and use that information to execute the rest of the program. This is a common way for users to have some control over how the program behaves based on some variables.

For example, if you were to use LabVIEW to check that a part is within tolerance using laser measurement tools, you would want to know whether that part is a cylinder or a prism so that you can command the measurement tool accordingly, and check measurements against the right set of numbers. That selection of either a cylinder or a prism could be in the form of a dropdown menu like this:

It is very easy, then, for LabVIEW to take that selection from one screen, which we'll call the "Configure Test" screen/VI, pass that selection to a "Run Test" VI, and execute the appropriate set of code.

However, what if you had a set of selections that you often use across multiple test stands? Instead of going to each stand, and selecting all the same configuration options repeatedly, one solution would be to use an Excel file that LabVIEW could read in to set the configurations. You could have multiple Excel files that automatically configure different standard sets of options, and that Excel file could be easily distributed to multiple stands.

To summarize, using Excel files to configure programs in LabVIEW is one way to standardize any configurations that need to be set for a program. Using Excel files also allows easy access to the same configurations on multiple PCs. It may not be the best way (more information on this later), but LabVIEW engineers still use this method in the field.

Setup

For demonstration purposes, let's assume we have these few configurations to set:

Let's also assume that we often test Big, Blue, Cylinders, and want to consistently set that test up without mis-selecting Small, Red, or Prism. Then, you could create an Excel sheet like the following:

With that in place, we can look at the code that reads this Excel information.

Making LabVIEW Read Excel

I have made a VI that will open Excel as an ActiveX object, extract the data in a cell as a string, and output that data. Since I am assuming that we may want to read from multiple cells, I encased the relevant part in a for loop that grabs the data from each cell listed in the array.

This VI takes in the file path of the Excel, the name of the appropriate sheet in that Excel workbook, and a cell array with the cell names that you want to read, such as A1, B4, etc. In our case, we want to read A2, B2, and C2.

Integrate subVI into Main Application

Once you have something like the above VI that can get the data you need from cells in Excel, you can incorporate that as a subVI into a larger, higher-level application. Applied to this specific example, here is what that code and the corresponding front panel may look like:

As you can see, I am giving the subVI the file path to my Excel, the Cell Array with the cells that I am interested in reading, and the sheet name. With the array of strings that are outputted, I am matching each element to what configuration I expect it to be selecting as an enum. After running it, you can see that it successfully displayed what was in my Excel: Cylinder, Blue, Big. Seems simple, right?

Thinking Bigger

Now, imagine that you had many more options to configure. For example, you may be working as part of an assembly line where different products need to be measured and tested in succession without breaks to choose a different Excel and type in different sheets and cell values. If you knew the order of the parts that were coming through, you could start to automate the entire test configurations process. Your Excel could look like this:

Now, you wouldn't have to stop between each of the 12 parts, manually configure the options to test that specific part, and repeat this process at all other test stands doing the same thing. Instead, you can have the same Excel file read by all the test stands, and the program would know how to handle, measure, and test each individual product.

Example Applications

One key application of having LabVIEW read Excel is automating test or manufacture processes, such as running durability tests. Let's say you wanted to test a new paint by varying the temperature, pressure, and humidity conditions over time.

For example, you wanted it to be at -5 C for 24 hours, then 50 C for 24 hours, and so on. Instead of manually configuring every step of the schedule for the durability test to follow from scratch and redoing that every time you wanted to test a new paint, you could make your standard testing schedule or recipe in Excel once, and distribute that to all the test stands. The configuration of tests is then automated by having LabVIEW read from Excel documents, which other applications may even populate or read.

Limitations

Although Microsoft Excel is widely used and understood, it does have very notable limitations when used in LabVIEW for such applications. The biggest pitfall is its lack of stability and robustness.

Some common concerns are:

1. The target PC's version of Excel must be the same as or compatible with the development PC's version of Excel
2. Excel must open and close in the background during runtime to read from it, so it's best to make sure Excel is not running or open before execution
3. Any warnings or popups that occur from Excel will be blocking and will cause unexpected behavior or prevent LabVIEW from reading the Excel
4. Changes to the format of the information in the Excel can break the code since it will no longer find the correct data where it used to be
5. The Excel toolkit for LabVIEW itself can be unstable and unreliable

Because of these limitations, use caution when relying on Excel. Even considering a CSV file could save some of the headaches that may result from the fragility of Excel + LabVIEW.

A better and more stable alternative would be to create a Configuration UI, which would be its own VI that is essentially an “editor” for the recipe or schedule you want to create. It would be an executable user interface for you to craft your recipe and would save the recipe either to a plain text file or a binary file on disk.

For further robustness, this file can have a custom file extension, forcing users to go through the Configuration UI to make edits and eliminating the possibility of a user “breaking” the code by improperly altering an Excel file. This configuration file on disk can then be ported to other test stands via standard methods like a USB or a shared drive on the computers.

To summarize, keep as much configuration within LabVIEW as possible and use Excel only when unavoidable, such as when supporting a client’s previously established codebase.

Learn more about DMC's LabVIEW Expertise and contact us with any questions or project inquiries.