Print to PDF VBA

[jhm0054]

Hi All,

I'm trying to create a macro to allow the print to PDF to have a shortcut key and save the excel sheet to the same folder and the workbook as the title of a particular cell in the active sheet. The current formula i am using is:
Sub save_excel_as_PDF()
With ActiveSheet.PageSetup
CenterHeader = "Sample Excel File Saved As PDF"
Orientation = xlPortrait
PrintArea = "$A$1:$F$40"
Zoom = False
FitToPagesTall = False
FitToPagesWide = 1
End With
ActiveSheet.ExportAsFixedFormat
Type:=xltypePDF
File:=activeworkbook.path"/"&activeSheet.range("B18").value,
Quality:=xlqualitystandard
ignoreprintarea:=false
From:=1,
To:=2,
Openafetrpublish:=true

End Sub

the final 8 lines of the code are causing me some issue as I continually get a compile error, expected: expression appears with the := highlighted

Can someone please check my code or provide a solution for this?

Cheer

J

 

[juddaaaa]

Firstly, inside a With block you need to prefix properties and methods with a "."

Secondly, if you're extending onto more than one line you need to use " _" at the end of each line

Sub save_excel_as_PDF()
  With ActiveSheet.PageSetup
    .CenterHeader = "Sample Excel File Saved As PDF"
    .Orientation = xlPortrait
    .PrintArea = "$A$1:$F$40"
    .Zoom = False
    .FitToPagesTall = False
    .FitToPagesWide = 1
  End With

  ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B18").Value, _
    Quality:=xlQualityStandard, _
    IgnorePrintArea:=False, _
    From:=1, _
    To:=2, _
    OpenAfterPublish:=True
End Sub

 

[jhm0054]

Thanks, Juddaaaa,

The code works, however, when I run the code I recieve a run-time error'13' type mismatch.

What dose this mean?

 

[juddaaaa]

There's one mistake I've spotted.

It's IgnorePrintAreas rather than IgnorePrintArea

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B18").Value, _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=2, _
    OpenAfterPublish:=True

but that wouldn't cause a Type Mismatch. That would be a Named Parameter error.

The only thing I can see that could cause a Type Mismatch is whatever is in cell B18.

Which line is the Debugger highlighting?

 

[jhm0054]

There's one mistake I've spotted.

It's IgnorePrintAreas rather than IgnorePrintArea

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B18").Value, _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=2, _
    OpenAfterPublish:=True

but that wouldn't cause a Type Mismatch. That would be a Named Parameter error.

The only thing I can see that could cause a Type Mismatch is whatever is in cell B18.

Which line is the Debugger highlighting?

There's one mistake I've spotted.

It's IgnorePrintAreas rather than IgnorePrintArea

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B18").Value, _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=2, _
    OpenAfterPublish:=True

but that wouldn't cause a Type Mismatch. That would be a Named Parameter error.

The only thing I can see that could cause a Type Mismatch is whatever is in cell B18.

Which line is the Debugger highlighting?

I was hoping the excel would save the PDF in the same location as the workbook and name the PDF the same as the title in B18.

is that what the Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B18").Value, _ indicates?

 

[jolivanes]

Use code tags around your code please.
Here is another possibility.

Sub Save_As_Pdf()
Dim PDF As String, a As String
With ActiveSheet
    .PageSetup.PrintArea = "A1:F40"
    a = .Range("B18").Value
End With
    PDF = ThisWorkbook.Path & "\" & a & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
End Sub

Change references where required.

 

[jhm0054]

Thanks Guys for the help.

I'm having another issue now. I have recorded a shortcut key for the macro in the excel template however if I rename/move the file the macro no longer works. Is there a way to get the shortcut keys to work on the active sheet without having to record the shortcut again?