Example Code

Convert Excel cell address, RC to A1 and A1 to RC with error checking using LabVIEW

Products and Environment

This section reflects the products and operating system used to create the example.

To download NI software, including the products shown below, visit ni.com/downloads.

    Software

  • LabVIEW

Code and Documents

Attachment

Overview
The RC to A1.vi and A1 to RC.vi converts Excel addresses from row/column numbers to A1 type address and vs using ActiveX calls only.


Description
The RC to A1.vi and A1 to RC.vi converts Excel addresses from row/column numbers to A1 type address and vs using ActiveX calls only. The validity of the row and column input is checked. The vis have the same functionality as the Excel get Excel location.vi and Excel set Excel location.vi that are part of the Report generation for MS Office package.

Version 2: Error in column address calculation in RC to A1.vi fixed. Previous version failed to calculate correct column address after column number 675 (YZ). Current version calculates all addresses within Excel's address space (column 0 - 16383 or A - XFD) correctly (Excel 2014).

The A1 to RC.vi takes an Excel cell address, e.g. A1 and returns the corresponding row and column number. Row and column numbers are zero based, e.g. A1 is returned as row 0 and column 0. -1 is returned if address is invalid.

The RC to A1.vi takes a row and column number and returns an Excel type address, e.g. row 0 and column 0 returns A1.


Requirements

Software:

  1. LabVIEW 2012(or compatible)


Steps to Implement or Execute Code

  1. Open A1 to RC.vi.
  2. Enter an Excel address, e.g. JK123
  3. Run the vi and obsevere the returned row 122 and column 270 values
  4. Enter an invalid address, e.g. a-123, run the vi and observe that row and column numbers both are returned as -1
  5. Open RC to A1.vi
  6. Enter a row and column number, e.g. 122 and 270
  7. Run the vi and observe the returned Excel address JK123

 

Additional Information or References
VI Block Diagram

 1.png

 **This document has been updated to meet the current required format for the NI Code Exchange.**

Example code from the Example Code Exchange in the NI Community is licensed with the MIT license.