Using Acrobat PDFMaker Add in from VBA 1

[Moebius01]

First off, for the record, I am not a vba coder. I've taught myself enough over the years to be dangerous, but since I only need to script things once in a blue moon, have never had any official instruction.

That said, I'm trying to automate saving an Excel file as PDF using the Acrobat PDFMaker Office Com Add In. This is Excel 2007 with Acrobat 9.2 Standard installed. From Excel, I can click the office button and select save as - PDF with no troubles.

From what I've been researching online, the call for this should be:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Test\Test.pdf"

But pretty much any variation I try on this results a run-time of either Object Required or Invalid Procedure Call or Argument. Am I missing something obvious in this? My main script is in a template that graphs data into a report. The script copies to a new workbook, modifies the charts to contain data only instead of links to the original, then saves. The main code is below. I've also tried this as a simple macro from a test file with the same error.

Application.DisplayAlerts = False
    Dim Fname As String
    Dim FPath As String
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook

    FPath = "C:\Reports"
    Fname = "Comparative Ratings - " & Sheets("Data").Range("D2").Text & " " & Sheets("Data").Range("C2").Text
    
    Set Sourcewb = ActiveWorkbook
    Sheets.Copy
    Set Destwb = ActiveWorkbook
    
    With Destwb
        .Sheets("Report").Select
        .Sheets("Report").Rows("2:4").Copy
        .Sheets("Report").Rows("2:4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
                For Each Cht In Destwb.Sheets("Report").ChartObjects
                Cht.Chart.ChartTitle.Characters.Text = Cht.Chart.ChartTitle.Characters.Text
                For Each oSeries In Cht.Chart.SeriesCollection
                    With oSeries
                        .Name = .Name
                        .Values = .Values
                        .XValues = .XValues
                    End With
                Next
            Next
        .Sheets("Data").Delete
        .Sheets("Report").Range("A6").Select
        .SaveAs Filename:=FPath & "\" & Fname, FileFormat:=56
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=FPath & "\" & Fname, OpenAfterPublish:= False
        .Close
    End With
            
'Turn alerts back on
Application.DisplayAlerts = True
            
End Sub

 

[SkipVought]

Hi,

Note
An error will occur if the PDF add-in is not currently installed.

Visual Basic for Applications
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF FileName:=“sales.pdf” Quality:=xlQualityStandard DisplayFileAfterPublish:=True

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Moebius01]

But if I see Adobe PDF as an option under Save As, and it works, wouldn't that imply the add-in is currently installed?

 

[Moebius01]

Ok, apparently it doesn't mean it's installed (or at least installed right). Re-installing the add-in seems to be working now. Thanks!

 

[SkipVought]

This is weird. I have never saved a workbook as a pdf.

I have no pdf add-in either in Excel or VBA.

I executed this code and it exported a pdf and opened it...

Sub test()
    ActiveWorkbook.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\tstPDF.pdf", _
        OpenAfterPublish:=True

End Sub

go figger!?

please add this code to yours...

        .SaveAs Filename:=FPath & "\" & Fname, FileFormat:=56
[b]
      Debug.Print FPath & "\" & Fname
[/b]
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=FPath & "\" & Fname, OpenAfterPublish:= False

and please post the result from the Immediate Window.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HughLerwill]

I seem to remember that saving to a pdf is not an out of the box feature in Excel 2007. You have to download the feature. Once downloaded/installed it is listed in the SaveAs options; is it there?

 

[Moebius01]

The Save As option was there and working, but apparently the piece that would work with the VBA call wasn't, or wasn't working right. Once I re-installed the add-in, it started working right.