[Solved] Define print area based on value in a cell

[embolalia]

This is primarily being used with 3.3.0, on varying XP and above versions of WIndows.

I found some snippets lying around that looked like they were supposed to do this, but I can't quite get them to work. This is what I have so far:

Code: Select all

REM  *****  BASIC  *****
sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
dim pages as integer
pages = thisComponent.Sheets(4).getCellRangeByName("F7").Value
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DefinePrintArea", "", 0, Array()) 

rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "PrintArea"
args2(0).Value = "$A$1:$X$"&mid(Str(pages*54), 2) ' I found it was inserting a space between X and the row number for some reason, so I remove it.
args2(1).Name = "PrintRepeatRow"
args2(1).Value = ""
args2(2).Name = "PrintRepeatCol"
args2(2).Value = ""

' This line is just to put the value out somwhere I can see it; it's not meant functionally
thisComponent.Sheets(0).getCellRangeByName("F15").String = "$A$1:$X$"&mid(Str(pages*54), 2)

dispatcher.executeDispatch(document, ".uno:EditPrintArea", "", 0, args2()) 

end sub

The basic idea is, someone will fill or calculate a number of pages to print, each of which is 54 lines long, and that's what will print.

In more detail, it's a "showbook" - a listing of item numbers, their retail and regular dealer prices, and a discounted price, for our yearly trade show. For eternity, we've been pasting in the data one page at a time, with each of the 400+ pages being a separate spreadsheet. If we wanted to insert more items onto a page, we would then have to manually edit the rest of the pages for that vendor (sometimes 12 or more). My idea here is to bring us down to one spreadsheet file per vendor, wherein the user pastes the data onto a separate worksheet within the file, and Calc does the work of filling that data into our template. The page would have multiple templates, one below the other, and fill them in top to bottom.

Of course, if there's a better way to do it, I'd love to hear it. As far as I can tell, using headers and footers wouldn't quite do it, since we like to have a page number both for the whole book and for the page number within the vendor (e.g. page 123 is page 3 of 4 for Widgetco).

Sadly, I'm not very familiar with OO.o macros or BASIC in general, so I appologize that I'm sort of bumbling through this.

[Villeroy]

Of course, if there's a better way to do it, I'd love to hear it.

A database.

Code: Select all

sh = ThisComponent.Sheets.getByName("Output")
REM get the print address from a named cell:
oNamed =ThisComponent.NamedRanges.getByName("PrintAddress")
 oRange = oNamed.getReferredCells()
 oCell = oRange.getCellByPosition(0,0)
 strAddr = oCell.getString()
 rg = sh.getCellRangeByName(strAddr)
 addr = rg.getRangeAddress()
 sh.setPrintAreas(Array(addr))

Put some formula with function ADDRESS in cell "PrintAddress"

[embolalia]

Hm. I keep getting an error on
oNamed = ThisComponent.Names.getByName("PrintAddress")
saying "Property or method not found: Names". I did define the PrintAddress name and point it at the cell with the address. I can't imagine this is a problem with different versions; I tried it with OO.o 3.3 on Windows and LibreOffice 3.4 on Linux, with the same error. Is there something else I need to set up for this? (Sorry if it's something obvious; I haven't really done anything with macros before this.)

EDIT: Figured out the right names, somehow:

Code: Select all

REM  *****  BASIC  *****
sub Main
	sh = ThisComponent.Sheets.getByName("Printing")
	REM get the print address from a named cell:
	oNamed =ThisComponent.NamedRanges.getByName("PrintAddress")
	oRange = oNamed.getReferredCells()
	oCell = oRange.getCellByPosition(0,0)
	strAddr = oCell.getString()
	rg = sh.getCellRangeByName(strAddr)
	addr = rg.getRangeAddress()
	sh.setPrintAreas(Array(addr))
end sub

And the formula in the cell is:
=ADDRESS(54*F7; 24;1;;"Printing")&":"&ADDRESS(1; 1;1;;"Printing")

This works exactly as I wanted it to. Thanks.