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