Setting up toolbars and buttons

[born2bongo]

I'm very new to Open Office but determined to make a go of it and add my bit.

I've been programming Excel for years and my first stab at recording a macro in OO has shown me how much I have to learn. I've set out to create an existing Excel program in OO. The first thing that happens on workbook open is that a toolbar is created, buttons are added and code is assigned. When I recorded this activity in OO, no code was generated. Does this mean OO doesn't have the functionality to do that, or is it just unable to record it?

Besides adding toolbars and buttons, the other activity that may prove a challenge for me is getting data off web pages within code. In Excel I use QueryTables.

If anyone has any experience of either of these activities, or has a great deal of Excel experience alongside their OO experience I would be glad to hear from them

B2B

[pitonyak]

First, let me say that the macro recorder in OOo is very poor. One might call it "almost useless".

So, you are trying to record the process of creating toolbars and such? For certain, these events will not be recorded in OOo.

If I understand your question correctly, you want to:

1. Create a custom toolbar.

2. Add buttons to the custom toolbar

What do you want these buttons to do?

The ability to do this sort of thing is very new, and I am not really certain as to the status of toolbar updates.

First, I think that you can do some of this with an add-on, but perhaps that is not what you desire.

Code: Select all

Sub NewCustBar
    REM *** Creates a new custom toolbar persistently for the Basic IDE

    REM *** The name of our new custom toolbar. A custom toolbar name MUST
    REM *** start with "custom_"!
    sToolbar = "private:resource/toolbar/custom_toolbar"
    
    REM *** Retrieve the module configuration manager from central module configuration manager supplier
    oModuleCfgMgrSupplier = createUnoService("com.sun.star.ui.ModuleUIConfigurationManagerSupplier")

    REM *** Retrieve the module configuration manager with the module identifier
    REM *** See com.sun.star.frame.ModuleManager for more information
    oModuleCfgMgr = oModuleCfgMgrSupplier.getUIConfigurationManager( "com.sun.star.script.BasicIDE" )
    
    REM *** Create a settings container which will define the structure of our new
    REM *** custom toolbar.
    oToolbarSettings = oModuleCfgMgr.createSettings()
    
    REM *** Set a title for our new custom toolbar
    oToolbarSettings.UIName = "My little custom toolbar"
    
    REM *** Create a button for our new custom toolbar
    sString = "My Macro's"
    oToolbarItem = CreateToolbarItem( "macro:///Standard.Module1.Test()", "Standard.Module1.Test" )
    oToolbarSettings.insertByIndex( nCount, oToolbarItem )
    
    REM *** Set the settings for our new custom toolbar. (replace/insert)
    if ( oModuleCfgMgr.hasSettings( sToolbar )) then
        oModuleCfgMgr.replaceSettings( sToolbar, oToolbarSettings )
    else
        oModuleCfgMgr.insertSettings( sToolbar, oToolbarSettings )
    endif
End Sub

Function CreateToolbarItem( Command as String, Label as String ) as Variant
    Dim aToolbarItem(3) as new com.sun.star.beans.PropertyValue

    aToolbarItem(0).Name = "CommandURL"
    aToolbarItem(0).Value = Command
    aToolbarItem(1).Name = "Label"
    aToolbarItem(1).Value = Label
    aToolbarItem(2).Name = "Type"
    aToolbarItem(2).Value = 0
    aToolbarItem(3).Name = "Visible"
    aToolbarItem(3).Value = true

      CreateToolbarItem = aToolbarItem()
End Function

and...

Code: Select all

Sub Main
    REM *** Removes a custom toolbar persistently from the Basic IDE

    REM *** The name of the custom toolbar. A custom toolbar name MUST
    REM *** start with "custom_"!
    sToolbar = "private:resource/toolbar/custom_toolbar"
    sBasicIDEModuleIdentifier = "com.sun.star.script.BasicIDE"
    
    REM *** Retrieve the module configuration manager from central module configuration manager supplier
    oModuleCfgMgrSupplier = createUnoService("com.sun.star.ui.ModuleUIConfigurationManagerSupplier")

    REM *** Retrieve the module configuration manager with the module identifier
    REM *** See com.sun.star.frame.ModuleManager for more information
    oModuleCfgMgr = oModuleCfgMgrSupplier.getUIConfigurationManager( "com.sun.star.script.BasicIDE" )

    REM *** Create single window state service
    oWindowState = createUnoService("com.sun.star.ui.WindowStateConfiguration")

    REM *** Retrieve the window state configuration for the Basic IDE
    oBasicWindowState = oWindowState.getByName( sBasicIDEModuleIdentifier )
    
    REM *** Remove the settings for our custom toolbar.
    if ( oModuleCfgMgr.hasSettings( sToolbar )) then
        oModuleCfgMgr.removeSettings( sToolbar, oToolbarSettings )
        oModuleCfgMgr.store()
    endif

    REM *** Remove the window state settings for our custom toolbar
    if oBasicWindowState.hasByName( sToolbar ) then
        oBasicWindowState.removeByName( sToolbar )
    end if
End Sub 

[born2bongo]

Hi Andrew

Thanks for that. I'm now happily creating and deleting toolbars and buttons at will.

I don't suppose anyone has an answer to the second question do they? I use Querytables in Excel to download constantly changing information, such as share prices, and build them into my own packages. The code line I am trying to recreate is along the lines of:

" Set theseresults = thissheet.QueryTables.Add(Connection:= _
"URL;http://www.advfn.com/p.php?pid=qkquote& ... &x=18&y=14", _
Destination:=thissheet.Cells(1, 1))"

The web page is then placed into the worksheet and the information I want will be in a predictable location. It's a bit flaky as the page can potentially be altered at any time, but for my personal stuff it works well, and I'd like to be able to do it in OO if poss.

Any ideas?