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

VB Code for saving PDF Print File
 

You don't... Cute PDF is poping up the Save As request. Excel has nothing to
do with it. The only effective way to avoid that is to use a PDF writer that
has an API that you can access. There is an example here...

http://www.excelguru.ca/node/21
--
HTH...

Jim Thomlinson


"dallin" wrote:

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