How to create a line chart automatically - Excel VBA

Charts are another very important elements available in Excel. They play key role in data visualization. Charts have been important in comparing data and analytics. We often use them in reports and dashboards. Because they help to get data-driven insights for the users.Today I am going to show you how to create a chart automatically using VBA. Let’s consider this sample data.

This is a monthly profit of a shop. Now let’s create a chart to compare profit vs months. So months will be in x axis and profit will be in y axis. We can create the chart automatically using below code.

Sub CreateChart_Ex1() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Activate WS.Range("A1:B13").Select WS.Shapes.AddChart2(227, xlLine).Select End Sub

Note that you need to activate the sheet before select the range if you are in different sheet. WS.Activate doesn’t throw error even if you are in that same sheet. You will get this chart if you run above code.

However using this code, you can create a chart only in the worksheet where your data is in. Suppose you have data in one sheet and you need to create chart in another sheet. We have a solution for that too. Assume we have data in Sheet1 and we want to create chart in Sheet3. We can do it like this.

Sub CreateChart_Ex2() Dim WS As Worksheet Dim WS_New As Worksheet Set WS = Worksheets("Sheet1") Set WS_New = Worksheets("Sheet3") WS_New.Activate WS_New.Shapes.AddChart2(227, xlLine).Select ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$13") End Sub

In above examples we created graphs for fixed range. How about if we require to create a chart for dynamic range. Then we have to find the last row of the data, assign it to a variable and then use that variable like this.

WS.Range("A1:B" & LastRow).Select

Also it is a good practice to define the variable at the beginning. You can define the LastRow as integer or long depending on the amount of rows you will have.

This post explains how to find the row number of the last non empty cell of a worksheet.

Best way to get the last row of any column of the excel sheet

So from this post we learnt how to create a chart with one series automatically. If you want to know how to create a chart with multiple series then check out this post.

Create line chart with multiple series using Excel VBA

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.

Save an Excel Sheet as PDF

Today I’m going to show you how to save an Excel sheet as PDF. Suppose we have an Excel sheet like this.

Now let's learn how to convert this Excel sheet to a PDF file. First click on the “File” menu.

Then click on “Save As”.

Click “Browse”.

It will open Save as dialog box. Choose the folder where you want to save your PDF file.

Select PDF format from the “Select as type” drop-down.

And give suitable name for the PDF file. Then click “Save”.

I have chosen the saving location as the folder, where I have this original excel file. So the PDF file will created like this.

Also if you need, you can convert an Excel sheet to PDF automatically using VBA. Read this post if you want to know how.

Convert an Excel Sheet to PDF Using VBA

Missing Reference - Excel VBA

I recently received an Excel file from internet. And I wanted to do some small modifications to it. It was macro enabled excel file. Then I opened the VBA editor and also checked the references. If you don’t know what references are, they are pointers to Type libraries or DLL files.

There was reference to office web components add-in. But it was not available on my system. So it was listed as a “MISSING” reference. When we copy excel file from one device to another, Type libraries and DLL files are not transferred with it. Only the pointer are transferred. This is the reason for missing references. However in this file, functionality related to that add-in was not needed anymore. So I removed that part from the code. But I didn’t remove the tick from that missing reference.

Next I ran the macro. Then I got a compile error.

When I click OK button it highlighted the code like this.

So it was highlighting a code related to Microsoft scripting runtime. But Microsoft scripting runtime was available in my system and there was no any error in this coding part or reference. Then I realized that this was occurred due to tick in the Missing reference. And I found that the program work without error, when I remove that tick from the missing library. So if you have a missing reference in your vba project then this can cause very strange compile errors like this.

Add Hyperlinks in PowerPoint - Excel VBA

In this post I will explain you how to add a hyperlink to a powerpoint shape. We often need to add hyperlinks in PowerPoint Presentations. It may be a website, email address, or another slide in same presentation. Sometimes we need to add hyperlink to a shape object of a slide. And sometimes we need to add hyperlink to specific text inside a object of a slide. So let’s learn how to do this both. First let’s learn how to add a hyperlink to a whole object of a slide. So let’s consider this sample slide.

Assume that the name of this file is “Sample Presentation.pptx” and both Excel and PowerPoint files are in same folder . First we need to launch the PowerPoint application and open the presentation file.

Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue DestinationPPT = ThisWorkbook.Path & "\" & "Sample Presentation.pptx" Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT)

So if you run above code, program will launch PowerPoint application and will open the presentation file. Then we need to add hyperlink to this object.

Name of this object is “24-Point Star 3”. And let’s add the "http://www.excelvbasolutions.com" as address. So we can add the hyperlink as follows.

With oPPTFile.Slides(5).Shapes("24-Point Star 3").ActionSettings(ppMouseClick)      .Action = ppActionHyperlink      .Hyperlink.Address = "http://www.excelvbasolutions.com" End With

There are times we don’t need to add hyperlink to whole object. Instead, we need to add hyperlink to specific text inside an object. Here is an example. Suppose that we need to add hyperlink only to text “blog” of this slide.

Assume this is the 6th slide of our presentation. We have a rectangle in this slide. The name of the rectangle is “Rectangle 3”. Now we are going to add the hyperlink only to text “blog” inside the rectangle. We can do it like this.

With oPPTFile.Slides(6).Shapes("Rectangle 3").TextFrame.TextRange.Find("Blog").ActionSettings(ppMouseClick)      .Action = ppActionHyperlink      .Hyperlink.Address = "http://www.excelvbasolutions.com" End With

So we will get following result if we run above code.

Next let’s learn how to add an email address as hyperlink.

We are going to add hyperlink to word “email”. Assume that the slide number is 7. Name of the object is “Rectangle 4”. So we can do it using following code.

Dim EmailAddress As String EmailAddress = "Your email address here" With oPPTFile.Slides(7).Shapes("Rectangle 4").TextFrame.TextRange.Find("email").ActionSettings(ppMouseClick)      .Action = ppActionHyperlink      .Hyperlink.Address = "mailto:" & EmailAddress End With

Then the result will look like this.

Controlling PowerPoint from Excel - VBA

In this post I will show you how to automate PowerPoint from excel using vba. Let’s learn this using following example.

This is a simple database which contains data of several companies. Database contains data like vision, history, branches etc. of each company. So in this example we will create PowerPoint file for each company.

First, let’s learn how to start PowerPoint application from Excel.

Sub StartPowerPoint() Dim oPPTApp As PowerPoint.Application Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue End Sub

So if you run above macro it will launch PowerPoint application.

For this example we use PowerPoint template file which is saved at the folder where our database file is. I created this very simple PowerPoint template to show you how to automate various tasks from excel.

First we need to develop the program to loop through each row and create separate PowerPoint file for each company. So following is the starting part of our program.

Sub GeneratePowerPointFiles() Dim WS As Worksheet Dim i As Long Dim PptFileName As String Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim oPPTShape As PowerPoint.Shape Dim oPPTSlide As PowerPoint.Slide Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue Set WS = ActiveSheet 'find last row of WS WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row For i = 2 To WS_LastRow Next i End Sub

Then within each loop, we need to open the template and do the editing. So we can open the PowerPoint template file for each loop like this.

For i = 2 To WS_LastRow      If WS.Range("G" & i).Value <> "" Then           DestinationPPT = ThisWorkbook.Path & "\" & "Company Template.pptx"           Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT)      End If Next i

Now let’s look at our first slide.

Here we need to put name of each company while loop through the rows. So we should replace “Company Name” with values in column A. To do that first we need to identify the names of objects inside the PowerPoint presentation. This post explains how to find names of objects in a PowerPoint presentation.

How to Find Names of Objects in a PowerPoint Slide

So if the name of the title object is “Title 1” then we can do it as follows.

oPPTFile.Slides(1).Shapes("Title 1").TextFrame.TextRange.Text = WS.Range("A" & i).Value

Now let’s move to our next slide.

We have a textbox in this slide. And the name of the textbox is “TextBox 4”. Here we need to put vision of each company.

oPPTFile.Slides(2).Shapes("TextBox 4").TextFrame.TextRange.Text = "Our Vision" & vbCrLf & vbCrLf & WS.Range("B" & i).Value

vbCrLf used to add space between title and the vision. And we have another different type of object in our next slide.

We have rectangle here and we need to put history of each company inside this rectangle. As the name of the rectangle is “Rectangle 3” we can automate it as follows.

oPPTFile.Slides(3).Shapes("Rectangle 3").TextFrame.TextRange.Text = WS.Range("C" & i).Value

Note that you need to put slide number before the object name. Because two different slides can have objects with same name. This is our next slide.

We have two types of objects in this slide. We need to put branch names in “Content Placeholder 2” object. We can do it like this.

oPPTFile.Slides(4).Shapes("Content Placeholder 2").TextFrame.TextRange.Text = WS.Range("D" & i).Value & vbCrLf & WS.Range("E" & i).Value & vbCrLf & WS.Range("F" & i).Value

Next we need to save the PowerPoint presentation with the name given in the column G.

PptFileName = WS.Range("G" & i).Value & ".pptx" PptFileNameString = oPPTFile.Path & "\" & PptFileName oPPTFile.SaveAs PptFileNameString

Also we need to close the original PowerPoint Template at the end of each loop.

'Close PowerPoint oPPTFile.Close Set oPPTFile = Nothing DoEvents

So below is the full code of this VBA program.

Sub GeneratePowerPointFiles() Dim WS As Worksheet Dim i As Long Dim PptFileName As String Dim WebsiteAddress As String Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim oPPTShape As PowerPoint.Shape Dim oPPTSlide As PowerPoint.Slide Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue Set WS = ActiveSheet 'find last row of WS WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row For i = 2 To WS_LastRow     If WS.Range("G" & i).Value <> "" Then         DestinationPPT = ThisWorkbook.Path & "\" & "Company Template.pptx"         Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT)         Application.Wait (Now + TimeValue("0:00:02"))         DoEvents         oPPTFile.Slides(1).Shapes("Title 1").TextFrame.TextRange.Text = WS.Range("A" & i).Value         oPPTFile.Slides(2).Shapes("TextBox 4").TextFrame.TextRange.Text = "Our Vision" & vbCrLf & vbCrLf & WS.Range("B" & i).Value         oPPTFile.Slides(3).Shapes("Rectangle 3").TextFrame.TextRange.Text = WS.Range("C" & i).Value         oPPTFile.Slides(4).Shapes("Content Placeholder 2").TextFrame.TextRange.Text = WS.Range("D" & i).Value & vbCrLf & _ WS.Range("E" & i).Value & vbCrLf & WS.Range("F" & i).Value         PptFileName = WS.Range("G" & i).Value & ".pptx"         PptFileNameString = oPPTFile.Path & "\" & PptFileName         oPPTFile.SaveAs PptFileNameString         'Close PowerPoint         oPPTFile.Close         Set oPPTFile = Nothing         DoEvents     End If Next i oPPTApp.Quit MsgBox "Completed!", vbInformation, "" End Sub

So the result files will be created in the folder where our original PowerPoint template file is.

How to Find Names of Objects in a PowerPoint Slide

Sometimes we need to control PowerPoint from excel. We may need to create or edit a PowerPoint presentation with excel sheet data. Or we may need to create several PowerPoint files using an Excel database and a PowerPoint template. So if you want to develop an Excel application which has this kind of requirement, first thing you need to do is finding the names of each objects within the PowerPoint presentation. So in this post I will explain how to find the name of any object within a PowerPoint presentation.

First, open your presentation.

Click on any object you want to find the name of. Then format tab will appear like this.

Now go to the “Format” tab. And click on the “Selection Pane” in the “Arrange” group.

When you click on the “Selection Pane”, Selection pane will appear right side of the PowerPoint Application.

Then you can find name of each object of current slide from that selection pane. In this example, names of objects are “Subtitle 2” and “Title 1”. And as I have selected the title in the slide, “Title 1” is highlighted in the “Selection pane”. This way you can find the name of any object in your slide.

Here is another example slide of that same PowerPoint presentation.

As you can see, this slide also has an object with name “Title 1”. So you can have objects with same name in different slides. Due to this reason we can’t reference objects only by their names. So we need to use slide number as well. See following example.

Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue DestinationPPT = "Path of the presentation here" Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT) oPPTFile.Slides(1).Shapes("Title 1").TextFrame.TextRange.Text = "Slide 1 heading" oPPTFile.Slides(2).Shapes("Title 1").TextFrame.TextRange.Text = "Slide 2 heading"

Also, using this method you can find names of other types of objects as well. Here is an example slide with an image.