converting ppt to pdf launched from xls

[pm]

hello,

is there any possibility to convert powerpoint file (ppt) into pdf?

file structure is shown below:

xls files -> powerpoint -> pdf

and i'd like to write VBA in one of xls files, which
1. open ppt file
2. update automaticly hiperlinks (to mentioned xls files without notification
about links), as charts, and tables which are pasted as hiperlinks
3. save ppt which is necessary before converting
4. convert into pdf file
5. close ppt file

the reason of my question is that i have to create almost 300 pdf files, which
(i think) can be created in some loop in VBA..

 

[voodooJoe]

yes. i'm not sure if its the only way, but i have used the Acrobat
Distiller to do this.

using this method : you need to have adobe installed and a VBA reference
set, print to ps file, use distiller to convert to pdf

you also need to set the activeprinter to the 'adobe printer' that installs
when you install acrobat (get this name by going into the vba immediate
window and typing "?activeprinter"
yes, you still need to even though the print line calls the activerprinter -
don't know why, but mine crashed w/o it
i put that value in a custom document property

the psfilename i used is a temp name like "tmp.ps" that the loop uses and
kills over and over

Public Const PSFileName As String = "tmp.ps"
Public myPDF As PdfDistiller

Private Sub CreatePDF(ws As Worksheet, TargetDirectory, PDFFileName)
If myPDF Is Nothing Then Set myPDF = New PdfDistiller
Application.ActivePrinter =
ThisWorkbook.CustomDocumentProperties("AdobePrinter").Value
ws.Range("Print_Area").PrintOut copies:=1, Preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prtofilename:=TargetDirectory & PSFileName
myPDF.FileToPDF TargetDirectory & PSFileName, TargetDirectory &
PDFFileName & ".pdf", ""
Kill TargetDirectory & PSFileName
If Len(Dir(TargetDirectory & PDFFileName & ".log")) > 0 Then Kill
TargetDirectory & PDFFileName & ".log"
End Sub

- voodooJoe

 

[pm]

using this method : you need to have adobe installed and a VBA reference
set, print to ps file, use distiller to convert to pdf

what references actually?

you also need to set the activeprinter to the 'adobe printer' that installs
when you install acrobat (get this name by going into the vba immediate
window and typing "?activeprinter"
yes, you still need to even though the print line calls the activerprinter -
don't know why, but mine crashed w/o it
i put that value in a custom document property

the psfilename i used is a temp name like "tmp.ps" that the loop uses and
kills over and over

Public Const PSFileName As String = "tmp.ps"
Public myPDF As PdfDistiller

Private Sub CreatePDF(ws As Worksheet, TargetDirectory, PDFFileName)
If myPDF Is Nothing Then Set myPDF = New PdfDistiller
Application.ActivePrinter =
ThisWorkbook.CustomDocumentProperties("AdobePrinter").Value
ws.Range("Print_Area").PrintOut copies:=1, Preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prtofilename:=TargetDirectory & PSFileName
myPDF.FileToPDF TargetDirectory & PSFileName, TargetDirectory &
PDFFileName & ".pdf", ""
Kill TargetDirectory & PSFileName
If Len(Dir(TargetDirectory & PDFFileName & ".log")) > 0 Then Kill
TargetDirectory & PDFFileName & ".log"
End Sub

it seems fine to me, but as i wrote i'd like to print powerpoint file to pdf
or ps, but i have to call this module from excel file:
1. open report.ppt
2. update hyperlinks without notification about links
3. save report.ppt
4. convert report.ppt to report.ps or report.pdf
5. close report.ppt

first question: is it possible at all from excel VBA?

 

[voodooJoe]

what references actually?

reference to Adobe Acrobat Distiller - if you have Adobe installed, you
should see it.

it seems fine to me, but as i wrote i'd like to print powerpoint file to
pdf
or ps, but i have to call this module from excel file:

well, dude (or dude-ette as the case might be), you DID post this to an
excel programming news group

1. open report.ppt
2. update hyperlinks without notification about links
3. save report.ppt
4. convert report.ppt to report.ps or report.pdf
5. close report.ppt

first question: is it possible at all from excel VBA?

Yes, its possible to do it from Excel. I recon it's also possible to do it
from PowerPoint - or any other application that uses VBA.

Set a = CreateObject("PowerPoint.Application")
the use the PowerPoint object model to open, print, ...

good luck - voodooJoe

 

[pm]

voodooJoe napisal:

reference to Adobe Acrobat Distiller - if you have Adobe installed, you
should see it.

yes. Adobe Distiller checked

Yes, its possible to do it from Excel. I recon it's also possible to do it
from PowerPoint - or any other application that uses VBA.

No, i don't want use powerpoint VBA, just everything in excel

Set a = CreateObject("PowerPoint.Application")
the use the PowerPoint object model to open, print, ...

sorry for stupid question, but how?

a.open filename("..") does not work..

 

[pm]

a.open filename("..") does not work..

i find sth like this:

Dim PPObj As Object
Set PPObj = CreateObject("PowerPoint.application")
With PPObj
.presentations.Add
.presentations.Open Filename:="F:\Analizy ISI\pl\prob.ppt"
.Visible = True

but an error ocures: '-2147188160(80048240)'

Presentations.Open : invalid request. the powerpoint frame window
does not exist

which means that powerpoint application must be open?
but how can i open powerpoint application and close it after
printing to ps. file...

 

[voodooJoe]

the fact that .presentations stays lower case is a hint the syntax is wrong

once you set the ppt object and are refer to it, you essentially need to use
the ppt object model - which is a little different than the excel object
model (objects, properties, methods, events, etc)

google the internet for tips on how to work the PowerPoint object model - or
post to the ppt newsgroup - its been too long since I wrote vba for ppt for
me to be any help there

sorry, but you're on your own now -- voodooJoe

 

[pm]

sorry, but you're on your own now -- voodooJoe

ok. but what about updating links in presentation?

i tried sth like this but does not work...

aLinks = .presentations.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
' MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

 

[voodooJoe]

again the 'p' in presentations does not capitalize

in VBA, type in all your code in lowercase. if the syntax is correct, the
key words will capitalize themselves automatically

look at you code - the 'p' is lowercase. this is a BIG clue that something
is wrong.

perhaps:

- the way you are using presentations is incorrect
- you are prferring to the presentations COLLECTION when you shoud be
referring to 1 presentation specifically
- linksources is a valid property - but not for presentation(s) object

- vdJ

 

[pm]

voodooJoe napisal:

perhaps:

- the way you are using presentations is incorrect
- you are prferring to the presentations COLLECTION when you shoud be
referring to 1 presentation specifically
- linksources is a valid property - but not for presentation(s) object

perheps.. but i do not know anything about referring to powerpoint files

this is my first time i try do something with ppt from excel..

i tried:

aLinks = PPObj.LinkSources(xlOLELinks)

and

aLinks = presentation("prob.ppt").LinkSources(xlOLELinks)
but nothing capitalize...

seriously. i do have no idea how to update links from excel
or even how to update links on opening ppt file (but without notification
about existing links)...

but i've already posted to apropriate group..

 

[voodooJoe]

looks like you are going to have to learn the hard way - hit the internet
and search. its out there somewhere.

 

[pm]

looks like you are going to have to learn the hard way - hit the internet
and search. its out there somewhere.

ok. but is there possibility to update links in ppt? just say me in what
direction should i search: updating from xls or updating on opening ppt?

anyway thanks

 

[pm]

Public Const PSFileName As String = "tmp.ps"
Public myPDF As PdfDistiller

Private Sub CreatePDF(ws As Worksheet, TargetDirectory, PDFFileName)
If myPDF Is Nothing Then Set myPDF = New PdfDistiller
Application.ActivePrinter =
ThisWorkbook.CustomDocumentProperties("AdobePrinter").Value
ws.Range("Print_Area").PrintOut copies:=1, Preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prtofilename:=TargetDirectory & PSFileName
myPDF.FileToPDF TargetDirectory & PSFileName, TargetDirectory &
PDFFileName & ".pdf", ""
Kill TargetDirectory & PSFileName
If Len(Dir(TargetDirectory & PDFFileName & ".log")) > 0 Then Kill
TargetDirectory & PDFFileName & ".log"
End Sub

ok. now i have sth like that:

Public Sub PPTPrint()
Dim PPObj As Object
Set PPObj = CreateObject("PowerPoint.application")
With PPObj

.presentations.Open Filename:="F:\Analizy ISI\pl\prob.ppt"
.Visible = True
.Run "Prob.ppt!UpdtAll"
.presentations("prob.ppt").Save

' printing to ps or pdf

.presentations("prob.ppt").Close

End With
End Sub

this sub has to produce 300 pdf or ps (i save them as pdf later)
in a loop (loop would be added later), and i'm asking about two
things:
- how to save in location C:\Reports\
- how to save as pdf or ps
- how to name files (number of loop 1-300)

once again, thanks for all your help

rgs