Help with macro

Creating a macro - Writing a Script - Using the API

Help with macro

Postby biggles » Wed Jul 26, 2017 5:20 pm

I hope someone can help as I am not at all experienced at this and would appreciate some assistance

What I want to do is
To test the value from Sheet (NR1) cell (T19), if that value is =>1, Then execute the recorded macro “PrtRangex”
If that is not true then, execute macro “PrtRange1x”.
If the value Sheet (NR1) cell (T19) is =>1 and the value Sheet (NR1) cell (N16) is also =>1, Then execute macro “PrtRange2”.
If the value from sheet (NR2) cell (V35) =>1, Then execute “PrtRange3x”.
My recorded macros below>

Code: Select all   Expand viewCollapse view

sub PrtRangex
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 args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:EditPrintArea", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "PrintArea"
args3(0).Value = "$A$2:$t$58"
args3(1).Name = "PrintRepeatRow"
args3(1).Value = ""
args3(2).Name = "PrintRepeatCol"
args3(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

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

rem ----------------------------------------------------------------------
dim args6(2) as new com.sun.star.beans.PropertyValue
args6(0).Name = "PrintArea"
args6(0).Value = ""
args6(1).Name = "PrintRepeatRow"
args6(1).Value = ""
args6(2).Name = "PrintRepeatCol"
args6(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

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

rem ----------------------------------------------------------------------
dim args9(2) as new com.sun.star.beans.PropertyValue
args9(0).Name = "PrintArea"
args9(0).Value = ""
args9(1).Name = "PrintRepeatRow"
args9(1).Value = ""
args9(2).Name = "PrintRepeatCol"
args9(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Nr"
args10(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args10())

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

rem ----------------------------------------------------------------------
dim args12(2) as new com.sun.star.beans.PropertyValue
args12(0).Name = "PrintArea"
args12(0).Value = ""
args12(1).Name = "PrintRepeatRow"
args12(1).Value = ""
args12(2).Name = "PrintRepeatCol"
args12(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args12())

rem ----------------------------------------------------------------------

end sub



rem next print 1--------------------------------------
sub PrtRange1x
rem page 1,2
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 args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:EditPrintArea", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "PrintArea"
args3(0).Value = "$A$2:$t$58"
args3(1).Name = "PrintRepeatRow"
args3(1).Value = ""
args3(2).Name = "PrintRepeatCol"
args3(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

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

rem ----------------------------------------------------------------------
dim args6(2) as new com.sun.star.beans.PropertyValue
args6(0).Name = "PrintArea"
args6(0).Value = "$A$1:$V$54"
args6(1).Name = "PrintRepeatRow"
args6(1).Value = ""
args6(2).Name = "PrintRepeatCol"
args6(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

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

rem ----------------------------------------------------------------------
dim args9(2) as new com.sun.star.beans.PropertyValue
args9(0).Name = "PrintArea"
args9(0).Value = ""
args9(1).Name = "PrintRepeatRow"
args9(1).Value = ""
args9(2).Name = "PrintRepeatCol"
args9(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Nr"
args10(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args10())

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

rem ----------------------------------------------------------------------
dim args12(2) as new com.sun.star.beans.PropertyValue
args12(0).Name = "PrintArea"
args12(0).Value = ""
args12(1).Name = "PrintRepeatRow"
args12(1).Value = ""
args12(2).Name = "PrintRepeatCol"
args12(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args12())
rem ----------------------------------------------------------------------

end sub


rem next 2-----------------------------------------------------------
sub PrtRange2x
rem page 1,2,3-----------
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 args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:EditPrintArea", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "PrintArea"
args3(0).Value = "$A$2:$t$58"
args3(1).Name = "PrintRepeatRow"
args3(1).Value = ""
args3(2).Name = "PrintRepeatCol"
args3(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

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

rem ----------------------------------------------------------------------
dim args6(2) as new com.sun.star.beans.PropertyValue
args6(0).Name = "PrintArea"
args6(0).Value = "$A$1:$V$54"
args6(1).Name = "PrintRepeatRow"
args6(1).Value = ""
args6(2).Name = "PrintRepeatCol"
args6(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

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

rem ----------------------------------------------------------------------
dim args9(2) as new com.sun.star.beans.PropertyValue
args9(0).Name = "PrintArea"
args9(0).Value = "$A$2:$W$61"
args9(1).Name = "PrintRepeatRow"
args9(1).Value = ""
args9(2).Name = "PrintRepeatCol"
args9(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Nr"
args10(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args10())

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

rem ----------------------------------------------------------------------
dim args12(2) as new com.sun.star.beans.PropertyValue
args12(0).Name = "PrintArea"
args12(0).Value = ""
args12(1).Name = "PrintRepeatRow"
args12(1).Value = ""
args12(2).Name = "PrintRepeatCol"
args12(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "Nr"
args13(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args13())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "ToPoint"
args14(0).Value = "$V$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args14())


end sub

rem next 3----------------------------------------
sub PrtRange3x
rem page 1,2,4
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 args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:EditPrintArea", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "PrintArea"
args3(0).Value = "$A$2:$t$58"
args3(1).Name = "PrintRepeatRow"
args3(1).Value = ""
args3(2).Name = "PrintRepeatCol"
args3(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

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

rem ----------------------------------------------------------------------
dim args6(2) as new com.sun.star.beans.PropertyValue
args6(0).Name = "PrintArea"
args6(0).Value = "$A$1:$V$54"
args6(1).Name = "PrintRepeatRow"
args6(1).Value = ""
args6(2).Name = "PrintRepeatCol"
args6(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

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

rem ----------------------------------------------------------------------
dim args9(2) as new com.sun.star.beans.PropertyValue
args9(0).Name = "PrintArea"
args9(0).Value = ""
args9(1).Name = "PrintRepeatRow"
args9(1).Value = ""
args9(2).Name = "PrintRepeatCol"
args9(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Nr"
args10(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args10())

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

rem ----------------------------------------------------------------------
dim args12(2) as new com.sun.star.beans.PropertyValue
args12(0).Name = "PrintArea"
args12(0).Value = "$A$3:$v$52"
args12(1).Name = "PrintRepeatRow"
args12(1).Value = ""
args12(2).Name = "PrintRepeatCol"
args12(2).Value = ""

dispatcher.executeDispatch(document, ".uno:ChangePrintArea", "", 0, args12())


end sub
Last edited by RoryOF on Wed Jul 26, 2017 8:29 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
0pen office 4.1.3 on windows 10
biggles
 
Posts: 4
Joined: Fri Jul 14, 2017 12:46 pm

Re: Help with macro

Postby Lupp » Wed Jul 26, 2017 8:17 pm

You won't get anywhere with this approach.

What you want is obviously to get one or more ranges of one or more sheets printed under specific conditions.
Leave it to those trying to help you to advise you concerning the appropriate means to accomplish that. Recorded "macros" are most likely not among them.

It's your turn on the other hand to tell exactly what shall be done under what conditions. Use well proven ways to do so. If few words do don't use many. Where some more words might help don't hesitate. The following is not any program code. It's an attempt to give a structured description:

Code: Select all   Expand viewCollapse view
Case Sheet1.T19 >= 1
    Print Range Sheet2.$A$2:$t$58  (may be referred to as Range_1Yes) [The only RangAddress extractable from the so called macros]
    SubCase Sheet1.N16 >= 1
        In addition Print Range Sheet5.$Z$17:$AG$101  (may be referred to as Range_11)
Else (now Sheet1.T19 < 1)
    Print Range ????? (referred to as Range_1No)
End of first alternative

Case Sheet2.V35 >= 1
    Print Range ?????  (referred to as Range_2Yes)
End of second alternative.


If this is (basically) what you want then fill in the missing information and make the needed corrcetions.
Otherwise make clear what you actually want to achieve.

The information you want to base the decisions on are sprinkled. It is not a sensible task for a "macro" to collect such information. Dedicate a few cells in the sheet from where you intend to start the printing and draw the needed values to them by very simple spreadsheet formulae. It is also bad style to include needed ranges with a macro as text constants. This makes things extremely unflexible. Dedicate a few more cells nearby the already mentioned ones and fill in the fully qualified ranges (like in the examples above: SheetName.CellRangeAddress).

See you later?
On Windows 10: LibreOffice 5.4.0 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1358
Joined: Sat May 31, 2014 7:05 pm

Re: Help with macro

Postby biggles » Thu Jul 27, 2017 1:04 pm

Hi Lupp
I very much appreciate your help, I hope this explains what I want to do better.
I have a workbook with 4 sheets, What I want to do is to convert to pdf and save (condition dependant) only those sheets with specific ranges as specified in the options below. (Macro will be manually started by button)

4 Conditions:
1). If Sheet1 cell T19 >=1 Then b). Convert Pages 1&2 Only to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”
2). If Sheet1 cell T19<1 Then a). Convert Page 1 Only to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”

3). If Sheet1 cell T19>=1 and N16>=1 Then c). Convert Pages 1,2&3 Onlys to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”
4). If Sheet2 cell T19>=1 and V35 >=1 Then d). Convert pages 1,2&4 Only to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”


a). Convert Page 1 Only to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”
Sheet1. Print Range. "$A$2:$t$58"
Sheet2. Print Range. ""
Sheet3. Print Range. ""
Sheet4. Print Range. ""

b). Convert Pages 1&2 Only to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”
Sheet1. Print Range. "$A$2:$t$58"
Sheet2. Print Range. "$A$1:$V$54"
Sheet3. Print Range. ""
Sheet4. Print Range. ""

c). Convert Pages 1,2&3 Onlys to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”
Sheet1. Print Range. "$A$2:$t$58"
Sheet2. Print Range. "$A$1:$V$54"
Sheet3. Print Range. "$A$2:$W$61"
Sheet4. Print Range. ""

d). Convert pages 1,2&4 Only to PDF and save in file “desktop/pdf_store/(name of Sheet1 cell C4)”
Sheet1. Print Range. "$A$2:$t$58"
Sheet2. Print Range. "$A$1:$V$54"
Sheet3. Print Range. ""
Sheet4. Print Range. "$A$3:$v$52"

Thanks
0pen office 4.1.3 on windows 10
biggles
 
Posts: 4
Joined: Fri Jul 14, 2017 12:46 pm

Re: Help with macro

Postby Villeroy » Thu Jul 27, 2017 3:36 pm

Do you know scenario ranges? They exchange the data and/or formulas leaving alone all the other stuff, formatting, layout etc.

download/file.php?id=2755
download/file.php?id=3004
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24077
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: hubert lambert and 7 guests