[Solved] Calc Cell Text into Dialog TextBox

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

[Solved] Calc Cell Text into Dialog TextBox

Post by Tobin »

Howdy,

I'm having difficulty figuring out how to get text from a Calc Cell into a TextBox on a Dialog. The attached zip file contains the Calc file I'm working with. The Sub I'm working on is Sub Accept. As you will see I have been trying many different approaches, so it's kind of full of old remarked-out attempts to figure this out. I can't seem to connect to a Cell.

I've done a lot of reading and even found some old examples but I'm still having trouble.
Can anyone show me how to do this?

Any suggestions/comments would be appreciated.
Attachments
GetCellError.zip
(34.5 KiB) Downloaded 753 times
Last edited by Tobin on Sun Feb 15, 2009 4:04 am, edited 1 time in total.
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calc Cell Text into Dialog TextBox

Post by FJCC »

Here is some code that gets the text of cell D4 into the text box. One problem I fixed is that the method getCellByPosition takes two numbers as arguments: the column index and the row index. These both start counting at zero, so cell A1 is (0,0) and the cell D4 is (3,3). A second problem was that you tried to assign the Cell object as the value of the text box. The text box has a property Text (not value) which is of the type String. The appropriate Cell property to assign to the Text of the text box is the Cell.String which is also of type String.

Code: Select all

Dim Sheet as Object
Dim Cell as Object
Dim tbxModel as Object


tbxModel = oDialog.Model.getByName("tbx_Accept")
Sheet = ThisComponent.getcurrentcontroller.activesheet

Cell = Sheet.getCellByPosition(3,3)

tbxModel.Text = Cell.String
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.
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: [Solved] Calc Cell Text into Dialog TextBox

Post by Tobin »

Excellent!

Very helpful reply. FJCC, you are obviously knowledgeable concerning OOo Basic. I appreciate your explanation of the code.

I'm getting a feeling the net is loaded with old, incorrect or just plan misinformation when it comes to OOo Basic. If you don't mind - can I ask a couple questions?
Do you think the bad information is because of some recent change in OOo Basic or what?
I've ordered Andrew Pitonyak's book - will it contain and explain the latest OOo Basic?

Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Calc Cell Text into Dialog TextBox

Post by FJCC »

I have only been writing macros for a few months, so I can't be sure how much OOo Basic has changed over time, but I doubt it is much. Also, you shouldn't confuse OOo Basic with the API (Application Programming Interface) that is used for most of the actual work and causes most of my difficulties. The meaning of things like Sheet.getCellByPosition(3,3) is defined by the API. The API Reference contains comprehensive descriptions, but is huge and I find it hard to use a lot of the time. That is why I rely on XRay or MRI, which present the information relevant to the objects I'm working with in a organized fashion. I would encourage you to work on understanding the structure of the API, things like properties, methods and structures. I'm far from an expert, but as I gained an understanding of the API I found more and more of the references and examples to be useful.

I don't have Andrew Pitonyak's book, but I have found his free macro document to be valuable. I expect his book is well worth the price.
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.
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: [Solved] Calc Cell Text into Dialog TextBox

Post by Tobin »

Howdy FJCC,

Thanks for your reply. I would have never imagined <you shouldn't confuse OOo Basic with the API (Application Programming Interface) >. I find this surprising or a little disturbing ;) . I suppose it's been adding to my confusion as well. I've noticed the recorded macros are quite different from many examples I've seen.

So - If I record a macro I'm getting the OOo Basic, but much of the written code and examples are the API?

Thanks for your help
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Calc Cell Text into Dialog TextBox

Post by FJCC »

Warning - at best I barely know what I'm talking about here!
If you go to the OOo Help (Press F1) and select OOo Basic from the drop down list at the top left, you can get a good idea of what constitutes OOo Basic. The Run-Time Functions section is probably most useful in this respect. (The sections on Dialogs starts mixing in API calls without being very clear about it.) You will see that Basic itself doesn't deal with Cells, Sheets, Documents etc. Basic allows you to control program flow with If..Then or For..Next, do mathematical operations on variables, manipulate arrays etc. Calls to the API are what allow you to manipulate the contents of an OOo document such as Cells, Tables, Forms, Charts etc. Other programing languages can also use the API: Python, JavaScript and BeanShell are listed along with OOo Basic when you select Tools -> Macros -> Organize Macros. Most of the examples in the Developer's Guide are in Java, which is very different from Basic.

The recorded macros use a technique called dispatcher calls and give you very little understanding of the API. They are not pure Basic by any means. It seems that some people use them routinely in their own macros. I've never found adequate documentation about dispatcher calls and I've chosen to avoid them.
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Calc Cell Text into Dialog TextBox

Post by Villeroy »

You don't need to use StarBasic to talk to this office like you don't need VBA to talk to MS Office.
StarBasic is a dialect of Basic that has been added to the predecessor of OOo when MS introduced Visual Basic for Applications. That was MSOffice '95 and Star Office 5.x. Unlike VBA StarBasic did not evolve since then.
StarBasic has some helper functions to connect more easily to the office, it comes with it's own editor and it is more easy to embed this type of code into documents. It's about ease-of-use for the developer. But all in all it's not a serious programming language. It is this office suite's preferred loudspeaker for API-calls.

The macro recorder records user-interactions in the current document's frame: Click here, select that, jump to there, click this ...
The frame is an abstract thing with 2 main-windows, all the bars and buttons, a model (the document) and a controller.

When the macro-recorder writes: document = ThisComponent.CurrentController.Frame
this statement is confusing since the symbol should be named as "frame" rather then "document".
ThisComponent is the document (aka model), that model has one or more views (aka controllers) to select items within the model and scroll through it. CurrentController is the one in the currently active frame and since every frame has zero or one controllers and every controller must belong to one frame, you can get the frame from the current controller of this component.
A "normal" document-frame has an outer ContainerWindow, inner CmponentWindow(showing the document content), a controller (select&scroll) and a model (aka component or document). The frame provides the whole dispatch-framework which is used to record the user's interactions in this frame.
And yes, all this is part of the API and this recorded Basic code ...

Code: Select all

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$D$23"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())


end sub
... could be written in a true programming language as well, Python for instance ...

Code: Select all

import  uno
def Main():
# we don't have predefined ThisComponent nor callable CreateUnoService()
    ctx = uno.getComponentContext()
    smgr = ctx.ServiceManager
# we don't have CreateUnoObject
    dispatcher = smgr.createInstance("com.sun.star.frame.DispatchHelper")
    dtp = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
# get document from desktop
    ThisComponent = dtp.getCurrentComponent()

# get frame from controller and controller from document
    frame   = ThisComponent.CurrentController.Frame

# ---arrays in Python require different syntax-------------------------------------------
    arg = uno.createUnoStruct("com.sun.star.beans.PropertyValue")
    arg.Name = "ToPoint"
    arg.Value = "$A$1:$D$23"
    dispatcher.executeDispatch(frame, ".uno:GoToCell", "", 0, (arg,))
By the way: The "Desktop" (predefined StarDesktop in Basic) can be seen as the single collection of frames.
Go to oooforum.org and search for all tems "model, controller frame" by user "DannyB". It's worth reading.
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
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: [Solved] Calc Cell Text into Dialog TextBox

Post by Tobin »

WOW!

Thank you FJCC and Villeroy for your responses. I'm just a beginner at programming. I have a little experience with VB.net. I've mostly used that with the SolidWorks 3D modeling API. I started looking into OOo Basic because of a challenge from a co-worker :D .

Your posts are very helpful
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Post Reply