[Solved] Macro to save selected area in AOO Calc as PDF

Creating a macro - Writing a Script - Using the API

[Solved] Macro to save selected area in AOO Calc as PDF

Postby jeevanthara » Mon Aug 05, 2019 7:58 am

I was searching for some code to convert selected area in a sheet as pdf in open office calc. i found the code at
viewtopic.php?f=20&t=79013
The issue i am facing is that pdf created doesn't have a name its kept blank. Is there any way out of this.
I have value at C4 customer name. Can i save pdf in name of customer. I mean depending the value of C4.



Code: Select all   Expand viewCollapse view
sub mypdf

dim args2(1) as new com.sun.star.beans.PropertyValue
dim Arg(0) as new com.sun.star.beans.PropertyValue
Doc   = ThisComponent
Controller = Doc.CurrentController
Sheet = Doc.Sheets.getByName("privatecarprint")
CellRange = Sheet.getCellRangeByName("$A$1:$H$30")
Controller.select(CellRange)
Cell = Sheet.getCellByPosition(6,10)

'Got this here http://wiki.services.openoffice.org/wiki/API/Tutorials/PDF_export
'See also http://user.services.openoffice.org/en/forum/viewtopic.php?f=44&t=1804
Arg(0).Name = "Selection"
Arg(0).Value = CellRange

args2(0).Name = "FilterName"
args2(0).Value = "calc_pdf_Export"
args2(1).Name = "FilterData"
args2(1).Value = Arg()
Doc.storeToURL("file:///c:/users/lic/Desktop/"& Cell.getString()&".pdf",args2())
end sub
Attachments
png.png
Name value is in C$
png.png (3.6 KiB) Viewed 443 times
Last edited by robleyd on Wed Aug 07, 2019 8:08 am, edited 5 times in total.
Open Office 14.1.6

Ubuntu 12.1.14
jeevanthara
 
Posts: 11
Joined: Tue Jul 30, 2019 12:31 pm

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby Zizi64 » Mon Aug 05, 2019 11:27 am

Can i save pdf in name of customer.

Do you want to use
- the actual user name in the operating system?
- the user name of the AOO/LO?

To get the user name from the Windows:
Code: Select all   Expand viewCollapse view
Function My_Environ(My_param as string) as string

      Select Case My_param
      Case "USERNAME", "COMPUTERNAME", "USERDOMAIN", "TMP", "ALLUSERSPROFILE",_
      "CI_HOLOS_CLI", "CLIENTNAME", "windir", "USERPROFILE", "TEMP", "SystemRoot",_
      "SystemDrive", "SESSIONNAME", "ProgramFiles", "PROCESSOR_REVISION",_
      "PROCESSOR_LEVEL", "PROCESSOR_IDENTIFIER", "PROCESSOR_ARCHITECTURE", "PATHEXT",_
      "Path", "OS", "NUMBER_OF_PROCESSORS", "MSDevDir", "LOGONSERVER", "lib", "JAVA_HOME",_
      "include", "HOMEPATH", "HOMEDRIVE", "FP_NO_HOST_CHECK2", "ComSpec",_
      "COMPUTERNAME", "CommonProgramFiles"
         My_Environ = Environ(My_param)
     Case Else
      My_Environ = "Name error"
   End Select
end function


To get the username from the AOO/LO:
viewtopic.php?f=20&t=7415
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8354
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby jeevanthara » Mon Aug 05, 2019 11:58 am

No sir. I just want the file name as the value i entered in cell C4 in my case customer name. I give quotation to different clients a day. or a modified code to open pdf automatically when created will also solve my issue.
Thanks in advance.
Open Office 14.1.6

Ubuntu 12.1.14
jeevanthara
 
Posts: 11
Joined: Tue Jul 30, 2019 12:31 pm

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby Lupp » Mon Aug 05, 2019 2:11 pm

@jeevanthara: Did you read the macro you posted above?
The code
Code: Select all   Expand viewCollapse view
Cell = Sheet.getCellByPosition(6,10)
creates the object assigned to the variable 'Cell' as the cell object belonging to 'Sheet' and having the (0-based API numbering!) column index 6 and row index 10. That's the cell with address "G11". From that cell the name part of the URL the code writes to is taken. If you want to read the name string from cell "C4" you can use
Code: Select all   Expand viewCollapse view
Cell = Sheet.getCellByPosition(2,3)
or
Code: Select all   Expand viewCollapse view
Cell = Sheet.getCellRangeByName("C4")

(I did not check the rest of the code. It looks somehow more complicated than needed.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby jeevanthara » Mon Aug 05, 2019 4:54 pm

thank you. Any way around to open the PDF automatically when it's created?
Open Office 14.1.6

Ubuntu 12.1.14
jeevanthara
 
Posts: 11
Joined: Tue Jul 30, 2019 12:31 pm

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby Zizi64 » Mon Aug 05, 2019 7:11 pm

Any way around to open the PDF automatically when it's created?


You can open any type of the documents with the application associated to the file type:

Code: Select all   Expand viewCollapse view
Sub Open_a_Document(sDocUrl as string)
Dim oSys as Object

   oSys = CreateUnoService("com.sun.star.system.SystemShellExecute")
   oSys.execute(sDocUrl, "", 0)

End Sub
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8354
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby jeevanthara » Tue Aug 06, 2019 7:00 am

sir this code is not working. my final code looks like this

Code: Select all   Expand viewCollapse view
sub mypdf

dim args2(1) as new com.sun.star.beans.PropertyValue
dim Arg(0) as new com.sun.star.beans.PropertyValue
Doc   = ThisComponent
Controller = Doc.CurrentController
Sheet = Doc.Sheets.getByName("privatecarprint")
CellRange = Sheet.getCellRangeByName("$A$1:$H$30")
Controller.select(CellRange)
Cell = Sheet.getCellByPosition(2,3)

'Got this here http://wiki.services.openoffice.org/wiki/API/Tutorials/PDF_export
'See also http://user.services.openoffice.org/en/forum/viewtopic.php?f=44&t=1804
Arg(0).Name = "Selection"
Arg(0).Value = CellRange

args2(0).Name = "FilterName"
args2(0).Value = "calc_pdf_Export"
args2(1).Name = "FilterData"
args2(1).Value = Arg()
Doc.storeToURL("file:///D:/New"& Cell.getString()&".pdf",args2())
end sub

Sub Open_a_Document(sDocUrl as string)
Dim oSys as Object

   oSys = CreateUnoService("com.sun.star.system.SystemShellExecute")
   oSys.execute(sDocUrl, "", 0)

End Sub
Open Office 14.1.6

Ubuntu 12.1.14
jeevanthara
 
Posts: 11
Joined: Tue Jul 30, 2019 12:31 pm

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby robleyd » Tue Aug 06, 2019 7:04 am

I don't see anywhere that you actually call the subroutine Open_a_Document
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2985
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby jeevanthara » Tue Aug 06, 2019 7:11 am

I don’t know how to code. i found the above code from this forum.
Open Office 14.1.6

Ubuntu 12.1.14
jeevanthara
 
Posts: 11
Joined: Tue Jul 30, 2019 12:31 pm

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby JeJe » Tue Aug 06, 2019 11:07 am

Code: Select all   Expand viewCollapse view

args2(1).Name = "FilterData"
args2(1).Value = Arg()
filepath = "file:///D:/New"& Cell.getString()&".pdf"
Doc.storeToURL(filepath,args2())

Open_a_Document(filepath)
end sub

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 584
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to save a selected area in OpenOffice Calc as PDF

Postby jeevanthara » Wed Aug 07, 2019 6:48 am

Thank you. you all have been very helpful. @Jeje @robeleyd @Zizi64. Problem solved.
Open Office 14.1.6

Ubuntu 12.1.14
jeevanthara
 
Posts: 11
Joined: Tue Jul 30, 2019 12:31 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: JeJe and 4 guests