Automating textbox updates in PP using VBA to read excel data

[noobslayer252]

Hi there,

I am trying to write a VBA code in order to automate a PP to automatically update textboxes with excel data. The current approach is:

1. Set Excel File Path: It defines the path to the Excel workbook from which the data will be read.
2. Open Excel Application: It creates a new instance of Excel in the background (not visible to the user).
3. Open Workbook and Access Worksheet: It opens the specified Excel workbook and accesses the first worksheet (assuming the data you want is on the first sheet).
4. Read Cell Data: It reads the value from cell J316 on the first worksheet.
5. Display Value from Excel: A message box pops up displaying the value read from Excel, which serves as a confirmation that the data has been retrieved.
6. Iterate Through PowerPoint Slides: The macro goes through each slide in the active PowerPoint presentation.
7. Check for Specific Shape: Within each slide, it checks if a shape named "TextBox 1" exists. The ShapeExists function is called here to determine whether a shape with the specified name is present on the slide.
8. Update Textbox: If the shape exists, the macro updates the text of that shape (textbox) with the value read from Excel. The value is formatted as a percentage (to two decimal places) before being set as the text of the textbox.
9. Display Update Confirmation: After updating the textbox, a message box notifies that "TextBox 1" on the current slide is being updated.
10. Clean Up: Once all slides have been processed, the macro closes the Excel workbook without saving changes (wb.Close False), quits the Excel application to free up system resources, and clears the object variables to prevent memory leaks.
11. ShapeExists Function: This helper function is used by the macro to check if a shape with a given name exists on a slide. It employs error handling to avoid runtime errors if the shape is not found. If the shape exists, the function returns True; otherwise, False.

Code Below:

Sub UpdateTextboxesFromExcel()
Dim excelApp As Object
Dim wb As Object
Dim ws As Object
Dim excelFilePath As String
Dim cellValue As Variant
Dim slide As Object

' Path to the Excel file
excelFilePath = "N:\_Initiative\Clients\2023\IAG\_Comms Design\Charlie Dox\INI-VENTORS\Ini Ventors Draft Excel V1.xlsm"

' Create a new Excel application and open the workbook
Set excelApp = CreateObject("Excel.Application")
Set wb = excelApp.Workbooks.Open(excelFilePath)
Set ws = wb.Sheets(1) ' Assuming data is on the first sheet

' Read data from cell J316
cellValue = ws.Range("J316").Value

' Display a message box with the value read from Excel
MsgBox "Read from Excel: " & cellValue

' Iterate through each slide in the PowerPoint presentation
For Each slide In ActivePresentation.Slides
' Check if the textbox named "TextBox 1" exists in the slide
If ShapeExists("TextBox 1", slide) Then
' Update the textbox text with data from Excel
slide.Shapes("TextBox 1").TextFrame.TextRange.Text = Format(cellValue, "0.00%")
' Display a message box indicating the textbox is being updated
MsgBox "Updating TextBox 1 on slide " & slide.SlideIndex
End If
Next slide

' Clean up
wb.Close False
excelApp.Quit
Set ws = Nothing
Set wb = Nothing
Set excelApp = Nothing
End Sub

Function ShapeExists(shapeName As String, slide As Object) As Boolean
Dim shp As Object
On Error Resume Next
Set shp = slide.Shapes(shapeName)
On Error GoTo 0
ShapeExists = Not shp Is Nothing
End Function

Problem: The textbox doesn't appear to update with any data despite the macro appearing to run (screenshot attached). Appreciate any help I can get to troubleshoot.