To open the PowerPoint presentation from excel macro using VBA. Create a text box on a slide using cell data from excel sheet.
Here we have written code in an excel macro file. This macro file opens a PowerPoint presentation from a location. Then the code adds a text box on the slide 1 of this presentation, text box will have the value which is placed in cell A1 of the excel macro file. The VBA code is added in the command button.
- Open the presentation from a location
- Add the text boxes on the slide
- Set the value in text box as the value in cell A1
- Change the font size of the text box string
- Set the border of the text box
- Save the PowerPoint presentation
- Close the PowerPoint presentation
Here first we have added “Microsoft PowerPoint 16.0 Object Library” in excel macro file,as shown in the image below.
We have created a command button on a slide, which has the VBA code(shown later in the post). When we click on this button it will open the PowerPoint presentation.
Once the PowerPoint presentation will be opened, a text box will be created which will have “Test Data” text in it.This text “Test Data” was mentioned in the excel VBA file in cell A1, which we can see in the image below.
After the successful execution of code, slide will look like as shown below.
Sub OpenMyPpt() Application.ScreenUpdating = False Dim ObjPptApp As PowerPoint.Application Dim TargetPpt As PowerPoint.Presentation Dim NewTextBox As PowerPoint.Shape Set ObjPptApp = CreateObject("PowerPoint.Application") ‘Open PPT from a location Set TargetPpt = ObjPptApp.Presentations.Open("C:\Users\YourUserName\Desktop\macro\PPT\1.7_Test_ppt.pptx") TargetPpt.Windows(1).Activate ‘Add text box in the slide 1 of opened PPT Set NewTextBox = ObjPptApp.ActivePresentation.Slides(1).Shapes. _ AddTextbox(Orientation:=msoTextOrientationHorizontal, Left:=100, Top:=100, Width:=100, Height:=50) With NewTextBox .TextFrame.TextRange.Text = Range("A1").Value .Line.Style = msoLineThinThick .TextFrame.TextRange.Font.Size = 15 End With ‘Save and close the presentation ObjPptApp.ActivePresentation.Save ObjPptApp.Windows(1).Close End Sub
Similar approach can be applied for performing any operation on a PowerPoint presentation. Writing the code in excel file gives an advantage that we don’t have to write any code in presentation.
Post you may like