vba change charts to reference new workbook

[libertynance]

Hi! I need to move a page of 30 charts and its reference page into a new workbook, then change the charts' references to see the the new workbook.

Due to the way this will be deployed, I can't save the workbook so can't re-direct links. I also have all charts referencing different parts of a huge dataset, so named ranges would be a huge pain.

Is there an easy way to search and replace the old file name with null so that the current workbook becomes the reference?

Thanks!!!
lw

p.s. this is excel 2003, windows XP

 

[libertynance]

***If anyone has a more elegant solution, PLEASE post! I stitched this together from what I could learn googling the internet and in this site! ***

Well, I figured out how to do it, but I had to end up saving the workbook. In case anyone's interested, here's how I did it:

I found that if you export only the graphs and the source data and paste values on both sheets, then save, that this should break most of the links to the old workbook. In the case that there are still a *few* random links (test by going to Edit | Links and redirect your source to the newly saved workbook...if you only have a few errors to enter through, then this should work, otherwise it gets hung and crashes).

Then to redirect the final few links run through the changelinks command, but use variables for the naming convention.

Then you can move other sheets into the workbook as needed using the variable reference for the name. I found that moving all worksheets in together created too many links that needed to be resolved.

Here's the final code:

Sub aaa()

WName = ActiveWorkbook.Name
Application.ScreenUpdating = False

Sheets(Array("Trends", "Graph_Source")).Select
Sheets("Graph_Source").Activate
Sheets(Array("Trends", "Graph_Source")).Copy

Application.ScreenUpdating = False
Sheets("Graph_Source").Select
Range("HD1").Select
db_dt = ActiveCell.Value

TName = ActiveWorkbook.Name
Workbooks(TName).Activate
Sheets("Graph_Source").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D9").Select
Application.CutCopyMode = False

Sheets("Trends").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G1").Select

ActiveWorkbook.SaveAs Filename:= _
"C:\Program Files\Cognos\Impromptu 5.0\User Workspace\" & db_dt & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
NName = ActiveWorkbook.Name
ActiveWorkbook.ChangeLink Name:=WName, _
NewName:=NName, Type:=xlExcelLinks

ActiveWorkbook.Save

Workbooks(WName).Activate
Sheets("Main").Select
Sheets("Main").Copy Before:=Workbooks(NName).Sheets(1)
Workbooks(NName).Activate
Sheets("Main").Select
Range("A1:BZ300").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Graph_Source").Name = "GS " & db_dt
Range("A1").Select

Sheets("Trends").Name = "Trends " & db_dt
Range("A1").Select

Sheets("Main").Name = "Main " & db_dt

ActiveWorkbook.Save

Workbooks(WName).Activate
Sheets("Main").Select
Range("D13").Select
Application.ScreenUpdating = True
End Sub