VBA code and Cutepdf Printer

jlejehan · Jun 1st 2006

Hi,

I'm trying to save excel files as pdf using vba code and the cutepdf printer. If I do this manually it works, but when I write code it saves the output to a file that cannot be read or opened

Unfortunately I don't have access to the pdfdistiller so can't use this technique which I've seen described in a number of forums.

The code I've used is below. Any help would be gratefully received.

thanks

Joe

PHP

Sub pdfing()


Dim cntTrue As Long, cnt As Long
Dim rng As Range, bk As Workbook
Dim fName As String
Dim WB As Workbook
Dim FundName As String


                    
                    
                    
Workbooks.Open ("S:\SR\QFS\FCW\Names")


Range("A1").Select


Do


            FundName = ActiveCell.Value
                                
            fName = Dir("S:\SR\QFS\" & FundName & ".xls")
            Do While fName <> ""
            Workbooks.Open ("S:\SR\QFS\" & fName), UpdateLinks:=0
            cnt = cnt + 1
            fName = Dir()
            Application.ScreenUpdating = False
            Loop
            
            
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
            


            Range("A3:X3").Select
            ActiveWorkbook.Save


    
            ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="CutePDF Printer", printtofile:=True, collate:=True, prtofilename:="S:\SR\QFS\" & FundName & ".pdf"
            
    
    


           


            
Workbooks("Names").Activate
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
            


End Sub

Display More

**norie** · Jun 1st 2006

Re: VBA code and Cutepdf Printer

Are you sure you are setting the print to filename correctly?

EDIT

Actually I've found the problem, when you use PrintToFile it creates a PostScript file, not a PDF file.

To see what I mean try opening the file in a text editor.

jlejehan · Jun 2nd 2006

Re: VBA code and Cutepdf Printer

Norie,

Thanks for the below - Any idea how to tackle chaging it from the postscript file to an openable pdf?

any help gratefully received

thanks, joe.

A9192Shark · Jun 2nd 2006

Re: VBA code and Cutepdf Printer

To automate cutePDF you need to purchase the developers version...

ahem...

Now to semi-automate it you can either print to a postscript file and then use a converter (I found this link http://www.cs.wisc.edu/~ghost/doc/cvs/Ps2pdf.htmfrom a Google search).

or you can set cutePDF as your default printer
Open your workbook
Print the file
Accept the default name
Use VBA code to rename the file
Loop back to the next printout.

Admittedly this means sitting there pressing return to accept the default name, but it does work.

HTH,

Alan.

jlejehan · Jun 2nd 2006

Re: VBA code and Cutepdf Printer

Alan,

Thanks - I think you're right, though, sitting and tapping enter may be the most straight forward solution if slightly more longwinded.

I'll take a look at those online converters as well.

VBA code and Cutepdf Printer

Participate now!

Share