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.




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

 

[Jeff Standen]

How about

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

assuming JName is a variable.

Jeff

 

[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

 

[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

(e-mail address removed)

 

[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