[Calc] Insert a button to print sheets

[pdjm]

Hi
I've been trying to get a macro that can do this:
I have a calc document with the following sheets: data,sheet1,sheet2,sheet3
Data is where i insert information, sheet1-3 are sheets generated from this information
I want to insert a button in data that when pressed will print sheet1 to 3 and then return to data sheet. I tryed recording a macro but i don't know why it just won't work. Can anyone please help me with this?
Thanks
Pedro

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[RoryOF]

If you posted the code from the macro you recorded it would be helpful. Use Post Reply and then the Code button to keep your macro code formatted.

[pdjm]

Code: Select all

REM  *****  BASIC  *****


sub code
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 = 2

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

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

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

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

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

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

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

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

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

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


end sub


sub macro_code
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 = 2

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

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

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

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

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

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

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

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

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

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


end sub

This is the code that openoffice generates.

Code: Select all

Sub Print()
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Data").Select
End Sub

And this is the code excel generates.

The excel code does not work in open office but is exactly what i want. The openoffice code hang when printing the first page

Thanks for your help

[Zizi64]

You can try this:

Print_4_Worksheets.ods

(10.14 KiB) Downloaded 1426 times

[pdjm]

Thanks for your help. However this macro prints data sheet and sheet3 x3times. What i would like it to do is to print only sheet1,2 and 3

[Zizi64]

However this macro prints data sheet and sheet3 x3times. What i would like it to do is to print only sheet1,2 and 3

Really. I was not make test.
I do not know, what caused that problem, but when I put a timing command into code: then it is works fine for me. (wait 500)

Code: Select all

for i=1 to 4

    args1(0).Value = i

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

    rem ----------------------------------------------------------------------
        dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
           dispatcher.executeDispatch(document, ".uno:Print", "", 0, args2())
   
next i

Print_4_Worksheets4.pdf

(9.54 KiB) Downloaded 712 times

[pdjm]

It works. Thanks
I changed
for i=1 to 4
to
for i=2 to 4
because i don't want the first data page to be printed

Is it possible to change the macro to end displaying the data sheet, making open office after printing to return to the data worksheet? Currently it stops after printing in the last worksheet?
A big thanks

[Zizi64]

Yes, that is possible. Here is the code: after "for" cycle you need add a "JumpToTable" command.

Code: Select all

for i=2 to 4

    args1(0).Value = i
    dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
    wait 500
    rem ----------------------------------------------------------------------
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    dispatcher.executeDispatch(document, ".uno:Print", "", 0, args2())
   
next i

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

[pdjm]

Big thanks for your help. Problem solved

[Ajith.kumar]

Hi,
Actually i used this macro but i am getting the error as shown in the file attached . plz help me just to print a single page which should convert to PDF format or provide me with the macro for the printing of the calc or excel page to pdf format and should be saved

kind regards
ajith Kumar