[Solved] Macro to select a sheet by name?

Keyboard macros or custom scripts

[Solved] Macro to select a sheet by name?

Postby Willem9 » Fri Jul 24, 2009 8:18 pm

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

Re: Macro to select a sheet by name?

Postby FJCC » Sat Jul 25, 2009 2:21 am

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   Expand viewCollapse view
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.phtml?t=4996&highlight=danny+macro+calc
English Macro Document

Some very useful tools for inspecting API objects:
XRay
MRI
Windows 10 and Linux Mint, since 2017
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
Moderator
 
Posts: 7839
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to select a sheet by name?

Postby Willem9 » Sat Jul 25, 2009 6:03 pm

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

Re: Macro to select a sheet by name?

Postby FJCC » Sat Jul 25, 2009 8:04 pm

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.
Windows 10 and Linux Mint, since 2017
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
Moderator
 
Posts: 7839
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to select a sheet by name?

Postby Willem9 » Sat Jul 25, 2009 8:50 pm

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?

Postby Willem9 » Sat Jul 25, 2009 8:51 pm

The file
Attachments
werkbladkiezenopnaam.ods
(9.4 KiB) Downloaded 1078 times
OOo 3.1.X on MS Windows Vista
Willem9
 
Posts: 22
Joined: Tue Jul 14, 2009 9:44 pm

Re: Macro to select a sheet by name?

Postby Villeroy » Sat Jul 25, 2009 10:29 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28842
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to select a sheet by name?

Postby FJCC » Sun Jul 26, 2009 3:20 am

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.
Windows 10 and Linux Mint, since 2017
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
Moderator
 
Posts: 7839
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Willem9 » Sun Jul 26, 2009 10:09 pm

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

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

Postby sarfaraz » Tue Jul 22, 2014 12:20 pm

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
sarfaraz
 
Posts: 5
Joined: Tue Jul 22, 2014 7:19 am

Re: Macro to select a sheet by name?

Postby hugoandresbustos » Wed Aug 05, 2015 5:39 pm

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   Expand viewCollapse view
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
hugoandresbustos
 
Posts: 34
Joined: Mon Jun 08, 2015 4:12 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests