[Solved] Calc-Export changes on the same sheet to unique pdf

[patel]

In my sheet Report i can change the graph by changing the C5 cell content, I wrote a macro for export changes to single pdf files

Code: Select all   Expand viewCollapse view

Sub PDF1()
Dim Entry(1 To 4) As String
Entry(1) = "A"
Entry(2) = "B"
Entry(3) = "C"
Entry(4) = "D"
Doc = ThisComponent
oSheet =  Doc.CurrentController.getActiveSheet()
oRng = oSheet.getCellRangeByName("A1:I17")
Dim mFilterData(0) As New com.sun.star.beans.PropertyValue
mFilterData(0).Name = "Selection"
Dim mStoreOpts(2) As New com.sun.star.beans.PropertyValue
mStoreOpts(0).Name = "Overwrite"
mStoreOpts(0).Value = True
mStoreOpts(1).Name = "FilterName"
mStoreOpts(1).Value = "calc_pdf_Export"
mStoreOpts(2).Name = "FilterData"
path = "file:///F:/Download/Totale" & Replace(Date,"/","-") & ".pdf"
For Count = 1 To 4
  oSheet.getCellRangeByName("C5").String = Entry(Count)
  mFilterData(0).Value = oRng
  mStoreOpts(2).Value = mFilterData()
  sUrl = "file:///F:/Download/"&  Entry(Count) & " " & Replace(Date,"/","-") & ".pdf" 'Cel.getString()
  Doc.storeToURL(sURL, mStoreOpts())
next
end sub

My goal is an unique PDF with all changes.
File attached

[Zizi64]

There are som workaround method fo this task:
- You can merge the created pdf files by usega a third party pdf-manager software, like the PDFTK Builder.
- You can merge the sheets by usage of a virtual pdf printer software, like the PDF Creator. In this case you must use a Print command instead of the StoreT/StoreAs.
- You can copy the calculation ranges cyclically and with a shifted address of the target range (4 times) onto a helper sheet, and then you will able to export the unique pdf with all of the desired parameters and calculations.

[patel]

Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?

[Zizi64]

I can not give help you for a "direct way solution" of this task. I do not know any "append" parameter or command in the API.

[Villeroy]

Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?

There are ready made solution to the problem. What you are trying to do is a workaround.

[Sébastien C]

I agree Villeroy

It is funny ... That is exactly the job I am doing right now... There is a plethora of command-line software to do that! Just for example: PDFtk, Sejda SDK (in Java), PDFJam... I suppose other ones than I do not know yet.

For my part, I chose PDFJam because what I wanted to do was a bit complicated (more complicated than just assembling four PDFs from macros). In any case, do not forget the possible delegation, from the Basic, to the command line (via the Shell function).

You can make your four PDFs, then launch the Shell that will assemble them into one, and then destroy four starting files with the Kill instruction.
Some example of PDFtk are here. Some examples of Sejda SDK are here. And the manual for PDFjam is here (PDF).

Have a nice choice !

[patel]

Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?

There are ready made solution to the problem. What you are trying to do is a workaround.

I mean Append for storeToURL
Do you think it's possible copy the used range to a new sheet, change the C5 cell content, then copy the used range to a new sheet and so on ..?
I tried it without success.
I'm able to save all sheets to unique pdf.

[patel]

You can make your four PDFs, then launch the Shell that will assemble them into one, and then destroy four starting files with the Kill instruction.
Some example of PDFtk are here. Some examples of Sejda SDK are here. And the manual for PDFjam is here (PDF).

Have a nice choice !

Thank You Sébastien for links, I know pdftk, but I like challenges

[Sébastien C]

If I may...

Code: Select all   Expand viewCollapse view

Sub myCopyRange()
 Dim  mySheets As Object
 Dim myName_01 As String
 Dim         i As Integer

  mySheets = thisComponent.sheets
 myName_01 = "report"

 For i = 4 To 2 step -1
  mySheets.copyByName(myName_01, myName_01 & "_" & format(i, "00"), mySheets.getByName(myName_01).rangeAddress.Sheet + 1)
 Next i
End Sub

[patel]

Did you test the suggested code ? the new graphs are binded to sheet Report, not to Report_2, Report_3 ...

[Sébastien C]

Did you test the suggested code ?

Not enough, that's for sure ...

But the problem is interesting. If it does not pose any problem to set the C5 cell of each newly created sheet, it remains that reattaching the chart's parameters is a disaster. I arrive without worry to specify the right page's number to each graph. But it erases all the other data in the graph!

Does anyone have a known solution for this?

Code: Select all   Expand viewCollapse view

Sub myCopyRange()
 Dim  mySheets As Object, mySheet As Object, myChart As Object, myZones() As Object
 Dim myName_01 As String,  myName As String
 Dim         i As Integer,      j As Integer

  mySheets = thisComponent.sheets
 myName_01 = "report"

 ' Add four sheets and set the cell C5.
 For i = 4 To 2 step -1
  myName = myName_01 & "_" & format(i, "00")
  mySheets.copyByName(myName_01, myName, mySheets.getByName(myName_01).rangeAddress.Sheet + 1)
  mySheets.getByName(myName).getCellRangeByName("C5").string = chr(64 + i)
 Next i

 ' Now, fix the datas of charts.
 For i = 2 To 4
  myName = myName_01 & "_" & format(i, "00")
  myChart = mySheets.getByName(myName).charts(0)
  myZones = myChart.ranges

  For j = 0 To ubound(myZones)
   myZones(j).sheet = i
  Next j

  myChart.ranges = myZones()
 Next i
End sub

[patel]

Solved by Huber Lambert on italian forum

Code: Select all   Expand viewCollapse view

sub PDF_tutti() ' hubert lambert
  on error goto unlock
    doc = thiscomponent
    sheets = doc.Sheets
    status = doc.CurrentController.StatusIndicator
    report = doc.Sheets.report
    existing = doc.Sheets.Count-1
    rappresantante = report.getCellByPosition(2, 4)
    memval = rappresantante.String
    rappresentanti = doc.Sheets.getCellRangeByPosition(1, 7, 1, 10, 0).DataArray
    status.start("Wait please...", ubound(rappresentanti)+3)

    for n = 0 to ubound(rappresentanti)
        nome = rappresentanti(n)(0)
        rappresantante.setString(nome)
        if sheets.hasByName("report " + nome) then
            sheets.removeByName("report " + nome)
        end if
        sheets.copyByName("report", "report " + nome, sheets.Count)
        f = sheets.getByName("report " + nome)
        f.AutomaticPrintArea = True
        status.setValue(n+1)
        chart = f.Charts(0).EmbeddedObject
        chart.Data.Data = chart.Data.Data
    next n
    ' memorizza printing areas
    dim printareas(existing)
    for n = 0 to existing
        foglio = doc.Sheets(n)
        printareas(n) = array(foglio.AutomaticPrintArea, foglio.PrintAreas)
        foglio.AutomaticPrintArea = False
        foglio.PrintAreas = array()
    next n
    ' esporta PDF
    dest = "F:/Download/chris1971_tutti.pdf"
    dim filterdata(0) as new com.sun.star.beans.PropertyValue
    filterdata(0).Name = "PageLayout"
    filterdata(0).Value = 1
    dim args(1) as new com.sun.star.beans.PropertyValue
    args(0).Name = "FilterName"
    args(0).Value = "calc_pdf_Export"
    args(1).Name = "FilterData"
    args(1).Value = filterdata
    status.setValue(n+2)
    doc.storeToURL(convertToUrl(dest), args())
    ' ripristina printing areas
    for n = 0 to existing
        status.setValue(n+3)
        foglio = doc.Sheets(n)
        printareas(n) = array(foglio.AutomaticPrintArea, foglio.PrintAreas)
        foglio.AutomaticPrintArea = printareas(n)(0)
        foglio.PrintAreas = printareas(n)(1)
    next n
    ' cancella fogli temporanei
    for each row in rappresentanti
        status.setValue(n+4)
        nome = "report " + row(0)
        if sheets.hasByName(nome) then
            sheets.removeByName(nome)
        end if
    next row
  unlock:
    rappresantante.setString(memval)
    status.end()
    if err = 0 then
        ' apri il pdf
        scelta = msgbox("File created in <" + dest + ">" + string(2, chr(10)) + "Open ?", 4, "")
        if scelta = 6 then
            systemshell = com.sun.star.system.SystemShellExecute.create()
            systemshell.execute(convertFromURL(dest), "", 0)
        end if
    else
        msgbox "Error " & err & ": " & error + chr(10) + "alla riga " + erl + chr(10), 16 ,"error occurred"
    end if
end sub