🔬 Uniface & Excel: Reading Cells & Managing Handles (Part 2)

#programming #microsoft #automation #tutorial

Welcome Back! 🛠️

In Part 1, we launched Excel from Uniface. Now, let's get to the real work: reading data.

To interact with Excel, you have to traverse its Object Model Hierarchy. It looks something like this:

Application → Workbooks → Workbook → Worksheet → Range (Cell)

In Uniface, each step in this chain requires its own handle.

Step 1: Opening a Workbook 📂

First, we need access to the collection of open workbooks. Then we can tell that collection to open a specific file.

variables
  handle vExcelHandle
  handle vWorkBooks
  string vFileName
endvariables

vFileName = "C:\temp\MyData.xlsx"

; 1. Get the Workbooks collection from the Application handle
vExcelHandle->GET_WORKBOOKS(vWorkBooks)

; 2. Open the file
; Note: The "-" characters are placeholders for optional Excel parameters.
vWorkBooks->Open("UT_OPTIONAL", vFileName, -, -, -, -, -, -, -, -, -, -, -, -, -, -)

💡 Pro Tip: Excel methods often have many optional parameters. Uniface signatures usually handle this with specific constants (like "UT_OPTIONAL") or by using placeholders (-), depending on how the signature was generated.

Step 2: Selecting the Sheet & Cell 🎯

Once the file is open, we need to grab the active sheet and then target a specific cell (Range).

variables
  handle vActiveWorkBook
  handle vActiveSheet
  handle vCellRange
  string vValue
endvariables

; Get the currently active workbook
vExcelHandle->GET_ACTIVEWORKBOOK(vActiveWorkBook)

; Get the active sheet
vActiveWorkBook->GET_ACTIVESHEET(vActiveSheet)

; Get Range "A1" (passed as a variable or string)
vActiveSheet->GET_RANGE(vCellRange, "A1", -)

; Finally... READ THE VALUE! 🎉
vCellRange->GET_VALUE(vValue, -)

putmess "Value in A1 is: %%vValue"

Why so many handles? 🤯

It might seem verbose, but this "handle-hopping" gives you precise control. You aren't just "reading a file"; you are manipulating a live Excel session. You could easily modify this to loop through rows 1 to 100 by changing the range dynamically in a Uniface loop!

Memory Management 🧹

Every newinstance or handle you create consumes memory. When you are done with a cell, sheet, or workbook, it is good practice to release them.

; Example of explicit cleanup
vCellRange->Release()
vActiveSheet->Release()

Coming Up Next...

Reading is great, but what about writing? In the final part, we will update the spreadsheet, save our changes, and close Excel gracefully.

See you in Part 3! 🚀

Source: Rocket Software Community

DEV Community