Print to .pdf file using VBA

[ArfArfArf]

Hi

I'm trying to write VBA code take the Print_Area of a spreadsheet and print it to a pdf file, and I'm having some trouble with this.

There are two ways I'm considering (which may not be the best ways)

1. Highlight the print_area then press print, and check print to file. The issue here is that I can only appear to save it as 'Filename.xps' not .pdf (I assume I'd need to have some 3rd Party pdf addon to make this work?
Also I'm getting no clues for the code as recording the basic steps doesn't show much after I stop recording. So, if this is the best way to do it, what am I missing re addon, and what would the code look like please?

2. The second way is use of range.printout. According to Help the syntax is

expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

However, there is no example of syntax in the Help menu, and it keeps returning an error code that isn't helping me find the solution...so..
- can anyone give me some example code that allows me to create a pdf file. I assume I don't have to specify a printer given I'm printing to file, but if I do, be aware I don't actually have one, so only printer option is XPS Document Viewer (not that this creates pdf...)

Thanks for reading this.

I have found a temporary solution where I copy the print area to a newly opened excel workbook then save this as a NewFile.pdf, then close the temp excel sheet without saving. However, it doesn't seem very elegant, and I'm worried about the processing time when I start running reports (the idea of all this is to create files to email to club members, and we're about 120 people)

ArfArfArf

 

[Warship]

Have you looked at ExportAsFixedFormat?

        ActiveWorksheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=FilenameStr, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                From:=2, _
                to:=150, _
                OpenAfterPublish:=False

 

[ArfArfArf]

No I hadn't....

Problem solved. Thank you so much Warship!

 

[StreetMike]

Finally! I've looked everywhere for a one-step solution like this and it works, the first time, like a charm. Thanks so much!!