Ask Your Question
0

Use cell values to create a range in a macro

asked 2014-08-16 18:07:14 +0200

Lanser gravatar image

I am trying to use cell values as the range to set a print area in a macro but without sucess.

e.g. A1.value = 2 A2.value = 16 starting at C1 would make a print area of $C1$:$E$18

Got as far as below biut don't know how to get the contents of A1 and A2 into the args2 value

sub test_print
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 args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$Printer.$C$1:$E$18"

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


end sub
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-08-10 20:13:31 +0200

Arkana gravatar image

Hello, I'm new here in this forum and also completely unexperienced with VBA-coding. Anyway, I try to define a printing-area in calc by makro.

I tried Miktokis code since it give me the possibility to define the range by index and not by cell-name...

When I run the makro it will highlight this line: Dim range As New com.sun.star.table.CellRangeAddress

... and give me this error-message: Type: com.sun.star.lang.IllegalArgumentException Message: cannot coerce argument type during corereflection call!.

Could someone help me and make this code run?

Thank you an advance! Best regards, Arkana

edit flag offensive delete link more
0

answered 2015-04-03 01:35:15 +0200

miktoki gravatar image

I imagine it could be done by doing something along the line of:


Sub test_print
    Dim rows(1) As Long
    Dim cols(1) As Long
    Dim sheet
    Dim cell
    Dim range As New com.sun.star.table.CellRangeAddress

    'get sheet1
    sheet=ThisComponent.Sheets.getByIndex(0)

    'get values in cells A1, A2, B1, B2 by column row sheet
    row(0)=sheet.getCellByPosition(0,0).getValue()
    row(1)=sheet.getCellByPosition(0,1).getValue()
    col(0)=sheet.getCellByPosition(1,0).getValue()
    col(1)=sheet.getCellByPosition(1,1).getValue()

    'use values to define print range
    range.sheet = 0
    range.StartRow = row(0)     : range.EndRow = row(1)  
    range.StartColumn = col(0)  : range.EndColumn = col(1)  

    ThisComponent.CurrentController.getActiveSheet().setPrintAreas(range())
    ThisComponent.Print(Array())
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2014-08-16 18:07:14 +0200

Seen: 1,996 times

Last updated: Aug 10 '16