[Solved] Return properties of all controls on Calc sheet

[jrs69]

-SOLVED-
Hi All, I want to loop thru all the controls on a Calc sheet and return their properties - below is the Excel VBA code to do this, Me is shorthand for the sheet that has focus.
I haven't been able to find how to do this in OpenOffice Calc Basic.

Cheers and thks.

7 For i = 1 To Me.OLEObjects.Count
8 Debug.Print Me.OLEObjects(i).ZOrder
9 Debug.Print Me.OLEObjects(i).name
10 Debug.Print Me.OLEObjects(i).ProgId
11 Debug.Print Me.OLEObjects(i).Height
12 Debug.Print Me.OLEObjects(i).Width
13 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.BorderStyle
14 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.BackColor
15 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.ForeColor
16 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.Font
17 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.FontSize
18 On Error Resume Next ' Control properties vary
19 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.Caption
20 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.Text
21 Debug.Print Me.Shapes(i).OLEFormat.Object.Object.MultiLine
22 On Error GoTo 0 'turn on error handling
23 Debug.Print "=========================="
24 Next

Edit: Changed subject, was Calc Basic  Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

I want to loop thru all the controls on a Calc sheet and return their properties

[Tutorial] Introduction into object inspection with MRI or
X-Ray version 6

Nice introduction: Andrew Pitonyak's OpenOffice Macro Information

[jrs69]

Thks for the info, I got a bit lost with MRI, got as far as Sheets, could somebody post a code sample?

Cheers and thks.

[FJCC]

Here is the MRI Basic code to get the properties of a Combo Box on the first sheet. My UI is not in English at the moment, so the names you see will be different. The controls are on the DrawPage of the sheet.

Code: Select all

oDrawPages = ThisComponent.getDrawPages()
  oObj1 = oDrawPages.getByIndex(0)
  oForms = oObj1.getForms()
  
  oObj2 = oForms.getByName("Formulario")
  oObj3 = oObj2.getByName("Campo combinado 1")

Here is the list of properties in MRI after taking the steps above.

Code: Select all

(Name)                 (Value Type)                    (Value)             (Info.)   (Attr.)                       (Handle)  
Align                  short                           0                             Maybevoid,Bound,Maybedefault   63  
AllItems               [].beans.Pair<string,string>    -Sequence-          Pseud     Read_Only                          
Autocomplete           boolean                         True                          Bound,Maybedefault            110  
BackgroundColor        long                            -void-                        Maybevoid,Bound,Maybedefault   58  
Border                 short                           1                             Maybevoid,Bound,Maybedefault   62  
BorderColor            long                            -void-                        Maybevoid,Bound,Maybedefault  10000  
BoundField             .beans.XPropertySet             -void-                        Read_Only,Bound,Transient     154  
ClassId                short                           7                             Read_Only,Transient             9  
ContextWritingMode     short                           4                             Bound,Transient,Maybedefault   21  
ControlTypeinMSO       short                           0                             Bound                         260  
ConvertEmptyToNull     boolean                         True                          Bound                         126  
CurrentValue           any                                                 Pseud     Read_Only                          
DataField              string                          ""                            Bound                           3  
DataFieldProperty      string                          Text                          Read_Only,Transient           206  
DefaultControl         string                          com.sun.star.fo...            Bound,Maybedefault             43  
DefaultText            string                          ""                            Bound                          71  
Delegator              .uno.XInterface                                     Pseud     WriteOnly                          
Dropdown               boolean                         True                          Bound,Maybedefault             64  
EnableVisible          boolean                         True                          Bound,Maybedefault            165  
Enabled                boolean                         True                          Bound,Maybedefault            109  
FontCharWidth          float                           0.0                           Bound,Maybedefault            10001  
FontCharset            short                           0                             Bound,Maybedefault            145  
FontDescriptor         .awt.FontDescriptor             -STRUCT-                      Bound,Maybedefault             54  
FontEmphasisMark       short                           0                             Bound,Maybedefault            210  
FontFamily             short                           0                             Bound,Maybedefault            144  
FontHeight             float                           0.0                           Bound,Maybedefault            146  
FontKerning            boolean                         False                         Bound,Maybedefault            10002  
FontName               string                          ""                            Bound,Maybedefault            142  
FontOrientation        float                           0.0                           Bound,Maybedefault            10003  
FontPitch              short                           0                             Bound,Maybedefault            10004  
FontRelief             short                           0                             Bound,Maybedefault            211  
FontSlant              short                                               Ignored   Bound,Maybedefault                 
FontStrikeout          short                           3                             Bound,Maybedefault            150  
FontStyleName          string                          ""                            Bound,Maybedefault            143  
FontType               short                           0                             Bound,Maybedefault            10005  
FontUnderline          short                           4                             Bound,Maybedefault            149  
FontWeight             float                           0.0                           Bound,Maybedefault            147  
FontWidth              short                           0                             Bound,Maybedefault            10006  
FontWordLineMode       boolean                         False                         Bound,Maybedefault            208  
HelpText               string                          ""                            Bound,Maybedefault            141  
HelpURL                string                          ""                            Bound,Maybedefault            152  
HideInactiveSelection  boolean                         True                          Bound,Maybedefault            10007  
ImplementationId       []byte                          -SEQUENCE-          Pseud     Read_Only                          
ImplementationName     string                          com.sun.star.co...  Pseud     Read_Only                          
InputRequired          boolean                         True                          Bound                          19  
ItemCount              long                            0                   Attr.     Read_Only                          
LabelControl           .beans.XPropertySet             -void-                        Maybevoid,Bound               171  
LineCount              short                           20                            Bound,Maybedefault            111  
ListEntrySource        .form.binding.XListEntrySource  -void-              Pseud                                        
ListSource             string                          ""                            Bound                          46  
ListSourceType         .form.ListSourceType            TABLE                         Bound                          45  
MaxTextLen             short                           0                             Bound,Maybedefault            112  
MouseWheelBehavior     short                           0                             Bound,Maybedefault            10008  
Name                   string                          Campo combinado 1             Bound                           1  
NativeWidgetLook       boolean                         False                         Bound,Transient                18  
ObjIDinMSO             unsigned short                  65535                         Bound                         261  
Parent                 .uno.XInterface                 -INTERFACE-         Pseud                                        
Printable              boolean                         True                          Bound,Maybedefault            117  
PropertySetInfo        .beans.XPropertySetInfo         -INTERFACE-         Pseud     Read_Only                          
PropertyToDefault      string                                              Ignored                                      
PropertyValues         [].beans.PropertyValue          -Sequence-                                                       
ReadOnly               boolean                         False                         Bound,Maybedefault             11  
ReferenceDevice        .awt.XDevice                    -void-                        Bound,Transient,Maybedefault  10009  
ServiceName            string                          stardiv.one.for...  Pseud     Read_Only                          
StringItemList         []string                        -SEQUENCE-                    Bound                          49  
SupportedServiceNames  []string                        -Sequence-          Pseud     Read_Only                          
TabIndex               short                           0                             Bound                           2  
Tabstop                boolean                         -void-                        Maybevoid,Bound,Maybedefault   68  
Tag                    string                          ""                            Bound                         120  
Text                   string                          ""                            Bound,Maybedefault             48  
TextColor              long                            -void-                        Maybevoid,Bound,Maybedefault   60  
TextLineColor          long                            -void-                        Maybevoid,Bound,Maybedefault  209  
Types                  []type                          -Sequence-          Pseud     Read_Only                          
Validator              .form.validation.XValidator     -void-              Pseud                                        
ValueBinding           .form.binding.XValueBinding     -void-              Pseud                                        
WritingMode            short                           4                             Bound,Maybedefault             20  

[jrs69]

Thanks muchly FJCC - all good.
Cheers and thks.

[jrs69]

I jumped the gun with "all good", I can't find the control:
width
height
font size
text alignment

Cheers, jrs69

[Zizi64]

I jumped the gun with "all good", I can't find the control:
width
height
font size
text alignment

Please upload your ODF type sample file here together with tyour embedded macro code.

[Zizi64]

I am using the XrayTool. There are two control elements on the Sheet1 in my sample file placed directly onto the sheet.

There are more "levels" of the properties. The Size property assigned to the Shape element. And other properties assigned to the Control property of the Shape (what is a sub-object of the object).

Inspect.ods

(10.12 KiB) Downloaded 116 times

(You need install the XrayTool if you want to try my sample file.)

Click on the image for a sharper view.

[jrs69]

Thks, I'll install Xray(I've been using MRI) and run the code.

[Villeroy]

Assign some event of the control you want to inspect to [My Macros]>MriLib.Module1.Mri
Then trigger that event and MRI shows the event struct from the calling object. Every event struct has an element "Source" which is the calling object itself. Form controls have a method getModel (pseudo-property "Model"). The model is what you see in the form control design mode. It has all the font, color, bling etc.

[jrs69]

I'm afraid I've got a bit lost - I'm new to this - given the code below(working), could somebody pls post a code snippet to access the Width property of a Text Box etc.
Sorry about the variable names, just testing.

Code: Select all

Sub cmdButton()
  DrawPages = ThisComponent.getDrawPages()
  page = DrawPages.getByIndex(0)
  
  On Error Resume Next ' Control properties vary
  for i = 0 to page.Count-1
  	ctl =  page.getByIndex(i).Control
    	nme = ctl.Name
  	cid = ctl.ClassID  	
  	text = ctl.Text
  	text = ctl.Label
  	bc = ctl.BackGroundColor
  	fc = ctl.TextColor
       bs = ctl.Border 
       f  = ctl.FontName    
  next i    	
End Sub

[Villeroy]

You have MRI installed.
Open the attachment. It does not contain any macro code.
Push the button (the big one).
Double-click "Source": all the button's properties and methods.
Double-click property "Model" or method "getModel": all the design properties
Double-click property "Parent" or method "getParent": all properties of the button's form. This one is bound to the "biblio" table of the "Bibliography" database.
From there you can browse the whole hierarchy of forms and controls.

[Zizi64]

Inspect_2.ods

(11.24 KiB) Downloaded 107 times

Code: Select all

Sub cmdButton()
	DrawPages = ThisComponent.getDrawPages()
	page = DrawPages.getByIndex(0)
 
	On Error Resume Next ' Control properties vary
	for i = 0 to page.Count-1
		oForm = page.getByIndex(i)
'Xray oForm 'This object has a property named Size. The Size object has two properties:Width and Height
		oSize = oForm.Size
		     
     
		oCtrl = oForm.Control
'Xray oCtrl
		sName = oCtrl.Name
		Print sName & " Width = " & oSize.Width
		Print sName & " Height= " & oSize.Height
		
		cid = oCtrl.ClassID     
		text = oCtrl.Text
		text = oCtrl.Label
		bc = oCtrl.BackGroundColor
		fc = oCtrl.TextColor
		bs = oCtrl.Border
		f  = oCtrl.FontName   
	next i       
End sub

[jrs69]

Now working - thanks to all.