Page 1 of 1

[Solved] Macro to select a sheet by name?

Posted: Fri Jul 24, 2009 8:18 pm
by Willem9
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?

Code: Select all

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

Re: Macro to select a sheet by name?

Posted: Sat Jul 25, 2009 2:21 am
by FJCC
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.

Code: Select all

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:

http://www.oooforum.org/forum/viewtopic ... macro+calc
English Macro Document

Some very useful tools for inspecting API objects:
XRay
MRI

Re: Macro to select a sheet by name?

Posted: Sat Jul 25, 2009 6:03 pm
by Willem9
Dear FJCC,

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.

Regards, Willem

Re: Macro to select a sheet by name?

Posted: Sat Jul 25, 2009 8:04 pm
by FJCC
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.

Re: Macro to select a sheet by name?

Posted: Sat Jul 25, 2009 8:50 pm
by Willem9
Hello FJCC,

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.

Regards, Willem

Re: Macro to select a sheet by name?

Posted: Sat Jul 25, 2009 8:51 pm
by Willem9
The file

Re: Macro to select a sheet by name?

Posted: Sat Jul 25, 2009 10:29 pm
by Villeroy
There is strange whitespace (no normal space character) between the end of the line and the comment.
Doc = ThisComponent___' comment

Re: Macro to select a sheet by name?

Posted: Sun Jul 26, 2009 3:20 am
by FJCC
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.

Re: [Solved]Macro to select a sheet by name?

Posted: Sun Jul 26, 2009 10:09 pm
by Willem9
Hello Villeroy, Hello FJCC,

Yes, Bingo. Now it runs.I hope never to make this mistake again. It costed me several hours without result. Thanks for helping me.

Regards,
Willem

Re: [Solved] Macro to select a sheet by name?

Posted: Tue Jul 22, 2014 12:20 pm
by sarfaraz
Declaration not correct
Dim Sheets As Object
Don't mention Sheets, used that present inside the thisComponent contains.

Re: Macro to select a sheet by name?

Posted: Wed Aug 05, 2015 5:39 pm
by hugoandresbustos
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.

Code: Select all

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)

Thank you very much. This was very useful for me.