[Solved] Total Noob Having Trouble with Basic Syntax

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
static_one
Posts: 2
Joined: Tue Jan 10, 2017 11:56 pm

[Solved] Total Noob Having Trouble with Basic Syntax

Post by static_one »

I'm trying to get some code to work. First time programming in OOO basic. I think I may have mish mashed from different segments of tutorials, a recorder macro, and code snippets on here a bit too much. It's hanging up on:
Case com.sun.star.table.CellContentType.EMPTY

Which I got from:
https://wiki.openoffice.org/wiki/Docume ... and_Ranges

I've tried a bunch of different stuff but now think my approach may just be fundamentally inefficient. The specific error is:
BASIC runtime error.
Property or method not found: getCellByPosition.

I am trying to define the edges of a box of characters surrounded by empty cells. Then I want to record the xy coords of the characters that contain a string value in a separate section of the spread sheet and ignore the ones that contain an integer. The box is made of X's and 0's with 0's bordering the outside edges of the box.

Here is the full code:

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub


sub MRecordTest1
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")

dim xmaxx as INTEGER
dim xinit as INTEGER
dim x as INTEGER
dim y as INTEGER
dim ymaxx as INTEGER
dim yinit as INTEGER
dim Cell as Object

xinit = 3
yinit = 8

y=yinit
xmaxx=100
ymaxx=100

For x = xinit to xmaxx
Cell = document.getCellByPosition(x,y)
Select Case Cell.Type
Case com.sun.star.table.CellContentType.EMPTY
	xmaxx = x-1
	x = xmaxx
End Select
Next x

For y = yinit to ymaxx
Cell = document.getCellByPosition(x,y)
Select Case Cell.Type
Case com.sun.star.table.CellContentType.EMPTY
	ymaxx = y-1
	y = ymaxx
End Select
Next y

y=yinit
dim xr as INTEGER
dim yr as INTEGER
xr=28
yr=35

for y = yinit to ymaxx
For x = xinit to xmaxx
rem //Select Case Cell.Type
rem //Case com.sun.star.table.CellContentType.VALUE
rem //End Select
Select Case Cell.Type
Case com.sun.star.table.CellContentType.TEXT
 xCell = xRange.getCellByPosition(xr, yr)
 xCell.setValue(x)
 xCell = xRange.getCellByPosition(xr+1, yr)
 xCell.setValue(y) 
 yr=yr+1
End Select
Next x
Next y

end sub
Thank you for looking!
Last edited by static_one on Wed Jan 11, 2017 2:49 am, edited 1 time in total.
OOO 4.1.2 win 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total Noob Having Trouble with Basic Syntax

Post by Villeroy »

You can write macros in real programming languages instead of Basic which is somewhat ... underdeveloped.

You must not use any dispatcher macros except for rare cases where the API is missing something.

The "document" variable of a recorded dispatcher macro is misleadingly named. "document" does not refer to any document. ThisComponent does. getCellByPosition does not apply to documents anyway.

UNO is a very abstract API which is not bound to any particular programming language. UNO is for professional programmers only. It may take some weeks until you become familiar with it.
No, learning by doing does not help you to cope with it ...
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
static_one
Posts: 2
Joined: Tue Jan 10, 2017 11:56 pm

Re: Total Noob Having Trouble with Basic Syntax

Post by static_one »

Thank you Villeroy - that was helpful. Here's the completed working code.

Code: Select all

document   = ThisComponent.getCurrentController.activesheet
dim xmaxx as INTEGER
dim xinit as INTEGER
dim x as INTEGER
dim y as INTEGER
dim ymaxx as INTEGER
dim yinit as INTEGER
dim Cell as Object

xinit = 2
yinit = 7

y=yinit
xmaxx=100
ymaxx=100

For x = xinit to xmaxx
Cell = document.getCellByPosition(x,y)
Select Case Cell.Type
Case com.sun.star.table.CellContentType.EMPTY
	xmaxx = x
	x = xmaxx
End Select
Next x

x = xinit

For y = yinit to ymaxx
Cell = document.getCellByPosition(x,y)
Select Case Cell.Type
Case com.sun.star.table.CellContentType.EMPTY
	ymaxx = y
	y = ymaxx
End Select
Next y

y=yinit
dim xr as INTEGER
dim yr as INTEGER
xr=27
yr=34

dim xCell as Object

for y = yinit to ymaxx
For x = xinit to xmaxx
Cell = document.getCellByPosition(x,y)
Select Case Cell.Type
Case com.sun.star.table.CellContentType.TEXT
 xCell = document.getCellByPosition(xr,yr)
 xCell.setValue(x-xinit+1)
 xCell = document.getCellByPosition(xr+1,yr)
 xCell.setValue(y-yinit+1) 
 yr=yr+1
End Select
Next x
Next y

end sub
OOO 4.1.2 win 10
Post Reply