CODEWITHSUNDEEP
vbapowerpoint
hunter21188
hunter21188

Reputation: 415

Extract Data from Excel Cell to PowerPoint Shape with VBA

I have an excel workbook with the julian day in column one, the normal high temperature in column two, and the normal low temperature in column three. I need to write a script in PowerPoint VBA to match the current Julian Day with the same number in Column one in the workbook. Then I need it to extract the normal high for that day (Column two) and insert that number into a named shape in a particular PPT slide. Here is what I have so far:

Function SectionIndexOf(sSectionName As String) As Long
'This Function makes sure you can declare the name of any Section Name
'in the Sub below.

    Dim x As Long
    With ActivePresentation.SectionProperties
        For x = 1 To .Count
            If .Name(x) = sSectionName Then
                SectionIndexOf = x
            End If
        Next
    End With
End Function

Sub Climo()

Headlines = SectionIndexOf("Headlines")

'Open the Excel Workbook.
Dim CLI As New Excel.Workbook
Set CLI = Excel.Application.Workbooks.Open("Z:\climo.xlsx")

'Grab the first Worksheet in the Workbook
Dim WS As Excel.Worksheet
Dim NormalHi As String
Dim NormalLo As String
Dim shp As Shape
Dim sld As Slide

Set WS = CLI.Worksheets(1)

Dim i As Long
'Loop through all rows in Column A (Julian Day)

For i = 1 To WS.Range("A372").End(xlUp).Row
    NormalHi = WS.Cells(i, 9).Value
    Debug.Print NormalHi 'This just returns new blank lines

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If sld.sectionIndex = Headlines Then
                With sld.Shapes("NormalHi")
                .TextFrame2.TextRange.Font.Name = Arial
                .TextFrame2.TextRange.Font.Size = 16
                .TextFrame.TextRange.Font.Color = vbRed
                .TextFrame2.TextRange.Text = NormalHi
                End With
            End If
        Next
    Next
Next

End Sub

Before even trying to figure out how to match the current Julian Day with the proper row in the Excel workbook, I am just trying to extract data from any cell and plot it in the shape. I get no errors when I run this and using Debug.Print gives me blank new lines. Not sure what is wrong. Thanks!

Upvotes: 0

Views: 2286

Answers (1)

Sabrina Tessier
Sabrina Tessier

Reputation: 79

You mention in your question that the Normal High data is found in column 2 but then when you are grabbing the value, you're referencing column 9. Perhaps that's the issue?

NormalHi = WS.Cells(i, 9).Value

Upvotes: 1

Related Questions