Please help!!!

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
I have a line of code to get my Excel workbook into a PDF and it works fine, BUT how do I specify the filename for the PDF? Currently it just prompts me to enter the file name. How can I enter this in the code?

ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne05:", Collate:=True
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Assuming Excel 2007
Sub CreatePDFRates1()
Dim FileNameStr As String
FileNameStr = "Filepath\" & "Assumpt" & ".pdf"
shtAssum2.Range("A1:AC51").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FileNameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
OpenAfterPublish:=False
End Sub
 
Upvote 0
If I remember correctly, there are a few add-ins that you need to make sure are marked. Goggling Excel 2003 to PDF might be helpful.

Using 2003

Sub gotoadobe()
Dim pdfDist As New ACRODISTXLib.PdfDistiller
' Set fs = CreateObject("Scripting.fileSystemObject")
psfilename = "v:\acmp\contract summaries\MY6352_EA.ps"
PDFFilename = "v:\acmp\contract summaries\MYGS II\ea version\" & _
Format(Range("inpDate").Value, "mm-dd-yy") & "\" & "IM-006352_EA_" & Format(Range("inpDate").Value, "mm-dd-yy") & ".PDF"
Sheets(Array("IM6352P1", "IM6352P2", "IM6352P3", "IM6352P4", "IM6352P5")).Select
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True, prtofilename:=psfilename
pdfDist.FileToPDF psfilename, PDFFilename, ""


psfilename = "v:\acmp\contract summaries\MY6354_EA.ps"
PDFFilename = "v:\acmp\contract summaries\MYGS II\ea version\" & _
Format(Range("inpDate").Value, "mm-dd-yy") & "\" & "IM-006354_EA_" & Format(Range("inpDate").Value, "mm-dd-yy") & ".PDF"
Sheets(Array("IM6354P1", "IM6354P2", "IM6354P3", "IM6354P4", "IM6354P5")).Select
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True, prtofilename:=psfilename
pdfDist.FileToPDF psfilename, PDFFilename, ""

' fs.deletefile = "v:\acmp\contract summaries\*.ps"


End Sub
 
Upvote 0
Wow! Thanks for your reply! But there can't be this much involved to just export it to a PDF, could there? There's no simple syntax to specify the filename?
 
Upvote 0
This may not be the most efficient, but it does work (it has been used multiple times. However since moving to 2007, I haven't taken the time to streamline it. If someone lets me know what is excess, I'll trim it out.
 
Upvote 0
Looking at my code this created two separate PDF - one before the spaces and one after

This does only creates one PDF

Sub gotoadobe()
Dim pdfDist As New ACRODISTXLib.PdfDistiller
' Set fs = CreateObject("Scripting.fileSystemObject")
psfilename = "v:\acmp\contract summaries\MY6352_EA.ps"
PDFFilename = "v:\acmp\contract summaries\MYGS II\ea version\" & _
Format(Range("inpDate").Value, "mm-dd-yy") & "\" & "IM-006352_EA_" & Format(Range("inpDate").Value, "mm-dd-yy") & ".PDF"
Sheets(Array("IM6352P1", "IM6352P2", "IM6352P3", "IM6352P4", "IM6352P5")).Select
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True, prtofilename:=psfilename
pdfDist.FileToPDF psfilename, PDFFilename, ""


End Sub
 
Upvote 0
Ok, at first I thought it was a ton of code, but then realized you were doing the same thing twice, for two different files. I don't understand the reasoning for the PS file and the PDF file, but I see that it is necessary for it to work. And I had to use the Kill command to delete the PS file as well as the LOG file.

Here is what I ended up with:

Sub gotoadobe()
Dim pdfDist As New ACRODISTXLib.PdfDistiller
PDFFileName = "C:\MyFile"
ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne05:", Collate:=True, prtofilename:=PDFFileName & ".ps"
pdfDist.FileToPDF PDFFileName & ".ps", PDFFileName & ".PDF", ""
Kill PDFFileName & ".ps"
Kill PDFFileName & ".LOG"
End Sub


THANK YOU VERY MUCH FOR YOUR HELP!!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top