Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Pdf Run-time Error 462:

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Ok I have a bug in my code and it is driving me crazy. Please help me!

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) .

So for example:

Sub 1 will run and at the end of the process call the pdfing function then exits the sub

Sub 2 will run and at the end of the process call the pdfing function then exits the sub.

Sub 1 and Sub 2 work perfectly when they are run separately!

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:

Run-time error '462':
The remote server machine does not exist or is unavailable.

This occurs on the following line:

PDFapplication.FileToPDF PSFileName, PDFFileName, ""

I have found a few things in relation to this error including:
http://support.microsoft.com/kb/178510

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.

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.

I hope this makes sense to someone out there.

Any help would be much appreciated!!!!

The full pdf function is code is:

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


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Output the File Path to and Name of a Workbook in Excel - UDF
- Free Excel UDF (user defined function) that displays the full file path and name of an Excel workbook. This function is
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

Similar Topics







I have used a code as shown below to create PDF file from excel. The
code doesn't work on the version of Excel 2003. Error message comes up
at the line 'Set myPDF = New PdfDistiller' with 'Run-time error
'-2147024893' Automation error The system cannot find the path
specified'. VB can identify PdfDistiller as an object. Can somebody
help me here please?

Dim PSFileName As String
Dim PDFFileName As String
Dim LogFileName As String
PSFileName = archive_file_path & "\a " & datestring & ".ps"
PDFFileName = archive_file_path & "\a " & datestring & ".pdf"
LogFileName = archive_file_path & "\a " & datestring & ".log"


space_for_graph = space_for_graph - offset_space + const_space


' Generate postscript file from excel file
Range("A1:P" & space_for_graph).PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prtofilename:=PSFileName


' convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill (PSFileName)


Derek


We have a message box that pops up when we want to print and select whether we want to print to the printer or to Acrobat Distiller. It works when printing to a printer, but if you want it to print to pdf, you have to have Distiller selected as the default printer. However it is now bombing out on this first line. Is there anything in this that looks strange that could cause it to bomb?


Function Print_To_PDF1(MyFrom As Integer, MyTo As Integer, PDF_Print As Boolean, PSFileName As String)
'
'
Dim MyCurrentDir As String
Dim MyPSFileName As String


Dim MyCurrectDir As String, PDFFileName As String

MyCurrentDir = "C:\Temp\Benefit Data Individuals\"

PDFFileName = MyCurrentDir + PSFileName + ".pdf"
MyPSFileName = MyCurrentDir + PSFileName + ".ps"

If Dir(MyCurrentDir + PSFileName + ".ps") "" Then
MsgBox "File Already Exist! " + PSFileName + ".ps"
Exit Function
End If

If PDF_Print = True Then 'print to PDF if TRUE
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True, _
collate:=True, prtofilename:=MyPSFileName
End If

'convert postscript file to PDF
Dim MyPDF As PdfDistiller
Set MyPDF = New PdfDistiller
MyPDF.FileToPDF MyPSFileName, PDFFileName, ""

Set MyPDF = Nothing

End Function


hey--

I am using this code to try to print an excel worksheet to a pdf file

Public Sub Distill_it()

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\tmp\myPostScript.ps"
PDFFileName = "c:\tmp\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.Range("A1:E4").PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, collate:=True, prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

I am getting and error that highlights the 3rd to last line and says: user-defined type not defined. Why am I getting this message and how do I fix the code to make this work?


Hi.
I'm trying to use VBA to automatically generate PDFs directly from an Excel worksheet.
I found some code online, which is shown he

Code:

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "C:\Specs Generator\PDF Creation Tests\myPostScript.ps"
PDFFileName = "C:\Specs Generator\PDF Creation Tests\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""


But this doesn't seem to work.
The code creates a postscript file, but instead of the associated PDF file I get a .log file with an error message in it:

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

I'm using Excel 2003 with Adobe Acrobat Professional 8 with Distiller.
BTW, in the VBE 'Tools' menu --> 'References' list, I have made sure that 'Acrobat Distiller' is ticked.

Any ideas?
Thanks for your help.


We have a message box that pops up when we want to print and select whether we want to print to the printer or to Acrobat Distiller. It used to work to print to pdf just fine (provided you had previously selected Distiller selected as the default printer), but within the last week, we started getting the above VB Compile error when we select print to Distiller.

When you click debug after getting this VB error message, the row that it goes right to is the row
Code:

Dim MyPDF As PdfDistiller


but when you scroll up, the one with the yellow highlight and the arrow that it bombs out on is the first row he


Code:

Function Print_To_PDF1(MyFrom As Integer, MyTo As Integer, PDF_Print As Boolean, PSFileName As String)
'
'
Dim MyCurrentDir As String
Dim MyPSFileName As String


Dim MyCurrectDir As String, PDFFileName As String

MyCurrentDir = "C:\Temp\Benefit Data Individuals\"

PDFFileName = MyCurrentDir + PSFileName + ".pdf"
MyPSFileName = MyCurrentDir + PSFileName + ".ps"

If Dir(MyCurrentDir + PSFileName + ".ps") <> "" Then
MsgBox "File Already Exist! " + PSFileName + ".ps"
Exit Function
End If

If PDF_Print = True Then 'print to PDF if TRUE
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True, _
collate:=True, prtofilename:=MyPSFileName
End If

'convert postscript file to PDF
Dim MyPDF As PdfDistiller
Set MyPDF = New PdfDistiller
MyPDF.FileToPDF MyPSFileName, PDFFileName, ""

Set MyPDF = Nothing

End Function



When I asked online what might be wrong with the code, I was told

Code:

Dim MyPDF As PdfDistiller


PdfDistiller I don't believe is a system-defined type.

As this is the row it first takes you to when you click debug, I am inclined to agree with the person, but I don't know how to "define" it or change the code to make it work.
Also have no clue why it used to work and now it doesn't. Makes me think that some new VB/Excel changes have come out that is now caused the code to be "defunct?"

I also had a suggestion from someone that said

If you want to keep the code as it is you'll need a reference to the PDFDistiller DLL Library, Tools>References...

Or you could try late-binding using CreateObject to create and reference an instance of Distiller.

But I have no clue how to do either. Again, any help would be greatly appreciated.


I didn't write the script and know very little about VB, but when we try to print to PDF within a huge excel program, the VB breaks when selecting printing to PDF. I get an error message on the Dim MyPDF As PdfDistiller line near the bottom of the code below. When I click OK, the first line in the code then highlights. Is the language "old" where the defining the As Integer, no longer needed?

Any thoughts on this would be most appreciated.

Function Print_To_PDF1(MyFrom As Integer, MyTo As Integer, PDF_Print As Boolean, PSFileName As String)
'
'
Dim MyCurrentDir As String
Dim MyPSFileName As String


Dim MyCurrectDir As String, PDFFileName As String

MyCurrentDir = "C:\Temp\Benefit Data Individuals\"

PDFFileName = MyCurrentDir + PSFileName + ".pdf"
MyPSFileName = MyCurrentDir + PSFileName + ".ps"

If Dir(MyCurrentDir + PSFileName + ".ps") "" Then
MsgBox "File Already Exist! " + PSFileName + ".ps"
Exit Function
End If

If PDF_Print = True Then 'print to PDF if TRUE
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True, _
collate:=True, prtofilename:=MyPSFileName
End If

'convert postscript file to PDF
Dim MyPDF As PdfDistiller
Set MyPDF = New PdfDistiller
MyPDF.FileToPDF MyPSFileName, PDFFileName, ""

Set MyPDF = Nothing

End Function


Hi demigod's

Please have a look at this code and especially the highlighted area.



Sub Create_and_SendPDF_Booking_Confirmation()
Dim strPrinter As String
Dim PSFileName As String
Dim PDFFileName As String
strPrinter = Application.ActivePrinter

ThisFile = Range("N16").Value

PSFileName = "c:\PDF Files\Sample.ps"
PDFFileName = "c:\PDF Files\Statement of Account.pdf"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=PSFileName

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller

myPDF.FileToPDF PSFileName, PDFFileName, ""
Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
Set Item = App.CreateItemFromTemplate ("C:\Users\Stuart\AppData\Roaming\Microsoft\Templates\excel.oft")
With Itm
.Subject = "Statement of Account"
.To = Range("N9")
.Body = "Dear " & vbCrLf & vbCrLf

.Attachments.Add "C:\PDF Files\Statement of Account.pdf"
.Attachments(1).Position = Len(.Body)

.Display
End With

Application.ActivePrinter = strPrinter
ActiveWorkbook.SaveAs Filename:="A:\AGL Server\Statements\" & ThisFile & ".xlsm", FileFormat:=52
End Sub


I need a bit of code that recognises the system user, so it changes the "stuart" to whoever on the highlighted line.

Any help, muchly appreciated.

Kind Regards
manc


Dear Demigod's

I have the following code that runs as a macro and converts current sheet to .pdf for me.

What do I need to add to it, so that if sheet2 has a value in A1, it will convert sheet1 and sheet2 to combined .pdf, in order of sheet1, then sheet2.

If sheet2 A1 has no value, it just converts Sheet1

Thanks for your help regarding this.
Regards
manc

Code:

Sub Create_and_SendPDF_Booking_Confirmation()
    Dim strPrinter As String
    Dim PSFileName As String
    Dim PDFFileName As String
    Dim ThisFile As String
 
    strPrinter = Application.ActivePrinter
    ThisFile = Range("K36").Value
 
    PSFileName = "c:\PDF Files\Sample.ps"
    PDFFileName = "c:\PDF Files\Statement of Account.pdf"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
    printtofile:=True, Collate:=True, prtofilename:=PSFileName
 
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
 
    myPDF.FileToPDF PSFileName, PDFFileName, ""
    Set App = CreateObject("Outlook.Application")
    Set Itm = App.CreateItem(0)
 
    With Itm
        .Subject = "Statement of Account"
        .To = Range("T21")
        .Body = "Dear " & vbCrLf & vbCrLf
 
        .Attachments.Add "c:\PDF Files\Statement of Account.pdf"
        .Attachments(1).Position = Len(.Body)
 
        .Display
    End With
 
    Application.ActivePrinter = strPrinter
    ActiveWorkbook.SaveAs Filename:="\\Server-pc\c\AGL Server\Statements\" & ThisFile & ".xlsm", FileFormat:=52
End Sub





Hi Guys,
I have below macro which does the below

Open the files on the computer
Select the sheet to print as pdf
then print it
save the file
close it
next file

but it crashes after 2-3 loops

the error message shows run time error -2147417848 (80010108)
i dont no what it is but it generally crashes when it try to close the open file.

Please help to resolve


Sub PrintLgraphs()

response = MsgBox("Are you sure you want to patch all BU files", vbYesNo)
If response = vbYes Then
GoTo Start
ElseIf response = vbNo Then
GoTo Finish
End If

Start:
application.ScreenUpdating = False
Windows("KPIPatch.xls").Activate
Sheets("Patch").Select
CountRow = 15
Check = Range("A" & CountRow).Value
path = Range("I15").Value
Template = Range("TemplateName")
Filename = Check & Template

Do Until Check = ""

Filename = Check & Template
If ActiveSheet.Range("C" & CountRow) = True Then

application.DisplayAlerts = False
application.EnableEvents = False
Workbooks.Open Filename:=path & Filename
application.DisplayAlerts = True
application.EnableEvents = True


Windows(Filename).Activate

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

Period = Range("Select_month")
Workbook = ActiveWorkbook.Name

Sheets(Array("Graphs")).Select

'Define the path and filenames (can get the names from a cell, and add the path & extension):
PSFileName = "\\eur-age-srv\teams\CoE\Reporting\Reporting_Activities\Group\KPI\PDF\Curr_Graphs\" & Workbook & "_" & "Graphs" & ".PS"
PDFFileName = "\\eur-age-srv\teams\CoE\Reporting\Reporting_Activities\Group\KPI\PDF\Curr_Graphs\" & Workbook & "_" & "Graphs" & ".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 Copies:=1, ActivePrinter:="PDF995", 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 7.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."

Sheets("instructions").Select


Windows(Filename).Activate

ActiveWorkbook.Save
application.DisplayAlerts = False
application.EnableEvents = False
ActiveWindow.Close


application.DisplayAlerts = True
application.EnableEvents = True



Windows("KPIPatch.xls").Activate
Sheets("Patch").Select
CountRow = CountRow + 1
Check = Range("A" & CountRow).Value

Else
CountRow = CountRow + 1
Check = Range("A" & CountRow).Value
End If

Loop
Finish:
Windows("KPIPatch.xls").Activate
Sheets("Patch").Select
application.ScreenUpdating = False
End Sub


Good morning Demigods

I have the follwoing code that save the current worksheet to .pdf in a directory.


Sub Print_PDF()

Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\PDF Files\Sample.ps"
PDFFileName = "c:\PDF Files\Booking Confirmation.pdf"
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub


All I need to happen now, is when the user clicks a button, the above code runs and then the email window opens and it attaches itself to an email address located in cell A1.

I've read the guides, namely Ron de Bruin's, and whilst interesting, it is unfortunately way above my level of a) inteligence, b) knowledge and c) awesomeness.

If someone can help appreciation will be granted.

Regards
manc


Hi Demigods

I have a macro that creates a .pdf of the active sheet and adds it as an attachment to an email. I would like to change the font and size of the email message via the same macro, so the whole process is automated

I want the font to be "Calibri" and size 11

What lines of code can I add?

Code is pasted below...

Your help, as always, is appreciated.

Regards
manc


Sub Create_and_SendPDF()
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\PDF Files\Sample.ps"
PDFFileName = "c:\PDF Files\Booking Confirmation.pdf"
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm

.Subject = "Booking Confirmation: " & Range("D20")
.To = Range("AB17")
.Body = "Dear " & Range("AB18") & vbCrLf & vbCrLf
.Body = .Body & Range("E24") & " " & Range("K24") & vbCrLf
.Body = .Body & Range("E25") & " " & Range("K25") & vbCrLf & vbCrLf
.Body = .Body & "Please find attached your booking confirmation." & vbCrLf & vbCrLf

.Attachments.Add "C:\PDF Files\Booking Confirmation.pdf"
.Attachments(1).Position = Len(.Body)
.Body = .Body & vbCrLf & vbCrLf & vbCrLf
.Display

End With

End Sub


Dear Demigod's

Thanks to you fine specimens of human, my sheet is currently working well...except for the following

It's basically an invoice with twenty available rows available for "invoice details" in the middle. The rest of the rows on the sheet are essentially the design of the invoice.

We've now found that in a couple of instances more than twenty rows are needed for the "invoice details".

How can I keep the formating of the "invoice", so that if "invoice details" is full, it automatically creates a new page, with same format, changing the print area to accomodate two pages etc, etc...

There is VBA code attached to the sheet which is quoted below. I'm not sure how this code would be affected by the requests I have made.

You supreme knowledge is, as always, muchly appreciated, and you will be added to the christmas card list (christmas card fund pending)

Best Regards
manc


PHP Code:

Sub Create_and_SendPDF_Booking_Confirmation()
    Dim strPrinter As String
    Dim PSFileName As String
    Dim PDFFileName As String
    Dim ThisFile As String
 
    strPrinter = Application.ActivePrinter
    ThisFile = Range("K36").Value
 
    PSFileName = "c:\PDF Files\Sample.ps"
    PDFFileName = "c:\PDF Files\Statement of Account.pdf"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
    printtofile:=True, Collate:=True, prtofilename:=PSFileName
 
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
 
    myPDF.FileToPDF PSFileName, PDFFileName, ""
    Set App = CreateObject("Outlook.Application")
    Set Itm = App.CreateItem(0)
 
    With Itm
        .Subject = "Statement of Account"
        .To = Range("T21")
        .Body = "Dear " & vbCrLf & vbCrLf
 
        .Attachments.Add "c:\PDF Files\Statement of Account.pdf"
        .Attachments(1).Position = Len(.Body)
 
        .Display
    End With
 
    Application.ActivePrinter = strPrinter
    ActiveWorkbook.SaveAs Filename:="\\Server-pc\c\AGL Server\Statements\" & ThisFile & ".xlsm", FileFormat:=52
End Sub
Public Sub SaveTemplate()
    Dim ThisFile As String
    ThisFile = Range("K26").Value
 
    Application.GoTo Worksheets("Sheet1").Range("A1")
    ActiveWorkbook.SaveAs "Server-pccAGL ServerStatement", FileFormat:=53
End Sub
Public Sub SaveJob()
    Dim ThisFile As String
 
    ThisFile = Range("K36").Value
 
    Application.GoTo Worksheets("Sheet1").Range("A1")
    ActiveWorkbook.SaveAs Filename:="Server-pccAGL ServerStatements" & ThisFile & ".xlsm", FileFormat:=52
 
End Sub 







We have a very strange issue with the use of SendKeys to print to pdf. It seems to work only with excel 2000. We have variables to give the name of the file we want (name of workbook & sheet), but when we try to use the same code in a 2003 version of excel, the dialog box needs to be filled in manually - not very efficient when you have 2000 sheets to print. I have read in many places that the use of SendKeys is not recommended but I have yet to find a different way to print to PDF using what we already have - Acrobat 7.0. Thanks in advance

HERE IS THE CODE

Sub printToPdf()

Dim x As Integer
Dim i As Integer
Dim j As Integer
Dim sheetName As String
Dim bookName As String
Dim compAbbr As String
Dim numReports As Integer
Dim WordObj As Word.Application
Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant
Dim keepOpen

Workbooks("pointBook.xls").Activate
Sheets("specSheet").Select
numReports = Range("b10").Value
compAbbr = Range("b8").Value

For x = 1 To 10

Workbooks("BookName.xls").Activate
Sheets("SheetName").Select
Cells(1, 132 + x).Activate

'determine if section was generated
If x > 1 Then
Sheets("specSheet").Select
If ActiveSheet.CheckBoxes("Check Box " & x - 1).Value = xlOff Then
GoTo 500
End If
End If

Sheets("SheetName").Select
keepOpen = 1
bookName = ""

For i = 1 To numReports

keepOpen = 1
Workbooks("BookName.xls").Activate
Sheets("SheetName").Select
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Value = bookName Then
keepOpen = 2
GoTo 300
End If
If i > 1 Then
Workbooks(bookName).Save
Workbooks(bookName).Close
End If

bookName = ActiveCell.Value

300 If bookName = "" Then
GoTo 200
End If

sheetName = Cells(ActiveCell.Row, 2).Value

'Define the path and filenames
PSFileName = "c:\folderName\" & compAbbr & "\Reports\pdfParts\" & sheetName & "_" & bookName & ".PS"
PDFFileName = "c:\folderName\" & compAbbr & "\Reports\pdfParts\" & sheetName & "_" & bookName & ".PDF"
ActiveCell.Offset(0, 12).Value = PDFFileName

'open workbook
If keepOpen = 2 Then
GoTo 400
End If
Workbooks.Open "c:\folderName\" & compAbbr & "\Reports\excelParts\" & bookName

400 Workbooks(bookName).Activate
Sheets(sheetName).Select


SendKeys PSFileName & "{ENTER}", False
ActiveSheet.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"


200 Next i

Workbooks(bookName).Save
Workbooks(bookName).Close




500 Next x



End Sub


Hi... New here. Apologies for the long quote of code, but not sure of the etiquette.

I am working with an Excel model that I inherited. (In a large networked environment, using Excel 2003, IT support have looked at the problem and said "not really our area".)

I have a page with a dropdown menu of clients (for a specific centre. Based on the value of the dropdown menu, this populates an Excel spreadsheet with lots of summary statistics about the client and a graph. The designer included a macro which was supposed to run all of the possible outputs (one for every client on the list) and create them as PDFs (or possibly as one long PDF). I have amended other parts of the model but not changed the client page.

When I run the macro, it thinks for a long time, then returns runtime error 429 - "ActiveX component can't create object". When I run the debugger, it highlights the line in red below.

Any suggestions, given that I am limited in what I can change in my PC settings? Any parts of the code that look wrong? Or any suggestions for a better solution to do the same thing? Any help would be hugely appreciated as it would save me running over 3000 PDFs manually.
Code:

---------------------------------------------------------------------------------------------------
Sub PDFClientPages()
Dim S, P, Clients, MaxCentres As Integer
Dim originalprinter, myFileName, PSFileName, PDFFileName, LogFileName, FilePath As String
Dim PrintSheets As Sheets
Dim myPDF As New ACRODISTXLib.PdfDistiller6
    Dim oPDF, oTemp As Acrobat.CAcroPDDoc
    Dim File1, File2, MainFileName As String
    Dim nPagesTo, nPagesFrom As Long

originalprinter = Application.ActivePrinter
FilePath = Application.ActiveWorkbook.Path & "\"
PSFileName = FilePath & "PSFile.ps"

MinCentres = 63
MaxCentres = 63 'Range("Centres").Value
    
Application.ScreenUpdating = False
    
For S = MinCentres To MaxCentres
    Range("ID").Value = S
    
    Sheets("Auto").Calculate
    
    myFileName = Range("Centre").Value & " Client PSF Results " & Range("Year").Value
    If Dir(FilePath & myFileName & ".pdf", vbNormal) <> "" Then GoTo 20
    
    Sheets("Centre").Calculate
    Sheets("ChartData").Calculate
    ClientList
    
        Set PrintSheets = ActiveWorkbook.Sheets(Array("ClientList"))
        myFileName = "PSFClientList" & S
        LogFileName = FilePath & myFileName & ".log"
        PDFFileName = FilePath & myFileName & ".pdf"
        PrintSheets.PrintOut Copies:=1, _
                    preview:=False, ActivePrinter:="Adobe PDF", _
                    printtofile:=True, Collate:=True, _
                    prtofilename:=PSFileName
        myPDF.FileToPDF PSFileName, PDFFileName, ""
        Kill LogFileName
        Kill PSFileName

        Clients = Range("Clients")
        For P = 1 To Clients
            Range("Client").Value = P
            Sheets("ChartData").Calculate
            Sheets("Auto").Calculate
            Sheets("Client").Calculate
            Set PrintSheets = ActiveWorkbook.Sheets(Array("Client"))
            myFileName = "PSFClientOutput" & P
            LogFileName = FilePath & myFileName & ".log"
            PDFFileName = FilePath & myFileName & ".pdf"
            PrintSheets.PrintOut Copies:=1, _
                        preview:=False, ActivePrinter:="Adobe PDF", _
                        printtofile:=True, Collate:=True, _
                        prtofilename:=PSFileName
          [BOLD] myPDF.FileToPDF PSFileName, PDFFileName, "" [/BOLD]
            Kill LogFileName
            Kill PSFileName
        Next P

    Set oPDF = CreateObject("AcroExch.PDDoc")
    Set oTemp = CreateObject("AcroExch.PDDoc")
    
    MainFileName = Range("Centre").Value & " Client PSF Results " & Range("Year").Value
    
    File1 = FilePath & "PSFClientList" & S & ".pdf"
    oPDF.Open (File1)
    nPagesTo = oPDF.GetNumPages
     
    For P = 1 To Clients
        File2 = FilePath & "PSFClientOutput" & P & ".pdf"
        Set oTemp = CreateObject("AcroExch.PDDoc")
        oTemp.Open (File2)
        nPagesFrom = oTemp.GetNumPages
        If Not oPDF.InsertPages(nPagesTo - 1, oTemp, 0, nPagesFrom, True) Then MsgBox ("Unable to add " & File2)
        nPagesTo = oPDF.GetNumPages
    Next P
        
    If Not oPDF.Save(PDSaveFull, FilePath & MainFileName & ".pdf") Then MsgBox ("Unable to save " & MainFileName)
    Set oPDF = Nothing
    Set oTemp = Nothing

20 'Line 20

Next S

End Sub





Hi

I am trying to output to a pdf from Excel.
I found the code below from a search of previous topics

I understand it works in two parts:
- creates a postscript file
- uses distiller to create a pdf

The macro is running without errors and it is creating the postscript file
But it doesn't seem to be creating the PDF.

I was wondering if the files need to be a particular place for the FiletoPDF to work (the ps file is currently being generated in My Documents.

I am running Acrobat and Distiller v7.0 and Excel 2003 and I have installed the Acrobat libraries in Tools-->References

Any assistance would be greatly appreciated

Here is the code

Sub Printt()

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

tempPDFRawFileName = "test"

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

Dim mypdfDist As New PdfDistiller

mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""

Kill tempPSFileName
Kill tempLogFileName
End Sub


Using an Excel macro, I want to create a pdf using Docudesk's deskPDF software. The installed ps interpreter (distiller) is Ghostscript. Additionally, I want to use code to define the ps and pdf file names, and the respective folder paths.

I have a working macro that does all of this, using Adobe PDF and Acrobat Distiller. My users have deskPDF only, so I'm looking for similar code for deskPDF. Alternatively, does anyone know the code to do this using Shell or SendKeys commands. My working code for Adobe PDF is as follows.

Sub makeMyPDF()
'##########################################
'# The Acrobat Distiller reference library must be enabled. #
'# In the Excel VB Editor window, select Tools > References #
'# and insure that Acrobat Distiller is checked. #
'##########################################
Dim pdfPrinter As String 'Adobe pdf printer
Dim ps_interpreter As PdfDistiller 'Acrobat distiller

Set ps_interpreter = New PdfDistiller

Application.Dialogs(xlDialogPrinterSetup).Show 'Prompt user to select PDF printer
pdfPrinter = Application.ActivePrinter 'Capture user's PDF printer

ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:=pdfPrinter, _
printtofile:=True, Collate:=True, PrToFilename:="D:\Junk\myPDFtest.ps"

ps_interpreter.FileToPDF "D:\Junk\myPDFtest.ps", "D:\Junk\myPDFtest.pdf", ""

Kill "D:\Junk\myPDFtest.ps" 'Delete the ps file
Kill "D:\Junk\myPDFtest.log" 'Delete the log file

End Sub


I am very much a newbie on VBA macros. I had one we obtained from Mr. Excel and used to print an Excel worksheet to PDF. But over time I have moved from Acrobat 6 to Acrobat 8 professional and from Excel 2002 to Excel 2003. Oh, to make things more fun, I control the printing by running all of this from SAS (my true love). Now when I try run the same programming, this VBA macro fails at Dim mypdfDist As New PdfDistiller and says there is no project or library available. I have made sure that the VBE Tools|References| includes the appropriate references. I do recall in Acrobat 6 that it created a printer icon, in addition to Adobe PDF called Distiller something, perhaps even PdfDistiller. In acrobat 8 there is no such printer available. Based on my best guess, I'm assuming the creation of the PDF file is a 2 step process, step 1 creating the .ps file and step 2 converting .ps to .pdf. Not sure if the two step process is unnecessary in Acrobat 8. I can open up Acrobat distiller but it's a separate program, and not a printer. I'm missing something here, or the code change is simple. Any thoughts?
Thanks.

Code:

 
Sub LegisreportPDF()
'STEP1: create one set of filenames
Dim temPDFfilename As String
Dim temPSfilename As String
Dim temlogfilename As String
temPSfilename = Worksheets("Sheet1").Range("D1") & ".ps"
temPDFfilename = Worksheets("Sheet1").Range("D1") & ".pdf"
temlogfilename = Worksheets("Sheet1").Range("D1") & ".log"
 
Sheets("Card").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF on Ne02:", _
printtoFile:=True, Collate:=True, Prtofilename:=temPSfilename
Dim mypdfDist As New PdfDistiller
mypdfDist.FileToPDF temPSfilename, temPDFfilename, ""
Kill temPSfilename
Kill temlogfilename
'Code came from http://www.mrexcel.com/board2//viewt...c901810ebceabd
End Sub





I have Adobe Distiller 8.0. Using Excel '07.

The following code runs fine, but the pdf output that occurs is not a pdf but rather a log file stating:

%%[ Error: undefined; OffendingCommand: E*t600R&u600D*r0F&l1o&l2A&l7H&l0S&l1X&l8c1E*p0x0Y*c0t7632x5880Y*b0M ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%

There is lots on the internet about the error, but I can't find a solution. I need to use distiller rather than Excels PDF solution, because the PDF solution from microsoft messess up the format of the charts where distiller does not. Any help would be greatly appreciated. Thanks!

Code:

Sub InFlightUpdate()
    Dim I As Integer
    Dim wbJIRA As Workbook
    Dim wbResults As Workbook
    Dim wbInflight As Workbook
    Dim wbInflightLoc As String
    Dim InProcessFile As String
    Dim wbInflightLoc2 As String
    Dim PDFStartLoc As String
    Dim PDFEndLoc As String
    Dim PDFSheet As Worksheet
    Dim oPDF As PdfDistiller
    Dim TmpPSFile As String
 
    InProcessFile = "H:\Inprocess.xlsx"
 
    I = 2
    Do Until IsEmpty(Sheet2.Cells(I, 1))
'Action if effort is active, but not in test
        If Sheet2.Cells(I, 11).Value = 1 Then
            wbInflightLoc = Sheet2.Cells(I, 2).Value
            wbInflightLoc2 = Sheet2.Cells(I, 8).Value
            PDFStartLoc = Sheet2.Cells(I, 9).Value
            PDFEndLoc = Sheet2.Cells(I, 10).Value
            Name PDFStartLoc As PDFEndLoc
            Set wbInflight = Application.Workbooks.Open(wbInflightLoc)
                wbInflight.Sheets(2).Cells(75, 2).Value = Sheet2.Cells(I, 6).Value
                Kill InProcessFile
                wbInflight.SaveAs (InProcessFile)
                wbInflight.Close True
                Kill wbInflightLoc2
            Set wbInflight = Application.Workbooks.Open(InProcessFile)
                Set PDFSheet = ActiveSheet
                Set oPDF = New PdfDistiller
                TmpPSFile = "H:\TmpPSFile.ps"
                PDFSheet.PrintOut copies:=1, preview:=False, _
                ActivePrinter:="Acrobat Distiller", printtofile:=True, _
                collate:=True, PrToFileName:=TmpPSFile
                oPDF.FileToPDF TmpPSFile, PDFStartLoc, ""
                Kill TmpPSFile
                wbInflight.SaveAs (wbInflightLoc2)
                wbInflight.Close False
        Else
            'do nothing
        End If
'Action if effort is active, and in test
        If Sheet2.Cells(I, 11).Value = 2 Then
            'code to come
        End If
 
        I = I + 1
        Set wbJIRA = Nothing
        Set wbResults = Nothing
        Set wbInflight = Nothing
    Loop
End Sub





I am trying to write VBA code to automitically print to an ADOBE file.
I am somewhat familiar with VBA, but most of my experience is with Active Controls and User Menus and I have VERY limited experience with Adobe. I am saying this, because I have searched for examples online, and cannot get any of them to work. Probably because I am not properly defining the post script files etc...
One example:

Private Sub CommandButton1_Click()
Dim oSheet As Worksheet
Dim oPDF As PdfDistiller ( I get an error here)
Dim TmpPSFile As String
Dim PDFFile As String
Set oSheet = ActiveSheet
Set oPDF = New PdfDistiller
TmpPSFile = "c:\TmpPSFile.ps"
PDFFile = "c:\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & ActiveSheet.Name & ".pdf"
oSheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, _
collate:=True, PrToFileName:=TmpPSFile
oPDF.FileToPDF TmpPSFile, PDFFile, ""
Kill TmpPSFile
End Sub

I am using Adobe Acrobat/Distiller 8. Notes say to "Do not send fonts to Distiller" Option, but I cannot find that option within Distiller.

I am just looking for something very simple. I print about 20-25 reports out of my macro enabled worksheets a day - and just have to convert so supervisors can open with their IPADS.
Thank You,
Todd


Hi.
I use this code to export an exel sheet to text (.txt) file:

Code:

Sub TestTextFile()
    ActiveSheet.Unprotect Password:="secret"
    'Remove Empty Cells
    Sheets("Torrent Template").Select
    Application.ScreenUpdating = False
    With ActiveSheet.UsedRange
        Set c = .Find("", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.ClearContents
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address  firstAddress
        End If
    End With
    Application.ScreenUpdating = True
    'End of Remove empty Cells
    '
    'Start of actual export
    'Public Sub CharacterSV()
    Const DELIMITER As String = ""
    Dim myRecord As Range
    Dim myField As Range
    Dim sOut As String
    Dim newOutputFile As String
    
    newOutputFile = Next_Sequential_Filename("C:\Users\Default\Desktop\Techinfo.txt")

    Open newOutputFile For Output As #1
    For Each myRecord In Range("A1:A" & _
        Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Range(.Cells, _
                Cells(.Row, Columns.Count).End(xlToLeft))
                
                sOut = sOut & DELIMITER & myField.Text
            Next myField
            Print #1, Mid(sOut, 1)
            sOut = Empty
        End With
    Next myRecord
    Close #1
    'End Sub
    'end of export
    MsgBox "File exported to: " & newOutputFile, vbOKOnly
    ActiveSheet.Protect Password:="secret"
End Sub

Private Function Next_Sequential_Filename(baseFilename As String)

    Dim p As Integer
    Dim ext As String
    Dim n As Integer
    
    p = InStrRev(baseFilename, ".")
    ext = Mid(baseFilename, p)
    baseFilename = Left(baseFilename, p - 1)
    
    n = 0
    Do
        Next_Sequential_Filename = baseFilename & IIf(n = 0, "", "_" & n) & ext
        n = n + 1
    Loop Until Dir(Next_Sequential_Filename) = ""
    
End Function


Now, I want to ask:

1) How can I take part of the filename of the excel file and add it with the txt file.

Example: supposed that the excel filename is "Example.xlsx" and the text is "techinfo.txt". I want the txt exported file to have the name: "Example_techinfo.txt" . Keep in mind that, cause I use the code in a template, I will change the excel filename but the txt filename will be every time the same.

It is possible to add some code to do it?

2) Also, what have to do if I want to export, every time, in different directory the txt file?

Thanks in advance





I have successfully used the code detailed below to convert from excel to PDF from my PC but when the macro is used on another machine it halts at the line "Dim myPDFDist As New PdfDistiller" saying printer not found.

Could anyone suggest a solution that could be run on any PC, if they are using a different PDF printer to Adobe. Id it that PDF distiller needs to be installed on the other PC?

Many thanks in advance
Martin



Sub Create_PDF()

'Created by Dom Hill with considerable asistance from Biggoan and Mr Excel

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim tempshowWindow As String
Dim SaveLocation As String


SaveLocation = Sheets("data page").range("H49")
Sheets("P&L Output").Activate

tempPDFRawFileName = SaveLocation & safeFileName(range("a1").Value)

'Define the postscript and .pdf file names.

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

' Print the Excel range to the postscript file

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

'Create PDF File

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempshowWindow

'Delete PS File

Kill tempPSFileName
Kill tempLogFileName

End Sub


Hey All, glad to post my first thread.

Part of the scope of my project is to print all visible worksheets to pdf. Simple right? I've seen several threads along the way and tried my best to emulate what they did. I have it working however as I step through the sub I notice that the first iteration of ps to pdf conversion the distiller initializes and then onJobStart runs and then conversion complete and then OnJobDone runs. However on the following iterations OnJobStart does not run and the code will get hung up in a loop for DoEvents.

The workaround which I am not satisfied with is if I run the distiller with the bShowWindow as TRUE and then after conversion I do a send keys to close the Distiller application window. This allows all iterations to successfully convert to pdf. This workaround is no good if the user starts clicking around on other windows or let's say some new window automatically becomes active then I may send keys to close that window and thus pdf conversion stops and I may have lost information on the other application activewindow. Please advise, I hope I have given enough detail.

VB:

Sub PrintVisible_PS() 
    Dim objSheet As Object 
    Dim filename As String 
    Dim Job_num As String 
    Dim PauseTime As Integer 
    Dim svInputPS, svOutputPDF, svJobOptions As String 
    Dim save_location As String 
    Application.Volatile 
     'If nothing is selected give a message to user
    If Is_Selected = False Then 
        MsgBox "Error: Please Make a Selection for Export and Try Again." 
        Exit Sub 
    End If 
    save_location = GetFolder 'call getfolder which prompts user for location path to save exported files
    If save_location = "" Then ' USER HIT CANCEL when prompted to choose destination folder
        MsgBox "Error: Please Try Again and Select a Correct Destination Folder" 
        Exit Sub 
    End If 
    MsgBox "Warning: During execution of PDF Creation DO NOT click or press anything. If you do an error may occur" 'part of workaround
    Job_num = Sheets("Inputs_Menu").Range("Job_number").Value 
    Application.Cursor = xlWait 'HourGlass, part of workaround
    Application.Interactive = False ' disable mouse, part of workaround
    For Each objSheet In Sheets 
        Application.DisplayAlerts = False 
        If objSheet.Visible = True Then 
            On Error Resume Next 
            filename = objSheet.Name 
            Sheets(filename).Select 
            svInputPS = save_location & Job_num & "_" & filename & ".ps" 
            svOutputPDF = save_location & Job_num & "_" & filename & ".pdf" 
            ActiveSheet.PrintOut printtofile:=True, prtofilename:=svInputPS 
             
             ' Convert the postscript file to .pdf
            Dim myPDF As cAcroDist 
            Set myPDF = New cAcroDist 
            myPDF.odist.bShowWindow = True 
             
            Call myPDF.odist.FileToPDF(svInputPS, svOutputPDF, "") 
            For PauseTime = 1 To 10000 'loop to kill some time, NOTE I GOT RID OF WHILE LOOP TO BECAUSE IT WOULD HANG UP
                DoEvents 
            Next PauseTime 
            myPDF.odist.CancelJob 
            SendKeys "%{F4}", True ' Send ALT+F4 to close distiller
            blnFinished = True 
            On Error Goto errorhandler 
        End If 
        Application.DisplayAlerts = True 
         
    Next objSheet 
    Application.Interactive = True ' enable mouse
    Application.Cursor = xlDefault 'Back to normal
    Sheets("Inputs_Menu").Select 
    MsgBox "Exported PostScript File Location: " & save_location 
    Exit Sub 
errorhandler: 
    MsgBox "ERROR: PLEASE TRY AGAIN AND DO NOT CLICK ANYTHING WHILE PROGRAM IS RUNNING" 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



GET FOLDER
VB:

Function GetFolder(Optional sTitle As String = "Select Folder", _ 
    Optional sInitialFilename As String) 
    Dim myFolder As String 
    With Application.FileDialog(msoFileDialogFolderPicker) 
        If sInitialFilename = "" Then sInitialFilename = ThisWorkbook.Path 
        .InitialFileName = sInitialFilename 
        .Title = "Select your Export File Location" 
        If .Show = -1 Then 
            GetFolder = .SelectedItems(1) 
            If Right(GetFolder, 1) <> "\" Then 
                GetFolder = GetFolder & "\" 
            End If 
        Else: GetFolder = "" 
        End If 
    End With 
End Function 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



cAcroDist Class Module
VB:

Public WithEvents odist As PdfDistiller 
Public blnFinished As Boolean 
Dim StartTime As Date 
Private Sub Class_Initialize() 
    Set odist = New PdfDistiller 
End Sub 
Private Sub odist_OnJobDone(ByVal strInputPostScript As String, ByVal strOutputPDF As String) 
    blnFinished = True 
    Kill strInputPostScript 
End Sub 
Private Sub odist_OnJobFail(ByVal strInputPostScript As String, ByVal strOutputPDF As String) 
    blnFinished = True 
End Sub 
Private Sub odist_OnJobStart(ByVal strInputPostScript As String, ByVal strOutputPDF As String) 
    StartTime = Now() 
    blnFinished = False 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Hey all -

I am trying to write a macro that will print a worksheet to PDF and automaically name the file. This is the code I have so far:

Option Explicit

Private Sub PrintToPDF_Click()

Dim CurrentPrinter As String
Dim fileName As String

Let fileName = Cells(7, "e")

'Sets the sheet to print out in black and white
ActiveSheet.PageSetup.BlackAndWhite = True

CurrentPrinter = Application.ActivePrinter ' save the currently active printer
On Error Resume Next ' ignore errors
Application.ActivePrinter = "Adobe PDF on Ne05:" ' change to PDF printer
SendKeys fileName & "{ENTER}" ', False
ActiveSheet.PrintOut ' print the active sheet (Network Funding)
Application.ActivePrinter = CurrentPrinter ' change back to the original printer
On Error GoTo 0 ' resume normal error handling
End Sub

The problem is the sendkeys function does nothing and I still have to manually input the file name. Can anyone please help?!?!

Thanks -
Cara


I'm having a problem writing a VBA function. I want to call a function that looks something like this... GetLink("CCF-2000",K18) where the CCF-2000 is a project folder on my computer and K18 references a code related to the city. Using K18 and the Case function I'll differentiate between different file paths related to different cities.

Anyways, I got the Case function to work properly, but when I try to run the Excel Hyperlink function in VBA it returns #VALUE. I want it to return something simliar to =HYPERLINK("G:\CCF-2000", "CCF-2000"), but using a function to figure out the path based on the city code.

The K column only contains data such as SG, SP, LE, ENW, etc... that specify a city. Each city has it's own path on the local computer.

Here's the function I was trying to build to accomplish this. I wanted to call this function in another column... AC, to return a hyperlink, but to no avail.

Quote:

Function GetLink(Filename as String, City as String) as String

Select Case City Case "ENW", "ENE", "ESW", "ESE"
GetLink = Application.WorksheetFunctions.Hyperlink("G:\" & Filename, Filename)
Case "SG"
GetLink = Application.WorksheetFunctions.Hyperlink("G:\SG\" & Filename, Filename)
Case Else
GetLink = "Escape"
End Select

Hopefully, there's something in there that makes sense. It's my first VBA function, so hopefully that's not too far from the truth.


I'm using the following code to attempt to add a workbook. It was functioning in this exact form but now just fails to add the new workbook. No error message, no nothing.

Code:

Dim FilePathName As String
Dim Element As String
Dim savebook As Workbook
Dim newbook As Workbook
Dim currentbook As Workbook
Application.ScreenUpdating = False
Savings
Set currentbook = ActiveWorkbook
Workbooks.Add.Activate
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename("MyFileName.xls", _
        "Excel files,*.xls", 1, "Select your folder and filename")
Element = ActiveWorkbook.Name


has me baffled!

Any help appreciated!