Specifying Filename when creating PDf from Excel Workbook

[jlejehan]

Hi,

I've had a look through a number of the threads regarding excel and pdf-ing but I can't seem to find the answer I'm looking for.

I have a number of workbooks that open in sequence and I'd like to save each one as a pdf using a cutepdf printer. I'd like to save each one with the filename specified by refering to a particular cell

I'm using (so far) the following code:

My problems occur where I try and use prtofilename:="S:\SR\QFS\ JName i.e. I'm hoping that the file is saved as the name in the active cell JName that changes everytime the loop occurs without having the 'Save As' prompt coming up

- at the moment, though, a file is created named JName (not, say, David, as the activecell value may specify) and I cannot open the file as it is not saved into pdf form

To be honest I'm not sure how to alter this. I've seen a lot of discussions of Pdf distiller etc, but is there a way to solve this using cutepdf?

Many thanks for any help in advance

thanks

Joe.

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 JName As String 
 
                     
                     
                     
Workbooks.Open ("S:\SR\QFS\FCW\Names") 
 
Range("A1").Select 
 
Do 
 
            JName = ActiveCell.Value 
                                 
            fName = Dir("S:\SR\QF\" & JName & ".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").Select 
            ActiveWorkbook.Save 
 
     
            ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="CutePDF Printer", printtofile:=True, collate:=True, prtofilename:="S:\SR\QFS\ JName" 
             
     
     
 
            
 
             
Workbooks("Names").Activate 
ActiveCell.Offset(1, 0).Select 
Loop Until ActiveCell = "" 
             
 
End Sub  

[Jeff Standen]

How about

prtofilename:="S:\SR\QFS\" & JName

assuming JName is a variable.

Jeff

"jlejehan" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I've had a look through a number of the threads regarding excel and
> pdf-ing but I can't seem to find the answer I'm looking for.
>
> I have a number of workbooks that open in sequence and I'd like to save
> each one as a pdf using a cutepdf printer. I'd like to save each one
> with the filename specified by refering to a particular cell
>
> I'm using (so far) the following code:
>
> My problems occur where I try and use prtofilename:="S:\SR\QFS\ JName
> i.e. I'm hoping that the file is saved as the name in the active cell
> JName that changes everytime the loop occurs without having the 'Save
> As' prompt coming up
>
> - at the moment, though, a file is created named JName (not, say,
> David, as the activecell value may specify) and I cannot open the file
> as it is not saved into pdf form
>
> To be honest I'm not sure how to alter this. I've seen a lot of
> discussions of Pdf distiller etc, but is there a way to solve this
> using cutepdf?
>
> Many thanks for any help in advance
>
> thanks
>
> Joe.
>
>
>
>
> PHP code:
> --------------------
> 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 JName As String
>
>
>
>
> Workbooks.Open ("S:\SR\QFS\FCW\Names")
>
> Range("A1").Select
>
> Do
>
> JName = ActiveCell.Value
>
> fName = Dir("S:\SR\QF\" & JName & ".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").Select
> ActiveWorkbook.Save
>
>
> ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False,
> ActivePrinter:="CutePDF Printer", printtofile:=True, collate:=True,
> prtofilename:="S:\SR\QFS\ JName"
>
>
>
>
>
>
>
> Workbooks("Names").Activate
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell = ""
>
>
> End Sub
> --------------------
>
>
> --
> jlejehan
> ------------------------------------------------------------------------
> jlejehan's Profile:
> http://www.excelforum.com/member.php...o&userid=33950
> View this thread: http://www.excelforum.com/showthread...hreadid=547403
>

[davesexcel]

I have tried on many occasions to do what you are asking, and have never been successful, the closest I could get was to have PDF save as the workbook name at the press of a button, I could never get it to use a cell range.

So, I discovered that if I want to save to PDF automaically, the workbook would have to be saved each time before I printed, I just haven't gotten around to working on it much and figured the user can keep entering the file name until I found a way for this to work.....

The PDF that you have could have it's own users forum, maybe they have a solution there

[jlejehan]

Jeff, Dave

Many thanks for your answers

Jeff -the variable one worked well to save the name, thanks - a bit of a stupid error on my behalf!

However, now a file saves but it is not in pdf form even though when I go through the same process manually and go through the "Save As" dialog box it saves as a pdf. The resultant file I get can't be opened even though the name is now right

Any ideas would be gratefully received

thanks

Joe

[Jeff Standen]

Have you tried the macro recorder? It may not work as cutepdf is external to
Excel but it may add methods that allow you to do it - I don't know how the
program works.

Jeff

"jlejehan" <[email protected]> wrote in
message news:[email protected]...
>
> Jeff, Dave
>
> Many thanks for your answers
>
> Jeff -the variable one worked well to save the name, thanks - a bit of
> a stupid error on my behalf!
>
> However, now a file saves but it is not in pdf form even though when I
> go through the same process manually and go through the "Save As"
> dialog box it saves as a pdf. The resultant file I get can't be opened
> even though the name is now right
>
> Any ideas would be gratefully received
>
> thanks
>
> Joe
>
>
> --
> jlejehan
> ------------------------------------------------------------------------
> jlejehan's Profile:
> http://www.excelforum.com/member.php...o&userid=33950
> View this thread: http://www.excelforum.com/showthread...hreadid=547403
>

[William]

Hi jlejehan

I posted the following about 10 days ago in answer to a similar question. It
may help.


Sub PDFTest()
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, i As Integer
x = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4)
PSFileName = x & ".ps"
Set myPDF = New PdfDistiller
For i = 1 To ExecuteExcel4Macro("Get.Document(50)")
PDFFileName = x & " (Sheet" & i & ").pdf"
ActiveSheet.PrintOut From:=i, To:=i, prtofilename:=PSFileName
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill Left(PDFFileName, Len(PDFFileName) - 3) & "log"
Kill (PSFileName)
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

--

Regards

William

XL2003

[email protected]


"jlejehan" <[email protected]> wrote in
message news:[email protected]...
>
> Jeff, Dave
>
> Many thanks for your answers
>
> Jeff -the variable one worked well to save the name, thanks - a bit of
> a stupid error on my behalf!
>
> However, now a file saves but it is not in pdf form even though when I
> go through the same process manually and go through the "Save As"
> dialog box it saves as a pdf. The resultant file I get can't be opened
> even though the name is now right
>
> Any ideas would be gratefully received
>
> thanks
>
> Joe
>
>
> --
> jlejehan
> ------------------------------------------------------------------------
> jlejehan's Profile:
> http://www.excelforum.com/member.php...o&userid=33950
> View this thread: http://www.excelforum.com/showthread...hreadid=547403
>

[jlejehan]

William,

Thanks - sadly I don't have access to the pdfdistiller, so I'm having to use cutepdf instead and try and find code that works on this.

I hope I'm almost there but the file created just doesn't open in acrobat reader - it's not recognised.

I assume the reason for using the distiller in the first place is to rectify the problems associated with moving straight from traditional excel format to a pdf, so there may be someway of doing this using the cutepdf printer - but I still can't figure this out personally.

if anyone has any further suggestions I'd be grateful?

thanks

[KR]

jlejehan-

in case you are still monitoring this thread for responses; it sounds like
you are simply using the Excel "save as" function and adding .pdf on the end
of the filename. This won't work, because your pdf creation software (adobe
or cutepdf) actually has to make a new file based on what is being sent to
the printer driver by Excel. If you'd like a simple (but inelegant)
solution, print the document and let it save the file with your current
filename. Then in VBA code, pause for 5-10 seconds to give the pdf filemaker
time to finish making the file, then use VBA to simply rename that file to
your desired name using the name from your worksheet. You can also move the
file with the same code, if cutepdf is dumping the .pdf file in a directory
other than where you want the file. I had code that did this with Adobe
Acrobat 4 (or was it 5?) but I don't have access to that code anymore, so
you'll have to check the Excel helpfile, try to come up with some sample
code, then post back again if you need more help.

best of luck,
Keith

"jlejehan" <[email protected]> wrote in
message news:[email protected]...
>
> William,
>
> Thanks - sadly I don't have access to the pdfdistiller, so I'm having
> to use cutepdf instead and try and find code that works on this.
>
> I hope I'm almost there but the file created just doesn't open in
> acrobat reader - it's not recognised.
>
> I assume the reason for using the distiller in the first place is to
> rectify the problems associated with moving straight from traditional
> excel format to a pdf, so there may be someway of doing this using the
> cutepdf printer - but I still can't figure this out personally.
>
> if anyone has any further suggestions I'd be grateful?
>
> thanks
>
>
> --
> jlejehan
> ------------------------------------------------------------------------
> jlejehan's Profile:
http://www.excelforum.com/member.php...o&userid=33950
> View this thread: http://www.excelforum.com/showthread...hreadid=547403
>