VBA Macro to auto save PDF files (CutePDF)

[klutzz]

Hi, I am trying to automate my PDF print outs by using the record macro function.
However, the recording function is not able to capture my file name and path where I need to save the new PDFs.

The current codes are as per below, can anyone help to advise how I can automate the whole process?

Sub Macro1()
Selection.AutoFilter Field:=2, Criteria1:="=China", Operator:=xlOr, _
Criteria2:="=Hong Kong"
Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
End Sub

 

[klutzz]

bumpz

 

[Macropod]

Is there a reason you're not using Excel's built-in PDF-creation functions?

 

[klutzz]

thats because this function doesn't work for me.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True


I keep getting "object doesn't support this property or method"

 

[Macropod]

Perhaps, then, you should establish that whatever you have in A1 is actually valid for use as a filename.
There is nothing fundamentally wrong with the code, though it would be better to use something like:

With ActiveSheet
  .ExportAsFixedFormat Type:=xlTypePDF, Filename:=.Range("A1").Value, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
End With

 

[klutzz]

Thanks Macropod, I managed to find something that works after enabling my Acrobat Distiller.

The links are below for all who requires the same codes.

With Acrobat Distiller Enabled
http://www.mrexcel.com/forum/excel-questions/193347-save-excel-sheet-pdf-file-solved-2.html

With CutePDF - read Tigam's post (I have yet to try the codes, but it looks good)
http://www.mrexcel.com/forum/excel-...w-create-excel-pdf-macro-auto-filename-4.html

 

[Macropod]

The .ExportAsFixedFormat method works without the need for any third-party app, as it uses Excel's built-in functionality.

FWIW, the code I posted worked just fine in my testing. If it's not working for you, you might try repairing the Office installation (via Start > Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change>Repair).