How to publish to PDF in Excel VB macro?

Question

I have a worksheet with a macro that generates a web page (code is below). I would like to change this so it generates a PDF instead. Can this be done?

Sub publishwebpages()

'calculate how many iterations
    x = 0
    Sheets("webpagegen").Select
    Range("n1").Select
    Range("n1").Copy
    noofpages = ActiveCell.Value


'step through and select each customer

    For x = 0 To noofpages
    'For x = 364 To 366

    Sheets("listcust").Select
    Range("A2").Select
    ActiveCell.Offset(x, 0).Range("A1").Select
    Selection.Copy
    Sheets("webpagegen").Select
    Range("l1").Select
    ActiveSheet.Paste



'name folder and filename
    Sheets("webpagegen").Select
    Range("ac2").Select
    Range("ac2").Copy
    foldername = ActiveCell.Value


'publish pages
    Range("d3:q80").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWorkbook.PublishObjects.Add(xlSourceRange, "D:\Temp\" & foldername, "webpagegen", "$d$3:$q$80", xlHtmlStatic, "custweb08 current_8162", "").Publish (True)


    Next x

End Sub

Answer 1

If you are using Excel 2010>, it allows you to save your workbook/excel sheet as PDF as built in functionality, we used to have report specifically run in Excel just because of that :)

You can try to record while it trying to "Save As..." as PDF and it might look like this:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "c:\Book1.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

Note: I have tried it on Excel 2010, it might look different in other versions.