1
\$\begingroup\$

My task is to copy data from an Excel sheet into charts on PowerPoint slides. To minimize the interactions between the two, I have an Excel macro insert the data for each slide into that slide's Notes pane. Next I have a PowerPoint macro extract the data from each slide's Notes and insert it into the two charts on that slide.

The data in the Notes is a string with the values separated by commas. I use Split to convert the string to an array. I identify the fields by their position in the array; that's the part that embarrasses me.

This is an example of the data string:

Shawn-MPS/BSC/BP,1,,,
June Area Text,Flexible,Diplomatic,Stress Management,
June Area Numbers, 3.5,2.1,2.9

And the macro:

Sub openChartData()
'Dim ThisWB
Dim strSlideNoteText As String

'On each slide, this opens the charts in Item 4 and 13, and lets you edit the underlying data spreadsheet

For Each aSlide In ActivePresentation.Slides
    aSlide.Select                            'For debugging only
    strSlideNoteText = aSlide.NotesPage.Shapes(2).TextFrame.TextRange.Text
    If InStr(strSlideNoteText, "SAMPLE") > 0 Then GoTo skip 'Skip the Sample slide

    'Break the note text into an array of individual words
    Dim arrSlideNoteText As Variant          '
    arrSlideNoteText = Split(strSlideNoteText, ",")

     ' MPS/BSC/BP
    With aSlide.Shapes(13).Chart.ChartData
        .Activate
        With .Workbook.sheets(1)
            .Cells(2, 2).Value = arrSlideNoteText(1)
            .Cells(2, 3).Value = arrSlideNoteText(2)
            .Cells(2, 4).Value = arrSlideNoteText(3)
        End With
        .Workbook.Close
    End With

    ' First Harrison Score and "Area" names
    With aSlide.Shapes(4).Chart.ChartData
        .Activate
        With .Workbook.sheets(1)
            .Cells(1, 2).Value = Val(arrSlideNoteText(5))
            .Cells(1, 3).Value = Val(arrSlideNoteText(6))
            .Cells(1, 4).Value = Val(arrSlideNoteText(7))
            .Cells(2, 2).Value = Val(arrSlideNoteText(9))
            .Cells(2, 3).Value = Val(arrSlideNoteText(10))
            .Cells(2, 4).Value = Val(arrSlideNoteText(11))
        End With
        .Workbook.Close
    End With




skip:
    Next aSlide
    MsgBox "Done"
End Sub

Obviously, if I have to make a change to the order of the fields, I have to change 11 lines of code, and there's always a chance of error. Is there a better way to handle this?

(If fixing this code involves revising the format of the string sent by Excel, I'm willing to do that.)

\$\endgroup\$
  • \$\begingroup\$ Why are you not updating powerpoint charts directly from excel data, Is there any reason to paste data to notes area ? \$\endgroup\$ – Harsha Vardhan Jul 7 at 18:20
  • 1
    \$\begingroup\$ Having the data in a slide's Notes makes it easily visible in one spot. I can confirm that the numbers are on the correct slide, for one thing. Also, if we have to make minor changes to the data, we can do it in the Notes and run the PowerPoint macro without running Excel. \$\endgroup\$ – Shawn V. Wilson Jul 7 at 20:01
  • \$\begingroup\$ arrSlideNoteText is array, which will have LBound and UBound use these two functions you have replace celll(2,2).value with loop.. \$\endgroup\$ – Harsha Vardhan Jul 8 at 3:39
  • \$\begingroup\$ I would suggest updating both macros to use the titles of the data that you are copying. Then you can make the data more dynamic and not dependent on the order of the columns. \$\endgroup\$ – seadoggie01 Jul 26 at 17:36
  • \$\begingroup\$ @seadoggie01 Do you mean that every other field would contain the field name of the following field? "Name,Shawn,City,Los Angeles,Zip,90001", like that? \$\endgroup\$ – Shawn V. Wilson Jul 27 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.