Page 1 of 1
[Solved] Calc-Export changes on the same sheet to unique pdf
Posted: Mon Feb 04, 2019 1:09 pm
by 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
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
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Mon Feb 04, 2019 5:11 pm
by 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.
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Mon Feb 04, 2019 7:27 pm
by patel
Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Mon Feb 04, 2019 9:25 pm
by 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.
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Mon Feb 04, 2019 9:44 pm
by Villeroy
patel wrote: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.
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Tue Feb 05, 2019 4:44 am
by 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 !
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Tue Feb 05, 2019 5:25 pm
by patel
Villeroy wrote:patel wrote: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.
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Tue Feb 05, 2019 5:31 pm
by patel
Sébastien C wrote:
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
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Tue Feb 05, 2019 8:06 pm
by Sébastien C
If I may...
Code: Select all
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
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Tue Feb 05, 2019 8:39 pm
by patel
Did you test the suggested code ? the new graphs are binded to sheet Report, not to Report_2, Report_3 ...
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Tue Feb 05, 2019 10:14 pm
by Sébastien C
patel wrote: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
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
Re: Calc-Export some changes on the same sheet to unique pdf
Posted: Wed Feb 06, 2019 1:52 pm
by patel
Solved by Huber Lambert on italian forum
Code: Select all
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