B
Barb Reinhardt
I have the following macro that pulls out references to external files and
references the current sheet. I'm sure it's a simple fix to someone, but I
can't get it.
Sub SeriesFix()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim objCht As Object
Dim chtSeries As Series
Dim sName As String
Dim sReplace As String
Dim sTemp As String
'This sub will remove all references to files on other servers within the
'chart series
Set aWB = ActiveWorkbook
For Each aWS In aWB.Worksheets
Debug.Print aWS.Name
sName = "*" & aWS.Name & "*"
sReplace = "'" & aWS.Name & "'!"
For Each objCht In aWS.ChartObjects
With objCht.Chart
Debug.Print .Name
For Each chtSeries In .SeriesCollection
With chtSeries
If .Formula Like sName Then
sTemp = Replace(.Formula, _
sReplace, "")
Debug.Print sTemp, .Formula
chtSeries.Formula = sTemp <~~~~~doesn't like this
End If
End With
Next chtSeries
End With
Next objCht
Next aWS
End Sub
I'm guessing I need to change the X and Y parts of the series. Thanks for
any assistance.
Barb Reinhardt
references the current sheet. I'm sure it's a simple fix to someone, but I
can't get it.
Sub SeriesFix()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim objCht As Object
Dim chtSeries As Series
Dim sName As String
Dim sReplace As String
Dim sTemp As String
'This sub will remove all references to files on other servers within the
'chart series
Set aWB = ActiveWorkbook
For Each aWS In aWB.Worksheets
Debug.Print aWS.Name
sName = "*" & aWS.Name & "*"
sReplace = "'" & aWS.Name & "'!"
For Each objCht In aWS.ChartObjects
With objCht.Chart
Debug.Print .Name
For Each chtSeries In .SeriesCollection
With chtSeries
If .Formula Like sName Then
sTemp = Replace(.Formula, _
sReplace, "")
Debug.Print sTemp, .Formula
chtSeries.Formula = sTemp <~~~~~doesn't like this
End If
End With
Next chtSeries
End With
Next objCht
Next aWS
End Sub
I'm guessing I need to change the X and Y parts of the series. Thanks for
any assistance.
Barb Reinhardt