[Solved] Base: Set Macro Variable to Form Value

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

[Solved] Base: Set Macro Variable to Form Value

Post by jmadero »

Hi All,

I'm trying to pass a value that is in a Text Box to a variable. The ultimate goal is to search for the value in the text box in Google.

Code: Select all


Sub GoogleBookTitle
Dim launcher As Object
Dim aWebPage As String   

   form_container = ThisDatabaseDocument.FormDocuments
   myLibraryform = form_container.MyLibrary

   book_name = myLibraryForm.getByName("BookTitle").string

   launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
   aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Book Title: "&MyLibraryForm
   launcher.execute(aWebPage, "", 0)
End Sub

I'm hitting a problem with this line:

Code: Select all

   book_name = myLibraryForm.getByName("BookTitle").string
Form name is "MyLibrary", textbox name is "BookTitle".
Last edited by Hagar Delest on Wed Jul 18, 2018 10:27 pm, edited 1 time in total.
Reason: tagged [Solved].
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

Try

Code: Select all

myLibraryform =  ThisComponent.DrawPage.Forms.getByName("MyLibrary")
Then your code to get the string from "BookTitle" should work.
If you use the MRI tool you could discover that yourself
[Tutorial] Introduction into object inspection with MRI
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

Get a different error when I have that code:
BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message:

Code: Select all

Sub GoogleBookTitle
   Dim launcher As Object
   Dim aWebPage As String   
   
   myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MyLibrary") 'Get Form'

   book_name = myLibraryForm.getByName("BookTitle").getString
   
   launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
   aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Book Title"& MyLibraryForm
   launcher.execute(aWebPage, "", 0)
End Sub
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base: Set Macro Variable to Form Value

Post by Villeroy »

Most simple macro for all kinds of URLs, files, emails etc: viewtopic.php?f=100&t=92331
Easy installer in the test database. No customization required within the macro code. Just add your own push button to your own form with a column name as "additional info" and the "approve action" event pointing to the macro.
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
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

I'd really like to understand why mine isn't working. The craziest thing is that someone on another forum gave me a very simple example that works without a hitch. I copied the code and get the same error as mentioned before.

he form that it's connected to is "MyLibrary". The button is the Google looking button next to "BookTitle".

My db: https://drive.google.com/open?id=1kCiKT ... PX4nMR3i4E

The actual code is in -> WebsiteButtons -> Module1 ->GoogleBookTitle
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

The craziest thing is that someone on another forum gave me a very simple example that works without a hitch. I copied the code and get the same error as mentioned before.
Copying others macro code without understanding it often leads to these kinds of errors. Part of the problem you are experiencing involves confusion over the word "form" because it can have multiple meanings. Base contributes to the problem by having a Forms area on the main database page. Actually, it should be Form Documents. Looking at the example you referenced, MyLibrary is the name of a form document. A form document is a collection of 1 or more Forms. Macro code often is not portable when it is written to work with a specific form document. One clue is whenever getByName() is used in the code you will likely have portability issues.

Looking at the example database you referenced, Open the form document MyLibrary in the Edit/Design mode.
Near the bottom left of the screen is the Form Design toolbar.
Select the Form Navigator (5th icon from the left) to open the Form Navigator dialog.
In the Form Navigator dialog looking at the structure of the form document notice that the control BookTitle appears inside the Form named MainForm.
I modified the macro code found at WebsiteButtons -> Module1 ->GoogleBookTitle to read:

Code: Select all

myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MainForm") 
Now when I press the Google button to the right of BookTitle my web browser opens to a results page of a Google search on the text entered in BookTitle.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

You can also get the correct form using

Code: Select all

      myLibraryForm = ThisDatabaseDocument.FormDocuments.getByName("MyLibrary").Component.Drawpage.Forms.getByName("MainForm")
But that has the least portability, because it requires 3 calls to getByName() to get the text you need.
Here is an more portable version of the macro. Since the button is on the same form as the TextBox control named "BookTitle" this macro doesn't need to know the name of the form document or the form, it can obtain the form object from the button event.

Code: Select all

Sub GoogleBookTitle (oEvent as object)
   Dim launcher As Object
   Dim aWebPage As String   

      myLibraryForm = oEvent.Source.Model.Parent
      book_name = myLibraryForm.getByName("BookTitle").Text
      launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
      aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=" & book_name
      launcher.execute(aWebPage, "", 0)
End Sub
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

UnklDonald418 wrote: Looking at the example database you referenced, Open the form document MyLibrary in the Edit/Design mode.
Near the bottom left of the screen is the Form Design toolbar.
Select the Form Navigator (5th icon from the left) to open the Form Navigator dialog.
In the Form Navigator dialog looking at the structure of the form document notice that the control BookTitle appears inside the Form named MainForm.
I modified the macro code found at WebsiteButtons -> Module1 ->GoogleBookTitle to read:

Code: Select all

myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MainForm") 
Now when I press the Google button to the right of BookTitle my web browser opens to a results page of a Google search on the text entered in BookTitle.
This did the trick. I really am trying to understand the underlying logic, not seeing where in the Navigator I see form "MainForm". I have the navigator open but not seeing anything with regards to "MainForm".

Thank you so much for the help. I'm sure I'll have 100x more macro related questions as I crawl through this project. Really appreciate it.
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

There is a Navigator for inspecting components of a document that appears on the Standard toolbar along the top of the screen. If the title of the dialog is just "Navigator" then you won't find what you need there.
Right click on the form document MyLibrary and select Edit.
To the left, along the bottom of the screen should be a row of icons, including Select and Design Mode On/Off, Form and Form Navigator.
If you don't find those icons then select View>Toolbars>Form Design so that there is a check-mark in front of Form Design.
The 1st entry on the Form Navigator dialog is Forms. This is an important tool that allows you not only to inspect, but to edit all the elements on a form document. Right click on a control in the Form Navigator and select Properties to open the Properties dialog for that control.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

UnklDonald418 wrote:There is a Navigator for inspecting components of a document that appears on the Standard toolbar along the top of the screen. If the title of the dialog is just "Navigator" then you won't find what you need there.
Right click on the form document MyLibrary and select Edit.
To the left, along the bottom of the screen should be a row of icons, including Select and Design Mode On/Off, Form and Form Navigator.
If you don't find those icons then select View>Toolbars>Form Design so that there is a check-mark in front of Form Design.
The 1st entry on the Form Navigator dialog is Forms. This is an important tool that allows you not only to inspect, but to edit all the elements on a form document. Right click on a control in the Form Navigator and select Properties to open the Properties dialog for that control.
Nice! Thank you again.

On the same topic now, thought about creating a new thread but it's the same issue. I've successfully made buttons for "Book Title" and I'm moving to the dropdown menu of authors. Hitting another snag and I'm unclear why.

Code: Select all

Sub GoogleAuthor
   Dim launcher As Object
   Dim aWebPage As String   

      myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
      book_author = myLibraryForm.getByName("Author").Text
      launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
      aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Author: " & book_author
      launcher.execute(aWebPage, "", 0)
End Sub
Error:
BASIC runtime error.
Property or method not found: Drawpage.
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

I'm not sure why you got that error message because DrawPages isn't the issue here.
I copied you macro code into my copy of your database and connected it to the Approve Action event for the Goggle button to the right of the box containing the author's name. I get a NoSuchElementException.
Again, using the Form Navigator look for a control named Author. There isn't one!
With the Form Navigator still open, select the box on your form that contains the author's name.
Now look in the Form Navigator and notice the the highlighted item is "List Box 1".
Replace "Author" with "List Box 1" and give it a try.
Uh-oh a different error message. Property or Method not found: Text
This is where learning to use the the MRI tool is essential if you want to write macro code.
If you inspect the ListBox control using MRI you will see that it has no Text property, thus the error message.
Looking through the list of properties there is something that looks promising, CurrentValue, and looking to the right in the MRI dialog there is the author name for the first record in your database.
Replace Text with CurrentValue and try your macro again.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

Yeah I'm going to check out that tool when I'm at home - currently in my office which makes it a bit tougher.

So - now the issue is that it's returning the AuthorID instead of the actual text that is in the dropdown. A Google Search for "11" isn't as useful as a Google search for "Maxine Hong Kingston" :-b
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

That is interesting.
I tested the macro in LO and confirm the CurrentValue returns 11. Using MRI I see no easy way to get the name needed for the search.
When I run the macro in AOO 4.15 CurrentValue returns "Maxine Hong Kingston" and the macro works as expected.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

Confirmed, does work fine in OOo.

What I'm wondering is if there's a way to do a sql search in the macro to search the Authors table to match the Author's name based on the AuthorsID that is returned in LibO. Any thoughts on that approach? I'm going to report a bug with LibO about .CurrentValue returning the wrong value.

More interesting findings!
New Database: https://drive.google.com/open?id=1kCiKT ... PX4nMR3i4E

Finding: I included nearly identical code for "Series" and connected the new macro ("GoogleSeries") to the appropriate button under the book series. In OOo this is returning the SeriesID and not the SeriesName! This is identical to the behavior in LibO for Series AND identical to LibO for the Author but inconsistent compared to the behaviour that we previously discussed (Author in OOo works fine).

UPDATE: Cannot reproduce but at least on one occasion OOo returned AuthorID instead of AuthorName.
Last edited by jmadero on Wed Jul 18, 2018 4:54 pm, edited 3 times in total.
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

This works for me in both LO and AOO

Code: Select all

Sub GoogleAuthor
       Dim launcher As Object
       Dim aWebPage As String 
       Dim oDoc as Object, oControl as Object       
       Dim item As Integer 
       
'If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
'      Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
'End If
'Dim oMRI as object
'oMRI = CreateUnoService( "mytools.Mri" )  

          oDoc = ThisComponent
'oMRI.inspect oDoc
          myLibraryForm = oDoc.Drawpage.Forms.getByName("MainForm")
          oControl = myLibraryForm.getByName("Author")
'oMRI.inspect oControl
          item = oControl.SelectedItems(0)
          book_author = oControl.StringItemList(item)
'          book_author = oControl.CurrentValue  '.Text
          launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
          aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Author: " & book_author
          launcher.execute(aWebPage, "", 0)
End Sub
I left the MRI code in the macro in case we need to do any more checking, but I did comment if out so it shouldn't interfere with normal operation.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jmadero
Posts: 62
Joined: Sun Nov 15, 2009 10:04 pm

Re: Base: Set Macro Variable to Form Value

Post by jmadero »

Seems to work! Can I ask, how is this working:

Code: Select all

item = oControl.SelectedItems(0)
SelectedItems is the dropdown I think, as for the "(0)", I'm not sure why it's 0 vs. some variable to pass AuthorID forward. Any explanation appreciated. I'm not a programmer by trade but I try to understand and learn so I can avoid bothering everyone in the future.
Bodhi 5.0, LibreOffice 6.0.5.2, and OOo
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Base: Set Macro Variable to Form Value

Post by UnklDonald418 »

Multiselection is one of the properties of a ListBox. 99% of the time that is set to No, but there are some rare situations where someone needs to select more than on item from a ListBox. To accommodate that situation SelectedItems is an array
http://www.openoffice.org/api/docs/comm ... ectedItems
Arrays in OpenOffice/LibreOffice begin with element 0 so an array with five elements will be numbered 0,1,2,3,4. When Multiselection is set to No there is only one array element, 0.
If you use MRI to inspect oControl and double click on SelectedItems it will display something like (000) = 2 where the left side in parentheses is the array index and the right side is the actual value. So, in this case

Code: Select all

item = oControl.SelectedItems(0) 
item will be 2.
StringItemList is an array of strings, so in MRI if you can double click on it to see the array contents. In this case StringItemList(2) will return the 3rd array element Joel Madero.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply