[Solved] Cell value into macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

[Solved] Cell value into macro

Post by det »

Is there a simple way to get the value of a cell in a spreadsheet into my Sub for further calculation? (Windows 7, Visual Basic)
What am I missing? Trying for days. Thank you
Last edited by Hagar Delest on Sun Jun 11, 2017 12:44 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Cell value into macro

Post by RoryOF »

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Cell value into macro

Post by det »

I have changed you reference from:
oSheet.getCellByPosition( 0, 1 ).setFormula( "Jan" )
to: oSheet.getCellByPosition( 0, 0 ).getValue()

Here is my Code from macro recording:
sub GetCellValue
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
dim oSheet as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
RN = oSheet.getCellByPosition( 0,0).getValue()
print RN, "in Print"
end sub
I get error "object variable not set" at RN =......
As I am not familiar with UNO, com.sun.star.beans, I try by trial and error and get nowhere.
Could you please make changes to make it work?
As I said I am trying for days. Your support is greatly appreciated.
Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Cell value into macro

Post by Zizi64 »

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
The Macro recorder works with usage of the Dispatcher. You not needed the Dispatcher, if you WRITE a macro (but not record them). Just call the API functions directly.

Code: Select all

dim oSheet as object
...
RN = oSheet.getCellByPosition( 0,0).getValue()
Where you get the sheet? Which sheet you referenced to? There is not any value of the object variable 'oSheet' in your code. You must get it by API functions before you use the 'oSheet' variable.
Last edited by Zizi64 on Sat Jun 10, 2017 6:26 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Cell value into macro

Post by RoryOF »

http://www.pitonyak.org/AndrewMacro.pdf

Accessing a cell in a Calc document, section 6.2 listing 6.3
Setting cell value, format, string or formula: section 6.3, listing 6.4
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Cell value into macro [Solved]

Post by det »

It works! Here is my code:
oSheet = oDoc.Sheets.getByName("Sheet1")
iCol=0
iRow=0
oCell = oSheet.getCellByposition(iCol,iRow)
RN = oCell.getValue()
Thank you so much. Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Post Reply