Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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. Formula: -------------------- 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
How about
prtofilename:="S:\SR\QFS\" & JName assuming JName is a variable. Jeff "jlejehan" wrote in message ... 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. Formula: -------------------- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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 -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=547403 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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 "jlejehan" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Filename when creating PDf from Excel Workbook
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a named range gives an error only when the filename is lo | Excel Discussion (Misc queries) | |||
vba, get filename of this workbook | Excel Discussion (Misc queries) | |||
Creating a Word filename shortcut in Excel | Excel Discussion (Misc queries) | |||
activate a workbook without using its filename? | Excel Programming | |||
My workbook filename | Excel Programming |