View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
dallin dallin is offline
external usenet poster
 
Posts: 7
Default VB Code for saving PDF Print File

The following is my code to print individual worksheets to .pdf file. How do
I change the following code to use file name as coded instead of the SAVE AS
box popping up? Thanks.

Sheets(Array("KAW Financial Summary", "Total Impact", _
"Gas Impact", "OIL Impact", "NGL Impact", "CO2 Impact")).Select
Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

Dim myRange As Range
Set myRange = Worksheets("_").Range("a1")
stryearmo = Application.WorksheetFunction.Text(myRange, "yyyy-mm")
strdiryr = Application.WorksheetFunction.Text(myRange, "yyyy")
strmo = Application.WorksheetFunction.Text(myRange, "mm")

strfullfile = "U:\QEP\Revenue\Revenue_Estimates\Closing-" & strdiryr &
"\" & strdiryr & "_" & strmo & "\" & stryearmo & "EstimateImpact.pdf"
If Dir(strfullfile, vbNormal) < "" Then
Kill strfullfile
End If

ActiveWorkbook.SaveAs Filename:=strfullfile, _
CreateBackup:=False