Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Convert an Excel Sheet to PDF Using VBA

In my earlier post I explained how to save an Excel sheet as PDF manually. If you want to know how to do it, then check this post.

Save an Excel Sheet as PDF Manually

Today I’m going to teach you how to convert an Excel sheet to PDF automatically. We need this type of functionality, when we develop applications which output reports. Because lot of users need their reports as PDF files. Here is one such report generated by an Excel application.

Now let’s learn how to convert this to a PDF file. First we need to define our variables.

Dim WS As Worksheet

Dim FileName As String
Dim FilePath As String

I’m going to convert the activesheet to PDF. So I will assign activesheet to WS variable.

Set WS = ActiveSheet

Next we need to give the name to the PDF file

FileName = "Result PDF"

Also we should select the orientation. You should select portrait or landscape according to the length and width of your excel sheet. I will choose portrait for this example.

WS.PageSetup.Orientation = xlPortrait

If you need landscape then you should replace xlPortrait with xlLandscape. Now we have to select the saving location. In this example I will select the same folder where the application is.

FilePath = ThisWorkbook.Path

Or else you can assign specific file path like this.

FilePath = "C:\Users\EVS\Documents\PDF Reports"

We have assign the values to all our variables. And have set the orientation. So now we can convert the sheet as follows.

WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

Next we show the confirmation message.

MsgBox "Completed", vbInformation, ""

And here is the complete code to do the job.

Sub SaveAsPDF()

Dim WS As Worksheet

Dim FileName As String
Dim FilePath As String

Set WS = ActiveSheet

FileName = "Result PDF"

WS.PageSetup.Orientation = xlPortrait

FilePath = ThisWorkbook.Path

WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

MsgBox "Completed", vbInformation, ""

End Sub

When you run above code, result file will be created like this.



Contact Form

Name

Email *

Message *

Popular Posts