none
Replace text in chart data embedded in PowerPoint presentation - help, please! RRS feed

  • Question

  • Hi all,

    I have a huge PowerPoint presentation, most slides with charts.  I need to change the text within the charts - usually the text  repeats itself in all charts.  Instead of manually, opening the chart data and pasting the new text, I wrote the following syntax.  All components work, except for the Replace line:

    I get a "Run-time error '9': Subscript out of range" message

    What am I doing wrong?

    Thanks!

    Sub TranslateCharts()

        Dim sLang1 As String, sLang2 As String

        Dim sh As Shape

        On Error Resume Next

        Set ppApp = GetObject(, "PowerPoint.Application")

        If Err.Number <> 0 Then

            Set ppApp = CreateObject("PowerPoint.Application")

        End If

        Err.Clear

        On Error GoTo 0

        ppApp.Visible = True

        Set myPresentation = ppApp.Presentations.Open("---path/filename appear here---")

        ChangeCharts "XXX", "YYY"

    End Sub

    Sub ChangeCharts(sLang1 As String, sLang2 As String)

        Dim oSld As Object

        Dim oChart As Chart

        Dim oShp As Shape

        Dim oChartData As ChartData

        

        For Each oSld In myPresentation.Slides

            oSld.Select

            For Each oShp In oSld.Shapes

                oShp.Select

                If oShp.HasChart Then

                    Set oChart = oShp.Chart

                    oChart.Select

                    Set oChartData = oChart.ChartData

                    oChartData.Activate

                    oChartData.Workbook.worksheets("Sheet1").Range("A1:F10").Select   ' works fine up to this line - data table opens, range is highlighted

    ' this is the command line for which I get the error:

                    oChartData.Workbook.worksheets("Sheet1").Range("A1:F10").Replace What:=sLang1, Replacement:=sLang2, LookAt:=xlPart, _

                      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

                      ReplaceFormat:=False

                    oChart.ChartData.Workbook.Close

                End If

            Next

        Next

    End Sub

    Monday, December 16, 2013 11:55 AM

Answers

All replies

  • Hello Niritav,

    First of all, I would recommend breaking the following lines of code into single lines:

    oChartData.Workbook.worksheets("Sheet1").Range("A1:F10").Select   ' works fine up to this line - data table opens, range is highlighted
    
    ' this is the command line for which I get the error:
    
    oChartData.Workbook.worksheets("Sheet1").Range("A1:F10").Replace What:=sLang1, Replacement
    
    ' break them into single calls
    
    Dim workbook as Excel.Workbook
    Dim worksheet as Excel.Worksheet
    Dim range as Excel.Range
    Set workbook = oChartData.Workbook
    Set worksheet = workbook.worksheets("Sheet1")
    Set range = worksheet.Range("A1:F10")
    
    range.Select
    range.Replace '... specify arguments explicitly 
    
    Finally, the Replace method description in MSDN states the following: to avoid problems, set these arguments explicitly each time you use this method.
    Monday, December 16, 2013 12:32 PM
  • Thanks, Eugene!  It really works! --- you can see I'm new to this.

    Now I'm getting message boxes every time Replace can't find the target word.  I tried "Application.DisplayAlerts = False" but that doesn't do the trick.  Any advice?

    Thanks again,

    Nirit

    Monday, December 16, 2013 4:33 PM
  • Hi Nirit,

    Please try to use the ScreenUpdating property of the Application class.

    Monday, December 16, 2013 6:26 PM
  • ScreenUpdating = False didn't do the trick, but restarting the computer (not intentionally...) did!

    Thanks for your responses!

    Tuesday, December 17, 2013 11:34 AM
  • Good news, Niritav!

    Thank you for sharing the solution for other forum reading. I hope it will help somebody.

    Tuesday, December 17, 2013 4:49 PM