[Risolto] esportare in pdf e stampare successivamente

[arcgabriel]

pensavo di aver risolto con il problema di esportazione in pdf e subito mi si presenta un nuovo dilemma.
Quello che voglio ottenere è una macro che faccia questo:

1 selezionare un range di celle su un determinato foglio
2 esportare in pdf la selezione
3 stampare la stessa selezione in pìù copie

Ho creato inizialmente 2 macro;
una per esportare in pdf, salvare in una cartella variabile lo stesso e nominarlo in base al valore.stringa di una determinata cella (funziona)
la seconda mi stampa la selzione in più copie (funziona)
Il problema si presenta se unisco in un'unica macro o chiamo con il comando "call" le macro da una all'altra.
Non so come fargli stampare la selezione di celle che desidero. Va a stampare ciò che trova sul primo foglio n volte mentre ciò che mi serve si trova sul foglio(7). Praticamente dopo la conversione in pdf perde i parametri per il riconoscimento del range di celle da stampare.

Non so se mi sono spiegato se serve allego il codice creato.

Grazie

[FncZ4pp4]

Prova a dichiarare le variabili sul modulo e non sulle SubRoutine.

[arcgabriel]

Ti ringrazio per la veloce risposta, faccio presente che non sono ferrato in materia, potresti farmi un semplice esempio.

Grazie

[FncZ4pp4]

Immagino che tu abbia programmato in questo modo:

Codice: Seleziona tutto

Sub Macro1
dim document   as object
Dim Var1,Var2,............ as double
.......
.......
document   = ThisComponent.CurrentController.Frame
.......
Call Macro2
.......
End Sub

Sub Macro2
dim document   as object
Dim Var3,Var4,............ as integer
.......
.......
document   = ThisComponent.CurrentController.Frame
.......
.......
End Sub

Prova a rendere le Variabili che ti servono come pubbliche, dichiarale direttamente allinizio fuori dall'istruzione Sub
Esempio:

Codice: Seleziona tutto

Dim document   as object
Dim Var1,Var2,............ as double
Dim Var3,Var4,............ as integer
Sub Macro1
.......
.......
document   = ThisComponent.CurrentController.Frame
.......
Call Macro2
.......
End Sub

Sub Macro2
.......
.......
.......
.......
End Sub

[arcgabriel]

Ti posto il mio codice, mi dici come correggerlo?

Codice: Seleziona tutto

rem define variables
dim document   as object
dim dispatcher as object


sub PDF1

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 = "ToPoint"
args1(0).Value = "PAG1"

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

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

rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "URL"
args3(0).Value ="file:///D:/ARCHIVIO/FATTURE ATTIVE/2011/"& ThisComponent.Sheets(7).getCellRangeByName("N2").String & "/"& ThisComponent.Sheets(7).getCellRangeByName("M2").String & ".pdf"
args3(1).Name = "FilterName"
args3(1).Value = "calc_pdf_Export"
args3(2).Name = "FilterData"
args3(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EmbedStandardFonts",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))

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


Call "FT1"

end sub




sub FT1

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------

dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Copies"
args2(0).Value = 2
args2(1).Name = "RangeText"
args2(1).Value = "1"
args2(2).Name = "Collate"
args2(2).Value = true

dispatcher.executeDispatch(document, ".uno:Print", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeletePrintArea", "", 0, Array())
.
.
.
end sub

[FncZ4pp4]

è stato generato dal registratore di macro e le variabili in questo caso sono di uso immediato e non di calcolo e i dati directory e file presi da delle caselle dedicate, pensavo tutt'altra cosa. In questo caso non servono variabili pubbliche, prova queste macro modificate, le devi rimpiazzare a quelle che già hai nello stesso modulo, fallo su un file di prova per non rischiare nulla, io lo ho testato e a me funziona tutto bene.

Codice: Seleziona tutto

sub PDF1
dim document   as object
dim dispatcher as object
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 = "ToPoint"
args1(0).Value = "PAG1"

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

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

rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "URL"
args3(0).Value ="file:///D:/ARCHIVIO/FATTURE ATTIVE/2011/"& ThisComponent.Sheets(7).getCellRangeByName("N2").String & "/"& ThisComponent.Sheets(7).getCellRangeByName("M2").String & ".pdf"
args3(1).Name = "FilterName"
args3(1).Value = "calc_pdf_Export"
args3(2).Name = "FilterData"
args3(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EmbedStandardFonts",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args3())
FT1
end sub


sub FT1
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------

dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Copies"
args2(0).Value = 2
args2(1).Name = "RangeText"
args2(1).Value = "1"
args2(2).Name = "Collate"
args2(2).Value = true

dispatcher.executeDispatch(document, ".uno:Print", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeletePrintArea", "", 0, Array())
end sub

[FncZ4pp4]

Non so come fargli stampare la selezione di celle che desidero. Va a stampare ciò che trova sul primo foglio n volte mentre ciò che mi serve si trova sul foglio(7). Praticamente dopo la conversione in pdf perde i parametri per il riconoscimento del range di celle da stampare.

Dalla macro ho capito che non basta selezionare un area ma la devi anche definire come area di nome Pag1.

[arcgabriel]

L'area di stampa è nominata come pag1, ho provato a modificare il codice come hai suggerito ma non cambia nulla, crea correttamente il file pdf ma successivamente stampa i dati presi dal foglio n° 1 mentre quello che mi serve è il foglio n° 7.

[arcgabriel]

Ho risolto registrando il tutto in un'unica macro, ora funziona.

Grazie, ciao

[FncZ4pp4]

La macro stampa e converte l'area Pag1, tale area definendola nel foglio 7 avevi risolto il problema.

[arcgabriel]

avevo già definito Pag1 da tempo. Non era quello il problema. Non so dove sta l'arcano, comunque ora funge con un'unica macro, meglio.

Grazie lo stesso, ciao