I am back.
- [1] I have many sheets
[2] I want to position the cursor at a particular point say "$A$1", in all sheets.
[3] First recorded a Macro see the code below
Code: Select all
sub MovingCursor
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher 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())
rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$f9"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args4())
end sub
- [4] Tested the Macro, it worked.
[5] Now I have to do it in all sheets.
[6] Inserted the relevant parts into another Macro and tried to run it
[7] See below.
Code: Select all
REM ***** BASIC *****
Option Explicit
Sub Main
Dim Doc as object
dim dispatcher as object
Dim Sheets as object
Dim Sheet as object
Dim Cell as object
dim args1(0) as new com.sun.star.beans.PropertyValue
Dim i As Integer 'Index variable
Doc = ThisComponent
Sheets = Doc.sheets
For i = 0 To Sheets.getCount()-1
sheet = sheets.getbyindex(i)
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1()) Rem stops at this line
Next
End Sub
- [8] But it stopped at the last line
[9] Error Message Basic Runtime Error Variable Not Defined
[10] Tried several options, but could not solve .
[11] Please could somebody help.
Oooops ! Yes that was an error on my part, but even after correction the problem persists
Corrected code see below.
Something is wrong.
Code: Select all
REM ***** BASIC *****
Option Explicit
Sub Main
rem Object is to position the cursor at the begining
rem of every sheet.
rem Date written 21/07/2018
rem Corrected on 22/07/2018 - Refer post from Zizi64
rem error continues
rem error in subroutine at the last line
rem "dispatcher : Object variable not set"
MovingCursor
End Sub
sub MovingCursor
Dim Document as object
dim dispatcher as object
Dim Sheets as object
Dim Sheet as object
Dim Cell as object
dim args1(0) as new com.sun.star.beans.PropertyValue
Dim i As Integer 'Index variable
Document = ThisComponent
Sheets = Document.sheets
For i = 0 To Sheets.getCount()-1
sheet = sheets.getbyindex(i)
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
Rem Error at last line "dispatcher Object variable not set"
Next
End sub