CODEWITHSUNDEEP
vba
Eric K.
Eric K.

Reputation: 131

How to Save Excel Worksheet as PDF with Command Button?

I am getting a 'Syntax Error' with the coding beneath the Next R line toward the bottom below. I am trying to save the open worksheet as a PDF file referencing the order number found in cell D3. Thanks for your help!

Private Sub CommandButton1_Click()
Dim OrderDate As String, PONumber As String, Vendor As String, ShipTo As String, SKU As String, Path As String, filename As String
Dim R As Long, LastSKURow As Long, NextDBRow As Long, OFrm As Worksheet, DB As Worksheet
Set OFrm = Worksheets("Order Form 1")
Set DB = Worksheets("Database")
OrderDate = OFrm.Range("B3")
PONumber = OFrm.Range("D3")
Vendor = OFrm.Range("B7")
ShipTo = OFrm.Range("D7")
LastSKURow = OFrm.Cells(OFrm.Rows.Count, "F").End(xlUp).Row
For R = 3 To LastSKURow
    SKU = OFrm.Range("F" & R).Value
    NextDBRow = DB.Cells(DB.Rows.Count, "A").End(xlUp).Row + 1
    DB.Range("A" & NextDBRow).Value = OrderDate
    DB.Range("B" & NextDBRow).Value = PONumber
    DB.Range("C" & NextDBRow).Value = Vendor
    DB.Range("D" & NextDBRow).Value = ShipTo
    DB.Range("E" & NextDBRow).Value = SKU
Next R
Application.ScreenUpdating = False
OFrm.ExportAsFixedFormat Type:=xlTypePDF, _
        Path ="C:\PDF\"
        filename = OFrm.Range("D3")
        OpenAfterPublish = False
OFRrm.SaveAs filename:=Path & filename & ".pdf", FileFormat:=xlTypePDF
End Sub

Upvotes: 0

Views: 2187

Answers (2)

tlemaster
tlemaster

Reputation: 859

The SaveAs line is redundant. You just need to fix the ExportAsFixedFormat to work properly. When using named parameters, you need to use := instead of just =. I recommend creating a new Sub for the PDF save.

Sub SaveOrderAsPDF()
    Dim PDFName As String
    PDFName = Worksheets("Order Form 1").Range("D3").Value
    ChDir "C:\PDF"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\PDF\" & PDFName, Quality:=xlQualityStandard, IncludeDocProperties:= _
    True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

With that working, just add SaveOrderAsPDF to your existing sub.

Upvotes: 1

Pat Jones
Pat Jones

Reputation: 896

I think that your variable "filename" is conflicting with the method keyword "Filename". Call your variable something else...

OFRrm.SaveAs Filename:= Path & myFile & ".pdf", FileFormat:=xlTypePDF

or just eliminate the keywords in the arguments altogether:

OFRrm.SaveAs Path & myFile & ".pdf", xlTypePDF

Upvotes: 0

Related Questions