Finding links in a workbook

[RichardMGreen]

Hi all

I've got a template workbook which contains loads of formulas which are updated daily.
I then have a macro which exports most of the sheets and converts them to values only to produce an output file.

When I open the output file to check it, I get a message asking if I want to update the links (which I can't find and shouldn't be there).

How can I find where the link is so I can get rid of it?
I have this line of code in my macro:-
wb.BreakLink Name:=Template, Type:=xlExcelLinks

which forces an error.

I've checked all the sheets to see if there's any linked tables or cells referring to my original workbook but I can't find anything.

Any help greatly appreciated.

::edit::

Just to add, there's no links in my template workbook so it's definitely something peculiar in the output file.

 

[VoG]

Try this http://j-walk.com/ss/excel/usertips/tip054.htm

 

[RichardMGreen]

Thanks Peter, I'll work through that and see what I come up with.

 

[GlennUK]

The FindLink add-in by Bill Manville might help:

http://www.oaltd.co.uk/mvp/MVPPage.asp

 

[RichardMGreen]

I've got rid of most of the links (most were named ranges that aren't actually used anywhere so they've been deleted).
I now have the problem of chart information.
How can I remove the path and filename forom each of the chart series, series names and axis details on each sheet after the worksheets have been copied to a new book?

I'm fairly certain this can be done through code (I'll need to add it as part of my export macro) but don't know where to start.

Would someone be kind enough to point me in the right direction?

Thanks in advance.

 

[RichardMGreen]

OK, I've recorded a macro where the links for the charts are changed to remove the workbook reference which gave me this:-

Sub Macro1()
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = "=ChartData32Days!R3C117:R34C117"
    ActiveChart.SeriesCollection(1).Name = "=ChartData32Days!R2C17"
    ActiveChart.SeriesCollection(2).XValues = "=ChartData32Days!R3C1:R34C1"
End Sub

I tried putting into a loop like this:-

Sub changer()
    Dim ws As Worksheet, ch As ChartObject, scoll As SeriesCollection
    For Each ws In Worksheets
        For Each ch In ws.ChartObjects
            ch.Activate
            For counter = 1 To ActiveChart.SeriesCollection.Count
                ActiveChart.SeriesCollection(counter).Values = "=" & Right(Len(ActiveChart.SeriesCollection(counter).Values) - 199, 255)
                ActiveChart.SeriesCollection(counter).Name = "=" & Right(Len(ActiveChart.SeriesCollection(counter).Name) - 199, 255)
                ActiveChart.SeriesCollection(counter).XValues = "=" & Right(Len(ActiveChart.SeriesCollection(counter).XValues) - 199, 255)
            Next
        Next
    Next
End Sub

but it falls over at the first activechart line with a type mismatch error.

Anyone know where I'm going wrong?

 

[GlennUK]

Did you try the FindLink add-in? Did that not work for the charts?

 

[RichardMGreen]

I can find the links, I just need to edit them as they can't be fixed values.
When I export from the template to the real file, the chart formulas keep the path and filename of the template instead of updating to the new file, even though I'm copying the data sheet over as well.

 

[GlennUK]

Shouldn't you be doing a Cut, rather than a Copy?

 

[RichardMGreen]

I'm using a worksheet copy to create the output file.