VBA is common to all the applications within the Office Suite. This means that data in an Excel spreadsheet can be automatically exported to PowerPoint as well.
The advantage of knowing how to use VBA to automate these tasks saves a lot of time. Instead of manually copying from Excel and pasting into PowerPoint, everything can be automated, using the appropriate procedures in VBA. Our book VBA Automation for Excel 2019 Cookbook addresses these topics.
We will cover the following recipes in this article:
• Creating a new instance of PowerPoint
• Creating presentations and slides
• Adding text to textboxes
• Copying Excel content into PowerPoint
By the end, you will be able to initiate a PowerPoint slideshow from within Excel.
Technical requirements
This cookbook was written and designed to be used with MS Office 2019 and MS Office 365, installed on either Windows 8, 8.1, or 10.
If your hardware and software meet these requirements, you are good to go.
The code for this book is stored at the following link: https://github.com/ PacktPublishing/VBA-Automation-for-Excel-2019-Cookbook.
Please visit the following link to check the CiA videos: https://bit.ly/3jQRvVk.
Creating a new instance of PowerPoint
The process of setting a reference to the object library with Word is similar to that of PowerPoint. Hence, the same must be done with PowerPoint, or else Excel cannot communicate with PowerPoint at all.
In this recipe, we will be creating a new instance of PowerPoint from within Excel.
Getting ready
Open Excel and activate a new workbook. Save the file as a macro-enabled file on your desktop and call it PPoint_Interaction.xlsm. Sheet1 should be active. Press Alt + F11 to switch to the VBA Editor, and then insert a new module.
It is a prerequisite that MS PowerPoint must also be installed on your computer in order for the instructions in this recipe to work effectively.
How to do it…
Here is how to link PowerPoint to Excel:
1. In the VBA Editor, click on Tools | References. The References — VBAProject dialog box will open:
2. Scroll down the list of available references until you find Microsoft PowerPoint 16.0 Object Library:
3. Once selected, click on OK to save the selection and also to close the dialog box.
4. With the object library in place, we can initiate PowerPoint from within Excel. There are several ways of doing this, but for this recipe, we will be using this specific technique:
Sub CreatePPointSlides()
Dim PowPntApp As PowerPOint.Application
Set PowPntApp = New PowerPoint.Application
PowPntApp.Visible = True
PowPntApp.Activate
End Sub
5. Test the Sub procedure by pressing F5. A new instance of PowerPoint will appear on your screen, with no presentation or slide active. Close the application once you know that the Sub procedure is working:
With the Microsoft PowerPoint object library enabled, it is possible to use PowerPoint keywords and methods within Excel.
Actions such as opening an instance of PowerPoint, which was previously not possible, can now be done.
Creating presentations and slides
Opening PowerPoint without opening a new presentation is not very useful. It stands to reason that a new presentation, with at least one slide, should be initiated together with the application.
Getting ready
With Excel still open, make sure that PPoint_Interaction.xlsm is available.
Module1 in the VBA Editor must be visible.
How to do it…
Here are the steps to follow:
1. Add the next lines of code to the Sub procedure to open a new presentation with a new slide:
Sub CreatePPointSlides()
Dim PowPntApp As PowerPoint.Application
Dim PowPntPrsnt As PowerPoint.Presentation
Dim PowPntSlide As PowerPoint.Slide
Set PowPntApp = New PowerPoint.Application
PowPntApp.Visible = True
PowPntApp.Activate
Set PowPntPrsnt = PowPntApp.Presentations.Add
Set PowPntSlide = _
PowPntPrsnt.Slides.Add(1, ppLayoutTitle)
End Sub
2. If you now run the procedure, PowerPoint will open again, this time displaying not only a new presentation, but also a new slide.
3. Close PowerPoint, but don’t save anything. Return to the VBA Editor in Excel.
Opening a new instance of PowerPoint creates a new presentation as well as a new, empty slide. By using the appropriate VBA coding in Excel, the process can be automated.
Adding text to textboxes
It’s all very well to know how to open PowerPoint, create presentations, and make a slide available. The next objective is to add text to the two available textboxes on Slide1.
In this recipe, we will be adding text to the textboxes on Slide1.
Getting ready
Make sure PPoint_Interaction.xlsm is still open, and that the VBA Editor is active.
How to do it…
These are the steps to enter and format text in PowerPoint:
1. To add text to a PowerPoint presentation via Excel, add the following line of code:
Sub CreatePPointSlides()
Dim PowPntApp As PowerPoint.Application
Dim PowPntPrsnt As PowerPoint.Presentation
Dim PowPntSlide As PowerPoint.Slide
Set PowPntApp = New PowerPoint.Application
PowPntApp.Visible = True
PowPntApp.Activate
Set PowPntPrsnt = PowPntApp.Presentations.Add
Set PowPntSlide = _
PowPntPrsnt.Slides.Add(1, ppLayoutTitle)
PowPntSlide.Shapes(1).TextFrame.TextRange = _
“Employee Information Slide” PowPntSlide.Shapes(2).TextFrame.TextRange = _
“by Presenter”
End Sub
2. Press F5. Running the Sub procedure will now result in a new instance of PowerPoint, with a new presentation and slide open:
3. Each textbox will have its own line of text. Close PowerPoint without saving, since there is more code to add to our procedure in Excel.
Using the appropriate code will enable you to add customized text to individual textboxes on a slide.
Copying Excel content into PowerPoint
Automatically copying content from Excel to PowerPoint saves a lot of time. What we need to do now is select data, copy it, and then paste that into yet another newly inserted slide.
In this recipe, we will be copying a table into PowerPoint.
Getting ready
Make sure that PPoint_Interaction.xlsm is still open. Activate Sheet1, and enter the following data:
How to do it…
Follow these steps to copy text from Excel to PowerPoint:
1. Before selecting anything in Excel, we need to insert a new blank slide in PowerPoint. Add the following lines of code:
Sub CreatePowPntShow()
Dim PowPntApp As PowerPoint.Application
Dim PowPntPrsnt As PowerPoint.Presentation
Dim PowPntSlide As PowerPoint.Slide
Set PowPntApp = New PowerPoint.Application
PowPntApp.Visible = True
PowPntApp.Activate
Set PowPntPrsnt = PowPntApp.Presentations.Add
Set PowPntSlide = _
PowPntPrsnt.Slides.Add(1, ppLayoutTitle)
PowPntSlide.Shapes(1).TextFrame.TextRange = _
“Employee Information”
PowPntSlide.Shapes(2).TextFrame.TextRange = _
“by Presenter”
Set PowPntSlide = _
PowPntPrsnt.Slides.Add(2, ppLayoutBlank)
PowPntSlide.Select
End Sub
2. Run the Sub procedure to check your coding. We know that the first part will work, but in this case, we need to check whether a second slide was inserted:
3. To select the data in Excel, add the following line of code, directly below the previous entry. This will select the data on Sheet1 and then copy it:
Range(“A1”).CurrentRegion.Copy
4. Once the data has been copied, it must be pasted into the current slide. Enter the next line of code, once again directly below the previous line of code:
PowPntSlide.Shapes.Paste
5. The last thing we need to do is close down the document, and finally, PowerPoint itself. Just add the following two lines in a new line after the filename:
PowPntPrsnt.Close
PowPntApp.Quit
Selecting data in Excel, copying it, and then pasting it into PowerPoint can all be automated with VBA in Excel.
There’s more…
As a finishing touch, we can add code to create a unique filename for each PowerPoint presentation:
1. We can create a unique name for the file every time it is saved. Add the following lines after .Shapes.Paste:
PowPntPrsnt.SaveAs Environ(“UserProfile”) _
& “\Desktop\EmployeeInformation “ _
& Format(Now, “yyyy-mm-dd hh-mm-ss”) & “.pptx”
2. Every time you run the procedure now, a new file with a unique name will be saved.
3. The complete Sub procedure should look like this:
Sub CreatePPointSlides()
Dim PowPntApp As PowerPoint.Application
Dim PowPntPrsnt As PowerPoint.Presentation
Dim PowPntSlide As PowerPoint.Slide
Set PowPntApp = New PowerPoint.Application
PowPntApp.Visible = True
PowPntApp.Activate
Set PowPntPrsnt = PowPntApp.Presentations.Add
Set PowPntSlide = _
PowPntPrsnt.Slides.Add(1, ppLayoutTitle)
PowPntSlide.Shapes(1).TextFrame.TextRange = _
“Employee Information”
PowPntSlide.Shapes(2).TextFrame.TextRange = _
“by Presenter”
Set PowPntSlide = _ PowPntPrsnt.Slides.Add(2, ppLayoutBlank) PowPntSlide.Select
Range(“A1”).CurrentRegion.Copy
PowPntSlide.Shapes.Paste
PowPntPrsnt.SaveAs Environ(“UserProfile”) _
& “\Desktop\EmployeeInformation “ _
& Format(Now, “yyyy-mm-dd hh-mm-ss”) & “.pptx”
PowPntPrsnt.Close
PowPntApp.Quit
End Sub
Following the instructions in this recipe will enable you to select information in Excel, copy it there, and then paste it into a specific slide in PowerPoint.
To read in depth here is the link of the book VBA Automation for Excel 2019 Cookbook by Mike Van Niekerk.
Special 25% off at http://packt.live/3u1fPtG. Use discount code 25VBA (Promotion running from 10th February through 15th March).
