Excel sheets to PDF Without PDF Printer

[PWD]

Good afternoon, I have this that works in Excel 2010 for a single worksheet.

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        direc_output & fname & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Is there any way to do this with more than one sheet?

I've tried

ActiveWorkbook.Sheets(Array("Report_FIRST_PAGE", "Report")).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        direc_output & fname & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

but get

Run-time error '438':

Object doesn't support this property or method

I found some other code for use with pdf printer

ObjXL.ActiveWorkbook.Sheets(Array("sheet1", "sheet2",
"sheet3")).PrintOut , collate:=True

but that doesn't appear to help me much

I have found this on the Microsoft site:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF FileName:="sales.pdf" Quality:=xlQualityStandard DisplayFileAfterPublish:=True

But this seems to do just what I need!!

direc_output = "G:\Users\Des.Lavender\Excel\"
fname = "Test2"

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        direc_output & fname & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

All I had to do was tweak the margins & set the Print Area! Simples.

Thank you for listening.



Many thanks,
D€$

 

[Andrzejek]

How about simple:

Option Explicit

Sub Macro1()
Dim iSheet As Integer

For iSheet = 1 To Sheets.Count
    Sheets(iSheet).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\TEMP\" & Sheets(iSheet).Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
Next iSheet
        
End Sub

Have fun.

---- Andy

 

[PWD]

Hi Andy I need both sheets in one pdf.

Many thanks,
D€$

 

[zelgar]

What you need to do is use the ARRAY when selecting the sheets:

Sub Macro1()
'
' Macro1 Macro
'
    Sheets(Array("Sheet1", "Sheet2")).Select
    Sheets("Sheet1").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\TEMP\" & Sheets(iSheet).Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

Expland the ARRAY("Sheet1", "Sheet2", ...) to get all of your sheets you want exported to the PDF

 

[Andrzejek]

Nice, zelgar
and you don't even have to have: [tt]Sheets("Sheet1").Activate[/tt]
Works like a dream...

Have fun.

---- Andy

 

[PWD]

Thanks both, I have to extract both these sheets from another workbook and save them separately into a new workbook anyway but I shall take something away from this input.

Many thanks,
D€$