Hello!
I would like to ask for help in constructing a macro that would define print area based on value in a cell “U2”.
The formula in the cell U2 gives different values depending on the data entered in column A.
The formula in the cell U2 gives a cell ranges e.g. :
$A$1:$T$41
or
$A$1:$T$41;$A$42:$T$61
or
$A$1:$T$41;$A$42:$T$61;$A$62:$T$81
or
$A$1:$T$41;$A$42:$T$61;$A$62:$T$81;$A$82:$T$101
The file has many sheets in which the U2 cell is responsible for the same as described above.
Do you have any ideas for macro like that? Thank you in advance for help.
Below is what I needed. Macro that would define print area based on value in a cell “V2” instead of “U2” as I wrote above. The macro also includes PrintRepeatRow and PrintRepeatCol.
Function SetPrintAreaByCell_V2
Dim my_sheet as object
Dim my_cell as object
Dim Doc as object
Dim oSheet As Object
oSheet = ThisComponent.CurrentController.ActiveSheet
my_cell = oSheet.getCellRangeByName(“V2”)
If (my_cell.getError <> 0) Then
get_cell_V2 = my_cell.String & " (Error " & my_cell.getError & “)”
Else
Select Case my_cell.FormulaResultType
Case com.sun.star.table.CellContentType.VALUE
get_cell_V2 = my_cell.Value
Case Else
get_cell_V2 = my_cell.String
'MsgBox(get_cell_V2 ,0 ,“Komórka V2 zawiera:”)
'get_cell_V2 - wartość komórki V2 do ustalenia zakresów wydruku
End Select
End If
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
rem ----------------------------------------------------------------------
dim args5(2) as new com.sun.star.beans.PropertyValue
args5(0).Name = “PrintArea”
args5(0).Value = get_cell_V2
args5(1).Name = “PrintRepeatRow”
args5(1).Value = “$A$1:$A$21”
args5(2).Name = “PrintRepeatCol”
args5(2).Value = “$A$1:$T$1”
dispatcher.executeDispatch(document, “.uno:ChangePrintArea”, “”, 0, args5())
End Function
oBasket = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
oSheets = ThisComponent.getSheets()
sh = oSheets.getByIndex(0)
s = sh.getCellRangeByName("U2").getString()
aSplit = split(s, ";")
for each s in aSplit()
addr = sh.getCellRangeByName(s).getRangeAddress()
oBasket.addRangeAddress(addr, True)
next
sh.setPrintAreas(oBasket.getRangeAddresses())
2 Likes
Villeroy thanks for your effort. I have posted what I wanted above.