[Solved] Cannot read Calc cell value using VBScript

[kap]

I have just started looking at Calc and would like to see if I can get VBScript to read a cell value. So far my code looks like this:

Code: Select all

Set objServiceManager= WScript.CreateObject("com.sun.star.ServiceManager") 
Set StarDesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop") 
cURL = "file:///D:/Automation/Demo/Test_OpenOffice.ods" 
set oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, Array() ) 
set oSheet = oDoc.getSheets().getByName( "Sheet1" ) ' get by name 
set oCell = oSheet.getCellByPosition( 3, 3 ) 
nValue = oCell.getValue() 
msgbox nValue 

The problem is that regardless of which cell position I type in, the cell value always gets shown as 0 (zero) in the msgbox. I cannot figure out what I am doing wrong. Can someone please advise?

[rudolfo]

Works for me without any problems.
Please note that the cell position on a sheet starts counting at zero. getCellByPosition(3,3) returns Cell D4.
And OpenOffice is a bit picky on Cell contents: If you type in D4 '45 (with a leading quote or apostrophe forcing the content to be treated as a string) getValue() will return 0, while getString() returns 45.
In short: .getString() returns what you see in the cell and .getValue() the numerical Value in the celll, or 0 if the cell content is a string.

[kap]

Rudolfo,

ah, I did not know about the getString() - that works for me! Since my cell values are all strings, I was getting tripped up by the getValue always returning a 0. Rookie mistake on my part. I appreciate you clarifying the problem for me.

Thanks!

[rudolfo]

As your problem is not scripting language related, but is related with the variety of methods of the different UNO objects I would recommend that you download the MRI extension. It is an inspection tool (requires OpenOffice with Python) that allows you to explore the methods and properties of the current selection in Calc or Writer (and the other applications). You will see .getFormula(), getString() and getValue() and can try all of them to inspect there return value live. If you increase the area that looks like a status bar you will even see the associated macro code for your click on the methods or properties.
Alternatively there is Xray by Bernard Marcelly. This tool doesn't require Python and is a bit easier to use inside Macros, but it can only be called from macro code while MRI can be simply started from the Options menu. Google for Xray and you should find the place where to download it.
Both tools will help you to find an answer on your own if you have similar questions in the future.

You have an answer for your question. Please play nice with the community and mark the title of the initial Post with [Solved] (before the actual title, not after it). Click on the Edit Button on the left of the Quote button at your first post.

[Villeroy]

Each cell has oCell.getFormula(), which is not necessarily an expression starting with =
A localized version oCell.FormulaLocal is shown in the user interface.
The formula is a either one of number, text or an expression starting with =. The latter returns a number, a text or error. It never returns anything else. Dates, times, booleans are formatted numbers representing the same numeric value.

Each cell has oCell.getValue() [double] resulting from the formula. In case of a text value the value is zero.

Each cell has oCell.getText() resulting from the formula which is the visible representation of a number (including dates, times, percents,...) unless the formula gives a text rather than number.

A formatted number:
FormulaLocal: 12/31/1999(USA) or 31/12/1999(UK) or 31.12.1999(German) or 1999-12-31(Swedish,ISO).
Value: 36525 [number of days since day zero 1899-12-30]
Text: depends on how you formatted the cell's number. The above date can be shown as currency without changing the vlaue.
Text "36525" is also the string returned by oCell.getFormula(), the expression stored in the file sources is an ISO date "1999-12-31T00:00:00.00"

A formula expression:
FormulaLocal: =SUMME(A1:A99) [German user interface]
Value: 36525.99
Text: 36.525,99 € [depends on how you formatted the cell's number]
oCell.getFormula(): =SUM(A1:A99)

Numeric text:
FormulaLocal: '00123 [the ' inhibits numeric evaluation]
Value: 0
Text: 00123
getFormula: '00123

Excel treats Booleans as an additional type. Both formulas =TRUE()=1 and FALSE()=0 return TRUE in Calc but FALSE in Excel. FALSE/TRUE are different representations (formattings) of the values 0 and 1.
In Excel 0,1,True and False are 4 different values.