Ask Your Question

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("")

rem ----------------------------------------------------------------------
dim args2(0) as new
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

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

... and give me this error-message: Type: 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

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

    'get sheet1

    'get values in cells A1, A2, B1, B2 by column row sheet

    '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)  

End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 1,996 times

Last updated: Aug 10 '16