[Solved] Macro to select a sheet by name?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Willem9
Posts: 22
Joined: Tue Jul 14, 2009 9:44 pm

[Solved] Macro to select a sheet by name?

Post 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
Last edited by Hagar Delest on Sun Jul 26, 2009 10:25 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 3.1.X on MS Windows Vista
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to select a sheet by name?

Post 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
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.
Willem9
Posts: 22
Joined: Tue Jul 14, 2009 9:44 pm

Re: Macro to select a sheet by name?

Post 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
OOo 3.1.X on MS Windows Vista
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to select a sheet by name?

Post 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.
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.
Willem9
Posts: 22
Joined: Tue Jul 14, 2009 9:44 pm

Re: Macro to select a sheet by name?

Post 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
Willem9
Posts: 22
Joined: Tue Jul 14, 2009 9:44 pm

Re: Macro to select a sheet by name?

Post by Willem9 »

The file
Attachments
werkbladkiezenopnaam.ods
(9.4 KiB) Downloaded 1433 times
OOo 3.1.X on MS Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to select a sheet by name?

Post by Villeroy »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to select a sheet by name?

Post 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.
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.
Willem9
Posts: 22
Joined: Tue Jul 14, 2009 9:44 pm

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

Post 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
OOo 3.1.X on MS Windows Vista
sarfaraz
Posts: 5
Joined: Tue Jul 22, 2014 7:19 am

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

Post by sarfaraz »

Declaration not correct
Dim Sheets As Object
Don't mention Sheets, used that present inside the thisComponent contains.
OpenOffice 3.4.1 on Windows XP
hugoandresbustos
Posts: 34
Joined: Mon Jun 08, 2015 4:12 pm

Re: Macro to select a sheet by name?

Post 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.
OOo 4.1.1 - Vista Home Prem. 32-bit
Post Reply