I have an annoying problem that keeps coming up. I have sheets and sheets of
charts (we produce about 400 of them every quarter, distributed over 2
files). I have the charts linked to certain other files, which get updated
regularly. For some reason every time I am using the chart file a new
quarter, a few of the charts link to old files that are no longer the active
ones. I change the links, save new copies of all (and I do it in the order
that is necessary for the chart files to remember the new names, so I know
that's not the problem), and then am done with it. At this point, it is
happening, and the way I find the links is just to go through each chart and
click on the items until I see the name in the formula bar that is an old
file name. The I know I just need to update that link to the new file. Is
there any code I can use that would effectively:

1) search each sheet of the workbook
2) on any sheet, search the source links for every input of each chart on
the worksheet
3) if it's got a particular string in the links, tell me (pause on it, write
down the chart title, anything).

This way I could just run the macro to know where I need to go, as opposed
to clicking through the visible data items of all the charts each time.

Thx.
--
Boris

I don't quite follow what you want to do, why not simply Edit Links from the
Edit menu. Or maybe you only want to selectively change links in some
charts.

Following should produce something for your "1,2,3".

Option Explicit
Sub DumpSeries()
Dim wb As Workbook
Dim oSht As Object
Dim cob As ChartObject
Dim i As Long, j As Long
Dim rng As Range

ReDim vArr(1 To 5, 1 To 1)
vArr(1, 1) = "SHEET"
vArr(2, 1) = "CHART"
vArr(3, 1) = "TITLE"
vArr(4, 1) = "SERIES"
vArr(5, 1) = "FORMULA"

Set wb = ActiveWorkbook

For Each oSht In wb.Sheets
For Each cob In oSht.ChartObjects
SeriesFormulas cob.Chart, vArr, oSht.Name
Next
Next

For Each oSht In wb.Charts
SeriesFormulas oSht, vArr, oSht.Name
Next

On Error Resume Next
Set oSht = Nothing
Set oSht = Worksheets("MyCharts")
If Err.Number Then
Set oSht = wb.Worksheets.Add
oSht.Name = "Mycharts"
Else
'delete old listing ?
oSht.Cells.ClearContents
oSht.Activate
End If

On Error GoTo 0

Set rng = Range("a1:E" & UBound(vArr, 2))

'xl2002 & later
'rng.Value = Application.WorksheetFunction.Transpose(vArr)

'transpose might error in xl2000 with 5000+ elements in the array
ReDim varr2(1 To UBound(vArr, 2), 1 To 5)
For i = 1 To UBound(varr2): For j = 1 To 5
varr2(i, j) = vArr(j, i)
Next: Next
rng.Value = varr2

End Sub

Function SeriesFormulas(cht As Chart, va(), sShtName As String)
Dim cnt As Long
Dim i As Long
Dim sr As Series

cnt = cht.SeriesCollection.Count

i = UBound(va, 2)

ReDim Preserve va(1 To 5, 1 To i + cnt)
va(1, i + 1) = sShtName
va(2, i + 1) = cht.Name
If cht.HasTitle Then va(3, i + 1) = cht.ChartTitle.Text

For Each sr In cht.SeriesCollection
i = i + 1
va(4, i) = sr.Name
'can't dump series formula starting with = in a cell, so prefix it
va(5, i) = "#" & sr.Formula
Next

End Function

Wouldn't take too much to adapt to replace links on a selective basis. Note
the construct
' path [ name.xla ] sheet ! ' address

Regards,
Peter T


"BorisS" wrote in message
...
I have an annoying problem that keeps coming up. I have sheets and sheets
of
charts (we produce about 400 of them every quarter, distributed over 2
files). I have the charts linked to certain other files, which get
updated
regularly. For some reason every time I am using the chart file a new
quarter, a few of the charts link to old files that are no longer the
active
ones. I change the links, save new copies of all (and I do it in the
order
that is necessary for the chart files to remember the new names, so I know
that's not the problem), and then am done with it. At this point, it is
happening, and the way I find the links is just to go through each chart
and
click on the items until I see the name in the formula bar that is an old
file name. The I know I just need to update that link to the new file.
Is
there any code I can use that would effectively:

1) search each sheet of the workbook
2) on any sheet, search the source links for every input of each chart on
the worksheet
3) if it's got a particular string in the links, tell me (pause on it,
write
down the chart title, anything).

This way I could just run the macro to know where I need to go, as opposed
to clicking through the visible data items of all the charts each time.

Thx.
--
Boris

Forgot to mention, the example might not return full path's of links if
source workbooks are open.

Peter T

"Peter T" <peter_t@discussions wrote in message
...
I don't quite follow what you want to do, why not simply Edit Links from
the
Edit menu. Or maybe you only want to selectively change links in some
charts.

Following should produce something for your "1,2,3".

Option Explicit
Sub DumpSeries()
Dim wb As Workbook
Dim oSht As Object
Dim cob As ChartObject
Dim i As Long, j As Long
Dim rng As Range

ReDim vArr(1 To 5, 1 To 1)
vArr(1, 1) = "SHEET"
vArr(2, 1) = "CHART"
vArr(3, 1) = "TITLE"
vArr(4, 1) = "SERIES"
vArr(5, 1) = "FORMULA"

Set wb = ActiveWorkbook

For Each oSht In wb.Sheets
For Each cob In oSht.ChartObjects
SeriesFormulas cob.Chart, vArr, oSht.Name
Next
Next

For Each oSht In wb.Charts
SeriesFormulas oSht, vArr, oSht.Name
Next

On Error Resume Next
Set oSht = Nothing
Set oSht = Worksheets("MyCharts")
If Err.Number Then
Set oSht = wb.Worksheets.Add
oSht.Name = "Mycharts"
Else
'delete old listing ?
oSht.Cells.ClearContents
oSht.Activate
End If

On Error GoTo 0

Set rng = Range("a1:E" & UBound(vArr, 2))

'xl2002 & later
'rng.Value = Application.WorksheetFunction.Transpose(vArr)

'transpose might error in xl2000 with 5000+ elements in the array
ReDim varr2(1 To UBound(vArr, 2), 1 To 5)
For i = 1 To UBound(varr2): For j = 1 To 5
varr2(i, j) = vArr(j, i)
Next: Next
rng.Value = varr2

End Sub

Function SeriesFormulas(cht As Chart, va(), sShtName As String)
Dim cnt As Long
Dim i As Long
Dim sr As Series

cnt = cht.SeriesCollection.Count

i = UBound(va, 2)

ReDim Preserve va(1 To 5, 1 To i + cnt)
va(1, i + 1) = sShtName
va(2, i + 1) = cht.Name
If cht.HasTitle Then va(3, i + 1) = cht.ChartTitle.Text

For Each sr In cht.SeriesCollection
i = i + 1
va(4, i) = sr.Name
'can't dump series formula starting with = in a cell, so prefix it
va(5, i) = "#" & sr.Formula
Next

End Function

Wouldn't take too much to adapt to replace links on a selective basis.
Note
the construct
' path [ name.xla ] sheet ! ' address

Regards,
Peter T


"BorisS" wrote in message
...
I have an annoying problem that keeps coming up. I have sheets and
sheets
of
charts (we produce about 400 of them every quarter, distributed over 2
files). I have the charts linked to certain other files, which get
updated
regularly. For some reason every time I am using the chart file a new
quarter, a few of the charts link to old files that are no longer the
active
ones. I change the links, save new copies of all (and I do it in the
order
that is necessary for the chart files to remember the new names, so I
know
that's not the problem), and then am done with it. At this point, it is
happening, and the way I find the links is just to go through each chart
and
click on the items until I see the name in the formula bar that is an
old
file name. The I know I just need to update that link to the new file.
Is
there any code I can use that would effectively:

1) search each sheet of the workbook
2) on any sheet, search the source links for every input of each chart
on
the worksheet
3) if it's got a particular string in the links, tell me (pause on it,
write
down the chart title, anything).

This way I could just run the macro to know where I need to go, as
opposed
to clicking through the visible data items of all the charts each time.

Thx.
--
Boris

No, thanks. That's fantastic help.
--
Boris


"Peter T" wrote:

Forgot to mention, the example might not return full path's of links if
source workbooks are open.

Peter T

"Peter T" <peter_t@discussions wrote in message
...
I don't quite follow what you want to do, why not simply Edit Links from
the
Edit menu. Or maybe you only want to selectively change links in some
charts.

Following should produce something for your "1,2,3".

Option Explicit
Sub DumpSeries()
Dim wb As Workbook
Dim oSht As Object
Dim cob As ChartObject
Dim i As Long, j As Long
Dim rng As Range

ReDim vArr(1 To 5, 1 To 1)
vArr(1, 1) = "SHEET"
vArr(2, 1) = "CHART"
vArr(3, 1) = "TITLE"
vArr(4, 1) = "SERIES"
vArr(5, 1) = "FORMULA"

Set wb = ActiveWorkbook

For Each oSht In wb.Sheets
For Each cob In oSht.ChartObjects
SeriesFormulas cob.Chart, vArr, oSht.Name
Next
Next

For Each oSht In wb.Charts
SeriesFormulas oSht, vArr, oSht.Name
Next

On Error Resume Next
Set oSht = Nothing
Set oSht = Worksheets("MyCharts")
If Err.Number Then
Set oSht = wb.Worksheets.Add
oSht.Name = "Mycharts"
Else
'delete old listing ?
oSht.Cells.ClearContents
oSht.Activate
End If

On Error GoTo 0

Set rng = Range("a1:E" & UBound(vArr, 2))

'xl2002 & later
'rng.Value = Application.WorksheetFunction.Transpose(vArr)

'transpose might error in xl2000 with 5000+ elements in the array
ReDim varr2(1 To UBound(vArr, 2), 1 To 5)
For i = 1 To UBound(varr2): For j = 1 To 5
varr2(i, j) = vArr(j, i)
Next: Next
rng.Value = varr2

End Sub

Function SeriesFormulas(cht As Chart, va(), sShtName As String)
Dim cnt As Long
Dim i As Long
Dim sr As Series

cnt = cht.SeriesCollection.Count

i = UBound(va, 2)

ReDim Preserve va(1 To 5, 1 To i + cnt)
va(1, i + 1) = sShtName
va(2, i + 1) = cht.Name
If cht.HasTitle Then va(3, i + 1) = cht.ChartTitle.Text

For Each sr In cht.SeriesCollection
i = i + 1
va(4, i) = sr.Name
'can't dump series formula starting with = in a cell, so prefix it
va(5, i) = "#" & sr.Formula
Next

End Function

Wouldn't take too much to adapt to replace links on a selective basis.
Note
the construct
' path [ name.xla ] sheet ! ' address

Regards,
Peter T


"BorisS" wrote in message
...
I have an annoying problem that keeps coming up. I have sheets and
sheets
of
charts (we produce about 400 of them every quarter, distributed over 2
files). I have the charts linked to certain other files, which get
updated
regularly. For some reason every time I am using the chart file a new
quarter, a few of the charts link to old files that are no longer the
active
ones. I change the links, save new copies of all (and I do it in the
order
that is necessary for the chart files to remember the new names, so I
know
that's not the problem), and then am done with it. At this point, it is
happening, and the way I find the links is just to go through each chart
and
click on the items until I see the name in the formula bar that is an
old
file name. The I know I just need to update that link to the new file.
Is
there any code I can use that would effectively:

1) search each sheet of the workbook
2) on any sheet, search the source links for every input of each chart
on
the worksheet
3) if it's got a particular string in the links, tell me (pause on it,
write
down the chart title, anything).

This way I could just run the macro to know where I need to go, as
opposed
to clicking through the visible data items of all the charts each time.

Thx.
--
Boris