Charts still linked to old book.

[Fan924]

Excel 97. Macro copies data and a dozen charts to a new workbook.
Charts still linked to old book. Can I change book and sheet to the
new sheet automatically? Search and replace?

 

[Guest]

Normal Search & replace will let you replace the workbook references in
formulae, but not in the chart series. You need to do it in the macro code -
but I'm assuming that's not a problem for you since you've posted in an Excel
Programming newsgroup and you've said you've got a macro already.

I'm using Excel 2003. VBA probably hasn't changed a lot since 97, but
there's a chance my code won't work.
This is a complete sub - I suggest you grab the bits you want and add to
your existing code. You also need to change the filenames (just under the dim
statements) to match yours. If you already have variables for these in your
code, then replace my oldBook and newBook variables with the variables you've
already got.
I'm not sure, but I think if there are formulae in the chart titles then
they do not get "fixed", but it should work on the chart series.
-------------------------------------------
Sub RemoveLinks()
'
' RemoveLinks Macro
' Cringing Dragon 21/10/2007
'
Dim oSheet As Worksheet
Dim oChart As Object
Dim oSeries As Series
Dim newBook As String
Dim oldBook As String

' Replace these with YOUR workbook names
newBook = ActiveWorkbook.Name
oldBook = "Book2"

' Turn error checking off so it doesn't stop if any of the values can't be
changed
On Error Resume Next

' This loop checks all worksheets
For Each oSheet In Workbooks(newBook).Worksheets
' Remove links in cell formulae
oSheet.Cells.Replace What:="[" & oldBook & "]", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' This loop checks all embedded charts on the sheet
For Each oChart In oSheet.ChartObjects
' This loop checks all series in the chart
For Each oSeries In oChart.Chart.SeriesCollection
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]",
"")
Next oSeries
Next oChart
Next oSheet

' This loop checks all chart sheets
For Each oChart In Workbooks(newBook).Charts
' This loop checks all series in the chart
For Each oSeries In oChart.SeriesCollection
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "")
Next oSeries
Next oChart

' Turn normal error handling back on
On Error GoTo 0

End Sub

 

[Fan924]

Thanks. 97 does not like the command "Replace". Sub or function not
defined. Is ther an excel 97 equivalent? anyone?

 

[Doug Glancy]

fan924,

Try Application.Worksheetfunction.Substitute 'use the parameters as they
work in the worksheet Substitute function

hth,

Doug

 

[Guest]

If it's only the Replace function that gives errors - ie the lines like:
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "")
then try using WorksheetFunction.Replace instead:
oSeries.Formula = WorksheetFunction.Replace(oSeries.Formula, "[" & oldBook
& "]", "")

But don't make that change on the line that uses the Replace Method:
oSheet.Cells.Replace....

 

[Guest]

Sorry, Doug beat me to it and his solution's better. I think the syntax for
worksheetfunction.replace is slightly different, so mine wouldn't work anyway.

If it's only the Replace function that gives errors - ie the lines like:
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "")
then try using WorksheetFunction.Replace instead:
oSeries.Formula = WorksheetFunction.Replace(oSeries.Formula, "[" & oldBook
& "]", "")

But don't make that change on the line that uses the Replace Method:
oSheet.Cells.Replace....

Thanks. 97 does not like the command "Replace". Sub or function not
defined. Is ther an excel 97 equivalent? anyone?

 

[Guest]

A better way to change your links would be:

ActiveWorkbook.ChangeLink _
Name:="OldBookName.xls", _
NewName:="NewBookName.xls", _
Type:=xlExcelLinks

as an example.

If you want to use application.Substitute in that code to actually come up
with the name of the link, that is fine as well.



However, I suspect the best solution would be something like this

bk1.Worksheets(Array("Data1","Chart1","Chart2")).copy
After:=workbooks("Mybook.xls").Worksheets(1)

by copying all the sheets as a group, the won't create links referring back
to the old workbook (as long as all cells referenced are in the group of
sheets copied.

 

[Peter T]

Just to note both suggested methods will require data is copied to identical
SheetName!Cell-Ref locations in the new workbook. Also there are other
potential links in charts besides series formulas.

Regards,
Peter T