# Pulling substrings from a CSV string without knowing position

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.)

• Why are you not updating powerpoint charts directly from excel data, Is there any reason to paste data to notes area ? – Harsha Vardhan Jul 7 at 18:20
• 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. – Shawn V. Wilson Jul 7 at 20:01
• arrSlideNoteText is array, which will have LBound and UBound use these two functions you have replace celll(2,2).value with loop.. – Harsha Vardhan Jul 8 at 3:39
• 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. – seadoggie01 Jul 26 at 17:36
• @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? – Shawn V. Wilson Jul 27 at 19:20