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
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
