UserNameAlex
New Member
- Joined
- Dec 1, 2009
- Messages
- 1
Ok I have a bug in my code and it is driving me crazy. Please help me!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have several different subs that call a general pdf function (that uses PdfDistiller to create pdfs of the Excel s/s that the subs create) . <o></o>
<o></o>
So for example:<o></o>
<o></o>
Sub 1 will run and at the end of the process call the pdfing function then exits the sub<o></o>
<o></o>
Sub 2 will run and at the end of the process call the pdfing function then exits the sub.<o></o>
<o></o>
Sub 1 and Sub 2 work perfectly when they are run separately!<o></o>
<o> </o>
BUT if I run a Sub that runs Sub 1 and then calls and runs Sub 2 I get the following error message when the pdf function runs in Sub 2:<o></o>
<o></o>
Run-time error '462':
The remote server machine does not exist or is unavailable.<o></o>
<o></o>
This occurs on the following line:<o></o>
<o></o>
PDFapplication.FileToPDF PSFileName, PDFFileName, ""<o></o>
<o></o>
I have found a few things in relation to this error including:<o></o>
http://support.microsoft.com/kb/178510<o></o>
<o></o>
But I'm yet to figure out how to fix it. I suppose the frustrating thing is that it works in isolation but when run consecutively from different subs it fails.<o></o>
<o></o>
I think it's something to do with not exiting the distiller application properly (like what must be done when the sub stops running)? or could it be that the pdf distiller is still busy with the jobs from the other sub? Because if I step through the sub that calls sub 1 and sub 2 (using F8) I do not get the error...... so do I need make the VBA wait for the distiller to finish? I am very confused.<o></o>
<o></o>
I hope this makes sense to someone out there.<o></o>
Any help would be much appreciated!!!!<o></o>
<o> </o>
The full pdf function is code is:<o></o>
Public Function fPDFWorkbook(ReportsToPDF As Range) As Boolean
'For this sub to work the reference to 'Acrobat Distiller' in Tools --> References... needs to be selected.
Dim wbtoPDF As Workbook
Dim sCurrentPrinter As String
Dim PDFapplication As PdfDistiller '.Application
Dim X As Range
Dim FilePathName As String
Dim FileName As String
Set PDFapplication = New PdfDistiller
Dim BaseFileName As String
Dim PSFileName As String
Dim PDFFileName As String
Dim LogFileName As String
'Save the currently active printer
sCurrentPrinter = Application.ActivePrinter
For Each X In ReportsToPDF
Select Case X.Column
Case 4
FilePathName = X.Offset(0, 1)
FileName = X.Offset(0, 2)
Case 3
FilePathName = X.Offset(0, 2)
FileName = X.Offset(0, 3)
Case 2
FilePathName = X.Offset(0, 3)
FileName = X.Offset(0, 4)
End Select
BaseFileName = FilePathName & Left(FileName, Len(FileName) - 4)
PSFileName = BaseFileName & ".ps"
PDFFileName = BaseFileName & ".pdf"
LogFileName = BaseFileName & ".log"
UpdateProgress ("Opening " & FileName & " so that it can be converted to PDF...")
Set wbtoPDF = Excel.Workbooks.Open(FilePathName & FileName, ReadOnly:=True)
UpdateProgress ("Converting " & FileName & " to a Post Script File...")
'Print the workbook to the postscript file .ps
wbtoPDF.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, prtofilename:=PSFileName
'Colse the workbook
wbtoPDF.Close savechanges:=False
UpdateProgress ("Converting " & Left(FileName, Len(FileName) - 4) & ".ps file to a pdf file...")
PDFapplication.FileToPDF PSFileName, PDFFileName, ""
UpdateProgress ("Deleting the .ps and .log files...")
Kill PSFileName
Kill LogFileName
Next X
Application.ActivePrinter = sCurrentPrinter
Set PDFapplication = Nothing
fPDFWorkbook = True
End Function
Cheers Alex
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have several different subs that call a general pdf function (that uses PdfDistiller to create pdfs of the Excel s/s that the subs create) . <o></o>
<o></o>
So for example:<o></o>
<o></o>
Sub 1 will run and at the end of the process call the pdfing function then exits the sub<o></o>
<o></o>
Sub 2 will run and at the end of the process call the pdfing function then exits the sub.<o></o>
<o></o>
Sub 1 and Sub 2 work perfectly when they are run separately!<o></o>
<o> </o>
BUT if I run a Sub that runs Sub 1 and then calls and runs Sub 2 I get the following error message when the pdf function runs in Sub 2:<o></o>
<o></o>
Run-time error '462':
The remote server machine does not exist or is unavailable.<o></o>
<o></o>
This occurs on the following line:<o></o>
<o></o>
PDFapplication.FileToPDF PSFileName, PDFFileName, ""<o></o>
<o></o>
I have found a few things in relation to this error including:<o></o>
http://support.microsoft.com/kb/178510<o></o>
<o></o>
But I'm yet to figure out how to fix it. I suppose the frustrating thing is that it works in isolation but when run consecutively from different subs it fails.<o></o>
<o></o>
I think it's something to do with not exiting the distiller application properly (like what must be done when the sub stops running)? or could it be that the pdf distiller is still busy with the jobs from the other sub? Because if I step through the sub that calls sub 1 and sub 2 (using F8) I do not get the error...... so do I need make the VBA wait for the distiller to finish? I am very confused.<o></o>
<o></o>
I hope this makes sense to someone out there.<o></o>
Any help would be much appreciated!!!!<o></o>
<o> </o>
The full pdf function is code is:<o></o>
Public Function fPDFWorkbook(ReportsToPDF As Range) As Boolean
'For this sub to work the reference to 'Acrobat Distiller' in Tools --> References... needs to be selected.
Dim wbtoPDF As Workbook
Dim sCurrentPrinter As String
Dim PDFapplication As PdfDistiller '.Application
Dim X As Range
Dim FilePathName As String
Dim FileName As String
Set PDFapplication = New PdfDistiller
Dim BaseFileName As String
Dim PSFileName As String
Dim PDFFileName As String
Dim LogFileName As String
'Save the currently active printer
sCurrentPrinter = Application.ActivePrinter
For Each X In ReportsToPDF
Select Case X.Column
Case 4
FilePathName = X.Offset(0, 1)
FileName = X.Offset(0, 2)
Case 3
FilePathName = X.Offset(0, 2)
FileName = X.Offset(0, 3)
Case 2
FilePathName = X.Offset(0, 3)
FileName = X.Offset(0, 4)
End Select
BaseFileName = FilePathName & Left(FileName, Len(FileName) - 4)
PSFileName = BaseFileName & ".ps"
PDFFileName = BaseFileName & ".pdf"
LogFileName = BaseFileName & ".log"
UpdateProgress ("Opening " & FileName & " so that it can be converted to PDF...")
Set wbtoPDF = Excel.Workbooks.Open(FilePathName & FileName, ReadOnly:=True)
UpdateProgress ("Converting " & FileName & " to a Post Script File...")
'Print the workbook to the postscript file .ps
wbtoPDF.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, prtofilename:=PSFileName
'Colse the workbook
wbtoPDF.Close savechanges:=False
UpdateProgress ("Converting " & Left(FileName, Len(FileName) - 4) & ".ps file to a pdf file...")
PDFapplication.FileToPDF PSFileName, PDFFileName, ""
UpdateProgress ("Deleting the .ps and .log files...")
Kill PSFileName
Kill LogFileName
Next X
Application.ActivePrinter = sCurrentPrinter
Set PDFapplication = Nothing
fPDFWorkbook = True
End Function
Cheers Alex