Export as pdf in a for loop with variable values and filenames

English
#1

i want to export as pdf with a cell value as filename. i need to save 500 pdf with increment cell value of A1 by 1 each time , other cell values will be changed depending on the value in A1. Sample code i Used if below. A3 is taken as the file name.
the code used without loop is below. Please add the loop for 1to 500. i am very new to coding so please include the whole code.

sub exportAsPDFwithFilename
Dim oDescriptor

rem define variables
dim document   as object
dim dispatcher as object

rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oSheets = ThisComponent.getSheets()
oSheet0 = oSheets.getByIndex(0)

rem name of the file
filename = oSheet0.getCellRangeByName("A3").getString()

rem Prepare for PDF export
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///home/user/Desktop/New" + filename 
args1(1).Name = "FilterName"
args1(1).Value = "calc_pdf_Export"

dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
end sub

This is working. BUt each time I need to change value of A1 manually from 1 to 500. i need to add loop for this. but faild. Any helps are highly appreciated and thanks in advance

#2

So far… does it make sense, to produce 500 mostly identical copies of the same file?? …
…
…
NO!!

#3

not identical. all the cell values including A3, which is supposed to be the file name will be changed with respect the value in A1. used vlookup to take values from another sheet.

#4 
sub exportAsPDFwithFilename
  Dim oDescriptor

  rem define variables
  dim document as object
  dim dispatcher as object

  rem get access to the document
  document = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  oSheets = ThisComponent.getSheets()
  oSheet0 = oSheets.getByIndex(0)

  rem name of the file
  filename = oSheet0.getCellRangeByName("A3").getString()

  rem Prepare for PDF export
  dim args1(1) as new com.sun.star.beans.PropertyValue
  args1(0).Name = "URL"

  args1(1).Name = "FilterName"
  args1(1).Value = "calc_pdf_Export"
  for i = 1 to 500
      oSheet0.getCellRangeByName("A1").Value = i
      filename = oSheet0.getCellRangeByName("A3").getString()
      args1(0).Value = "file:///home/user/Desktop/New" + filename
      dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
  next i
end sub
#6

Thank you. perfectly ok

#5

This macro is with Autocalculate and progressbar, because export 500 files is very slow

Don’t forget to uncheck File/ Export as PDF → General/ View PDF after export, because it is very slow if the exported files as shown in PDF viewer

Set constant cmax to 500

Sub exportAsPDFwithFilename
	on local error goto bug
	
	const cmax=50 'maximum in your loop
	
	dim oDoc as object, oDescriptor as object, document as object, dispatcher as object, oSheets as object, oSheet0 as object, filename$, i%, oCell as object, sUrl$, a&, min&, max&
	dim oDlg as object, oPrubeh as object, oButton as object, iStep& 'variables for progressbar

	rem get access to the document
	oDoc=ThisComponent
	document=oDoc.CurrentController.Frame
	dispatcher=createUnoService("com.sun.star.frame.DispatchHelper")
	oSheets=ThisComponent.getSheets()
	oSheet0=oSheets.getByIndex(0)
	
	rem autocalculation
	oDoc.enableAutomaticCalculation(true) 'set automatic calculation
	oDoc.CurrentController.ComponentWindow.Visible=false 'hide the window with sheets (maybe it will be faster)

	dim args1(1) as new com.sun.star.beans.PropertyValue
	args1(0).Name="URL"
	args1(1).Name="FilterName" : args1(1).Value="calc_pdf_Export"

	oCell=oSheet0.getCellRangeByName("A1")
	min=oCell.Value 'initial value in A1
	max=min-1+cmax

	rem progressbar init
	oDlg=progressBarInit(min, max, oDoc) 'show the progressbar
	oDlg.Title=min & "/" & max
	oPrubeh=oDlg.getControl("Pprogress")
	oButton=oDlg.getControl("Pbutton")
	
	for i=min to max
		filename=oSheet0.getCellRangeByName("A3").getString() 'name of the file
		rem prepare fo PDF
		sUrl="file:///home/user/Desktop/New" & filename
		if FileExists(sUrl) then 'if file exists you can delete one
			'if msgbox("File " & sUrl & " exists" & chr(13) & "Delete one?", 52)=6 then kill(sUrl) 'delete old PDF with confirmation msgbox
			kill(sUrl) 'delete old PDF automatically
		end if
		args1(0).Value=sUrl
		dispatcher.executeDispatch(document,  ".uno:ExportDirectToPDF",  "",  0,  args1() )
		
		rem increase value in A1
		if i<max then 'increase only if it isn't last value in loop
			oCell.Value=oCell.Value+1 'increase a Value in A1
		end if
		
		rem update progressbar
		iStep=iStep+1
		if iStep=10 then 'update progressbar after some steps
			oPrubeh.Value=i
			oDlg.Title=i & "/" & max
			iStep=0
		end if
		if oButton.Model.State=1 then 'test if Cancel was pressed
			bCancel=true
			exit for
		end if
	next i
bug:
	if NOT isNull(oDlg) then oDlg.dispose() 'dispose the progressbar
	oDoc.CurrentController.ComponentWindow.Visible=true 'show the window with sheets
End Sub

rem UKAZATEL PRŮBĚHU - zobrazuje se ve středu okna Libre
Function progressBarInit(min&, max&, optional oDoc as object) as object 'vrátí objekt dialogového okna; při chybějícím oDoc ho přichytí k Desktopu
	on local error goto chyba
	dim oDlg as object, oDlgModel as object, oButtonModel as object, oProgress as object, oWindow as Object
	dim oSize as object, oSiz as new com.sun.star.awt.Size, koef as double
	const iDlgWidth=140, iDlgHeight=45 'bacha, nejde o pixely jako kdyby se vytvářelo dialogové okno v Basic editoru a pak volalo; dále je pro vystředění okna použita metoda convertSizeToPixel a nastavena proměnná koef

	rem model dialogového okna
	oDlgModel=CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
	with oDlgModel
		.Width=iDlgWidth
		.Height=iDlgHeight
	end with

	rem ukazatel průběhu
	oProgress=oDlgModel.createInstance("com.sun.star.awt.UnoControlProgressBarModel") 'objekt ukazatele průběhu
	with oProgress
		.Name="Pprogress" 'jméno pro makro
		.ProgressValueMin=min 'minimum
		.ProgressValueMax=max 'maximum
		.ProgressValue=0 'aktuální hodnota
		.Width=120
		.Height=15
		.positionX=10
		.positionY=5
		.Border=3 'rámeček
	end with
	oDlgModel.insertByName("Pprogress", oProgress) 'přidat do modelu
	
	rem tlačítko Zrušit
	oButtonModel=oDlgModel.createInstance("com.sun.star.awt.UnoControlButtonModel")
	with oButtonModel
		.Name="Pbutton"
		.Width=40
		.Height=15
		.PositionX=50
		.PositionY=25
		.Label="Zrušit"
		.PushButtonType=com.sun.star.awt.PushButtonType.STANDARD
		.TabIndex=0
		.Toggle=true 'aktivace detekce vlastnosti State tlačítka
	end with
	oDlgModel.insertByName("Pbutton", oButtonModel)

	rem vykreslení dialogového okna
	oDlg=CreateUnoService("com.sun.star.awt.UnoControlDialog") 'dialog
	oDlg.visible=false 'skrýt dialog ať neproblikává
	oDlg.setModel(oDlgModel) 'nastavit model dialogovému oknu

	rem přidat dialogové okno k oknu dokumentu nebo Desktopu
	oWindow=CreateUnoService("com.sun.star.awt.Toolkit") 'dialogové okno
	if isMissing(oDoc) then 'přidat k Desktopu - pozor, systém může po chvíli vypsat že program neodpovídá
		oDlg.createPeer(oWindow,null)
	else 'přidat okno k oDoc
		dim oToolkit as object
		oToolkit=oDoc.currentController.frame.containerWindow
		oDlg.createPeer(oWindow,oToolkit) 'spojení
	end if

	rem propočty na vystředění dialogového okna
	with oSiz 'rozměr z kterého budu přepočítávat koeficient pro usazení dialogového okna průběhu doprostřed
		.Width=iDlgWidth
		.Height=iDlgHeight
	end with
	koef=iDlgWidth / oDlg.convertSizeToPixel(oSiz, com.sun.star.util.MeasureUnit.APPFONT).Width 'koeficient pro korekci dialogového okna

	oSize=oDoc.CurrentController.Frame.ContainerWindow.GetPosSize 'rozměry okna Calcu
	with oDlgModel
		.positionX=fix(koef*(oSize.Width-iDlgWidth/koef)/2) 'pozice X od levého horního rohu okna; šířku dialogu je třeba brát zvětšenou koef, pozici X pak zmenšenou koef
		.positionY=fix(koef*(oSize.Height-iDlgHeight/koef)/2) 'pozice Y od levého horního rohu okna
	end with
	oDlg.visible=true 'zobrazit dialog
	progressBarInit=oDlg
	exit function
chyba:
	msgbox("Error " & Err & ": " & Error$ + chr(13) + "Line: " + Erl , 16 ,"progressBarInit")
End Function
#7

thank you very much

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.