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
[Solved] Cell value into macro
[Solved] Cell value into macro
Last edited by Hagar Delest on Sun Jun 11, 2017 12:44 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Re: Cell value into macro
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Cell value into macro
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
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
Re: Cell value into macro
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.dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Code: Select all
dim oSheet as object
...
RN = oSheet.getCellByPosition( 0,0).getValue()
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.
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.
Re: Cell value into macro
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
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
Re: Cell value into macro [Solved]
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
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