CODEWITHSUNDEEP
excelvbasyntaxsyntax-error
Kirsty McAlpine
Kirsty McAlpine

Reputation: 13

Exporting an Excel Workbook to PDF using VBA

I'm trying to help my mum remotely with her problem: she needs to save a workbook as an xlsx and a PDF. Here's my code:

Sub sb_Copy_Save_ActiveSheet_As_Workbook()

Dim wksht As Worksheet
Set wksht = ActiveSheet

Dim path As String
path = "C:\Users\" & Environ$("Username") & "\Company Name\Company Name Team Site - Documents\PO Numbers\"

    wksht.Copy
    ActiveWorkbook.SaveAs Filename:=path & wksht.Range("G1") & " " & wksht.Range("F1").Value & ".xlsx"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF FileName:=path & wksht.Range("G1") & " " & wksht.Range("F1").Value & ".pdf" Quality:=xlQualityStandard OpenAfterPublish:=True

End Sub

We got it working to the point where she can save an xlsx file in the specified filepath, but attempting to export it as a PDF isn't working. She says she's getting a syntax error, but as I don't have excel myself I can't test it. I've looked at some similar questions but I can't seem to find an answer.

Thanks very much in advance

Upvotes: 1

Views: 201

Answers (1)

Hayden Moss
Hayden Moss

Reputation: 98

you just need to add commas so that

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF FileName:=path & wksht.Range("G1") & " " & wksht.Range("F1").Value & ".pdf" Quality:=xlQualityStandard OpenAfterPublish:=True

becomes

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=path & wksht.Range("G1") & " " & wksht.Range("F1").Value & ".pdf", Quality:=xlQualityStandard, OpenAfterPublish:=True

Upvotes: 1

Related Questions