Excel 2007 VBA save as PDF 1

[dmuroff]

I'm looking to find the VBA code required to save as PDF in Excel 2007 using the Microsoft add-in.

I've been able to acheive this in Access like this:

DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, Directory

Does Excel 2007 have a File format simliar to "acFormatPDF"?

I've tried this:

ActiveWorkbook.SaveAs "Report", xlFormatPDF

but it does not work (Runtime error 1004)

Any help would be greatly appreciated. Thanking you in advance

Dan

 

[PHV]

Have a look at the ExportAsFixedFormat method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmuroff]

ExportAsFixedFormat does indeed work!

ActiveWorkbook.ExportAsFixedFormat xlTypePDF, directory

One more thing: Is it possible to use with on just the selected worksheets? i.e. something like this:

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
Sheets("Sheet1").Activate
selection.ExportAsFixedFormat xlTypePDF, directory

The output of this code is a blank pdf

 

[PHV]

I'd create a temporary workbook ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmuroff]

That did the trick!

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
    Sheets("Sheet1").Activate
   Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Copy
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
     Sheets("Sheet1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
   DisplayAlerts = False
    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, directory
    ActiveWorkbook.Close False

Thanks for the help PHV!