Excel to PDF

[sedgely]

Thanks to several previous threads I have the following code to create a pdf file from an Excel sheet(s):

Sub pdfConverter()

Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant


'Define the path and filenames (can get the names from a cell, and add the path & extension):
PSFileName = "\\syas2k05\intranet\demand_analysis\liveStuff\SSP\WeeklyPerformanceSummary\Archive\" & Worksheets("SSP").Range("$B$1").Value & ".PS"
PDFFileName = "\\syas2k05\intranet\demand_analysis\liveStuff\SSP\WeeklyPerformanceSummary\Archive\" & Worksheets("SSP").Range("$B$1").Value & ".PDF"

'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)

'The Sendkeys characters are the full path and filename, followed by the "Enter" key.
' These are buffered until the "print to file" screen appears:

SendKeys PSFileName & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "c:\Program Files\Adobe\Acrobat 6.0\Distillr\Acrodist.exe" & " /n /q /o" & PDFFileName & " " & PSFileName

'Call the Acrobat Distiller to distill the PS file. ReturnValue is zero
'if the application doesn't open correctly:

ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName & "failed."


End Sub

This works fine if i run it "manually" but as soon as i put it in the workbook on_open, I get a dialog asking for an output filename. Anyone any idea what's happening?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[sedgely]

OK I've sorted that issue, however i now want to create 2 PDF files from the same sheets (with different filenames)
i have amended the code to:

Sub pdfConverter1()

Application.ActivePrinter = "Adobe PDF on NE02:"

Dim PSFileName1 As String
Dim PDFFileName1 As String, [b][COLOR=red]PDFFilename2 As String[/color][/b]
Dim DistillerCall1 As String, [b][COLOR=red]DistillerCall2 As String[/color][/b]
Dim ReturnValue As Variant


'Define the path and filenames (can get the names from a cell, and add the path & extension):
PSFileName1 = "\\syas2k05\intranet\demand_analysis\liveStuff\SSP\WeeklyPerformanceSummary\Archive\" & Worksheets("SSP").Range("$B$1").Value & ".PS"
PDFFileName1 = "\\syas2k05\intranet\demand_analysis\liveStuff\SSP\WeeklyPerformanceSummary\Archive\" & Worksheets("SSP").Range("$B$1").Value & ".PDF"
[b][COLOR=red]PDFFilename2 = "\\syas2k05\intranet\demand_analysis\liveStuff\SSP\WeeklyPerformanceSummary\Archive\LastWeeksSSP.PDF"[/color][/b]

'If the files already exist, delete them:
If Dir(PSFileName1) <> "" Then Kill (PSFileName1)
If Dir(PDFFileName1) <> "" Then Kill (PDFFileName1)
[b][COLOR=red]If Dir(PDFFilename2) <> "" Then Kill (PDFFilename2)[/color][/b]

'The Sendkeys characters are the full path and filename, followed by the "Enter" key.
' These are buffered until the "print to file" screen appears:
Worksheets(Array("SSP", "CHARTS", "volumeCharts")).Select
SendKeys PSFileName1 & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName1 = Chr(34) & PSFileName1 & Chr(34)
PDFFileName1 = Chr(34) & PDFFileName1 & Chr(34)
[b][COLOR=red]PDFFilename2 = Chr(34) & PDFFilename2 & Chr(34)[/color][/b]
DistillerCall1 = "c:\Program Files\Adobe\Acrobat 6.0\Distillr\Acrodist.exe" & " /n /q /o" & PDFFileName1 & " " & PSFileName1
[b][COLOR=red]DistillerCall2 = "c:\Program Files\Adobe\Acrobat 6.0\Distillr\Acrodist.exe" & " /n /q /o" & PDFFilename2 & " " & PSFileName1[/color][/b]

'Call the Acrobat Distiller to distill the PS file. ReturnValue is zero
'if the application doesn't open correctly:

ReturnValue = Shell(DistillerCall1, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName1 & "failed."

[b][COLOR=red]ReturnValue = Shell(DistillerCall2, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName1 & "failed."[/color][/b]

End Sub

Now i get an error message saying the .PS file is in use by another process. So the distiller is opening the file on the first call but cannot reopen it for the second call. how can i distill 2 files from the same .ps file?
Any help please?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[Birddog]

When I attempt to run the original code in this thread from an Excel macro, I get the following msg in the Distiller log, as well as in two text files called in "MyPdfFile" & "myPSFile" which are created in the folder where I'm trying to save:

%%[ Error: syntaxerror; OffendingCommand: ) ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%

I am running Acrobat 7.0 so I changed the code to refer to 7.0 instead of 6.0 but other than that everything is the same. I "unchecked" Do not send fonts to "Adobe PDF" in the printing preferences of my Adobe printer since that seemed to be an issue when creating post script files.

Any help would be greatly appreciated. My code follows:

Private Sub CommandButton2_Click()


Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant


'Define the path and filenames (can get the names from a cell, and add the path & extension):
PSFileName = "c:\audits\TestExcel\myPSFile" & ".PS"
PDFFileName = "c:\audits\TestExcel\MyPdfFile" & ".PDF"

'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)

'The Sendkeys characters are the full path and filename, followed by the "Enter" key.
' These are buffered until the "print to file" screen appears:

SendKeys PSFileName & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "c:\Program Files\Adobe\[b]Acrobat 7.0[/b]\Distillr\Acrodist.exe" & " /n /q /o" & PDFFileName & " " & PSFileName

'Call the Acrobat Distiller to distill the PS file. ReturnValue is zero
'if the application doesn't open correctly:

ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName & "failed."

End Sub