[macro Calc] Selected cell is missing from exported PDF

kawsay · September 10, 2020, 12:52pm

We’ve a LO Calc form with a “Send Email” button that exports the sheet as PDF and sends it by email. The last cell that is filled by users is not part of the PDF unless the user click another random cell. Reformulation: after the users has fill the last cell, he must click anywhere on the sheet to “validate” the newly added value; otherwise it won’t be part of the PDF.

As I’m brand new to Macros, I’m comparing our script to the one from the Wiki. Sadly I can’t figure how the Wiki’ script deals with this problem.

Is there a way to “refresh” the sheet before exporting it ? How could I get this last cell to be part of the exported PDF ?

As reference, here’s the part of the Macro that deals with exportation:

Sub ExportToPDF()
	'GET SOME NAMES
	Dim oDocument As Object, oSheet As Object, oCell As Object
	Dim CIS as String, demandeur as String
	oDocument = ThisComponent
	oSheet= oDocument.getSheets.getByName("formulaire")
	oCell = oSheet.getCellRangeByName("CIS") 'get CIS value
	CIS = oCell.getString
	oCell = oSheet.getCellRangeByName("DEMANDEUR") 'get DEMANDEUR value
	demandeur = oCell.getString
	
	'EXPORT PDF
	dim document as object
	dim dispatcher as object
	'Get temporary user path
	Dim ps As Object, tempDir As String
	ps = CreateUnoService("com.sun.star.util.PathSettings")
	tempDir = ps.Temp
	'Set filename
	Dim fileName as String
	
	fileName = "lovely name here"
	
	ThisComponent.addActionLock
	ThisComponent.LockControllers
	
	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

	Dim path as String
	path =tempDir+"/"+fileName+".pdf"
	Open path For Append As #1
	Close #1
	
	dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "URL"
	args1(0).Value = tempDir+"/"+fileName+".pdf" 
	
	dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())

anon73440385 · September 10, 2020, 1:14pm

May be is there a screen refresh method for calc could help (hint: .uno:Repaint).

kawsay · September 10, 2020, 1:27pm

Thanks for your answer, it does help ! I believe I must have a better understanding of uno modules before solving this issue.

schiavinatto · September 10, 2020, 3:36pm

All data filling in cells must end with any procedure that leaves the current cell.

kawsay · September 11, 2020, 7:52am

I’ve found great resources about Macros, UNO
and DispatcherHelper (especially executeDispatch()). Though, on this document from Andrew Pitonyak, table 94 page 265, it says the 2 last arguments (long search flag & PropertyValue()) are optional; but not filling the PropertyValue() returns an error expected 5 arguments, got 4.

What am I missing ? How could I apply the .uno:Repaint to the document variable (which corresponds to ThisComponent.CurrentController.Frame) ?

Most of my attempts looks like dispatcher.executeDispatch(document, ".uno:Repaint", "", 0, <????>)

kawsay · September 11, 2020, 11:59am

Thanks for your help folks !

kawsay · September 11, 2020, 11:59am

Using .uno:GoToCell allow to mimic the required random click, thus include the last input into the exported document.

I’ve added the following snippet before .uno:ExportDirectToPDF:

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$1"

' Allow last input to be included in the exported PDF
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())