Hello,
To select worksheet 2 in Calc, I recorded the macro here below. I should like to change the macro for selecting a sheet by Name.
How can this be done?
sub kiestabblad
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 2
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
end sub
Last edited by Hagar Delest on Sun Jul 26, 2009 10:25 pm, edited 1 time in total.
Reason:tagged [Solved].
The documentation for the dispatcher calls used in recorded macros is limited. The various options are not documented anywhere I know of. A macro using calls to the API that makes a named sheet the active sheet is pretty simple.
document = ThisComponent 'assigns the current document to the variable document
Sheets = document.Sheets 'get the container of all Sheets
Sheet = Sheets.getByName("Sheet2") 'get the sheet named Sheet2
Controller = document.getcurrentController
Controller.setActiveSheet(Sheet)
If you want to learn more about programming macros, here are some resources:
Some very useful tools for inspecting API objects: XRay MRI
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Thank you for your reply. I have tried this code immidialtely but unfortunately it don't run.
---------
Sub kiestabblad
Dim Document As Object
Dim Sheets As Object
Dim Sheet As Object
Document = ThisComponent 'assigns the current document to the variable document
Sheets = Document.Sheets 'get the container of all Sheets
Sheet = Sheets.getByName("Sheet2") 'get the sheet named Sheet2
Controller = Document.getcurrentController
Controller.setActiveSheet(Sheet)
End sub
----------
At the line Document = ThisComponent a Msg warningBox appears “Basic-Syntax Error”, “Syntax Error” “OK” The text ThisComponent is highlighted.
When I put a REM before this line then the next line Sheets = document.Sheets brings the same warning and document.sheets is highlighted. A Rem before this line causes the error at the next line.
Then I have placed 3 Dim's at the begin of the code but without any result. There is something wrong. I have no idee what it is.
That is very strange because I tested it before I posted the code and I just copied what you posted into a document and ran it without any problems or modifications. The variable ThisComponent is a special term in OpenOffice Basic that refers to the current document. I can't think of how that line could generate a syntax error. Even if ThisComponent is misspelled, I would expect an "Object not Set" error. If you run this code in a new blank spreadsheet document, do you get the error? If you do, please post the document and I'll see if it runs on my system.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Here I am again. Thanks for the resources. I just tried the file on my notebook instead of my PC. On my notebook there are no other OOo files so no undesirable disturbance. On the Notebook the result brings the same Errors.
Now I'll try (I did that not yet before) to upload my file for a test.
There is strange whitespace (no normal space character) between the end of the line and the comment.
Doc = ThisComponent___' comment
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
When I first downloaded your file it did show the syntax error. Following Villeroy's observation I deleted all of the characters between the comments and the statements, re-entered them as spaces and now the code runs fine.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC wrote:The documentation for the dispatcher calls used in recorded macros is limited. The various options are not documented anywhere I know of. A macro using calls to the API that makes a named sheet the active sheet is pretty simple.
document = ThisComponent 'assigns the current document to the variable document
Sheets = document.Sheets 'get the container of all Sheets
Sheet = Sheets.getByName("Sheet2") 'get the sheet named Sheet2
Controller = document.getcurrentController
Controller.setActiveSheet(Sheet)