[Solved] Excel to OOffice calc

Discuss the spreadsheet application

[Solved] Excel to OOffice calc

Postby ironpower » Sun Nov 22, 2015 7:47 pm

Hello I have this code in excel that saves the sheet in a PDF file, and put the name of pdf file the value of cell "E2", but in OpenOffice calc I can't put the name of pdf file the value of cell "E2"

VBA:

Code: Select all   Expand viewCollapse view
Sub macrogravar()
'
' macrogravar Macro
'

'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="D:\Caixadiario\ " & Range("e2") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    Range("H23").Select
End Sub


On the office calc I have this code:


sub gravarpdf
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///D:/caixadiario/ . pdf"
args1(1).Name = "FilterName"
args1(1).Value = "calc_pdf_Export"
args1(2).Name = "FilterData"
.......
dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args1())


end sub
Last edited by Hagar Delest on Mon Nov 23, 2015 11:37 pm, edited 1 time in total.
Reason: tagged [Solved].
openoffice 4.1.2 windows XP
ironpower
 
Posts: 4
Joined: Sun Nov 22, 2015 7:38 pm

Re: Excel to OOffice calc

Postby Villeroy » Sun Nov 22, 2015 8:25 pm

If you would spend some 15 minutes with some text about OpenOffice macro programming, reading from and writing to cells would be plain obvious.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel to OOffice calc

Postby ironpower » Sun Nov 22, 2015 9:01 pm

Thanks for the help, it's always easy when people have the knowlodge. :super:
openoffice 4.1.2 windows XP
ironpower
 
Posts: 4
Joined: Sun Nov 22, 2015 7:38 pm

Re: Excel to OOffice calc

Postby Villeroy » Sun Nov 22, 2015 9:21 pm

We are no human macro recorders. There are many millions of foolish VBA macros.
Last edited by Villeroy on Sun Nov 22, 2015 10:26 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel to OOffice calc

Postby Zizi64 » Sun Nov 22, 2015 9:30 pm

Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Excel to OOffice calc

Postby ironpower » Sun Nov 22, 2015 11:10 pm

Thanks for the help, I'm reading the docs, and I'm trying to fix my problem.
openoffice 4.1.2 windows XP
ironpower
 
Posts: 4
Joined: Sun Nov 22, 2015 7:38 pm

Re: Excel to OOffice calc

Postby Zizi64 » Mon Nov 23, 2015 9:44 am

From the linked pages (it is not a complete code but snippets only:

Code: Select all   Expand viewCollapse view
Dim oDoc as object
Dim oSheet as object
Dim oCell as object
Dim MyText as string

'Get the active document:
oDoc = Thiscomponent


'Get a sheet by index:
oSheet = ThisComponent.Sheets(0)

'Get a sheet by name:
oSheets = oDoc.Sheets  'get the container of all Sheets
oSheet = oSheets.getByName("Sheet2")   'get the sheet named Sheet2

'Get the active sheet:
oSheet=oDoc.getcurrentcontroller.activesheet


'Get a cell by position:
oCell=oSheet.getCellByPosition(0,0) 'These coordinates means: the cell A1


'Get text from a cell:
MyText = oCell.string


'Put the text into the args1:
args1(0).Value = "file:///D:/caixadiario/" + MyText + ". pdf"

Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Excel to OOffice calc

Postby ironpower » Mon Nov 23, 2015 4:38 pm

Thanks a lot for your help, I did same changes, but your code it was perfect.
Thanks :D
openoffice 4.1.2 windows XP
ironpower
 
Posts: 4
Joined: Sun Nov 22, 2015 7:38 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests