How to Change Source Data after copy sheets from another workbook?

[davy]

I have two wookbooks, I call them workbook_A and workbook_B. workBook_B has a
chart in sheet1 and its source data is from sheet2. When I copy the two
sheets from workBook_B into workBook_A:

workBook_B.Worksheets.Copy(MissingValue, workBook_A.Sheets.get_Item(2));

After Copy, the Source data of chart in workBook_A still link to the
outside original workbook: workBook_B. Any friend can tell me how I can keep
the source data inside a workbook after copying? cheers.

 

[Dave Peterson]

I think you have a couple of choices.

You can let the formulas point back at the original workbook and then do
Edit|Links|change source (record a macro if you need code).

Or you could convert the formulas to strings, copy the worksheet, and convert
those strings back to formulas.

I like to do this (for each sheet):

Select all the cells
edit|Replace
what: = (equal sign)
with: $$$$$=
replace all

Then the copy.

And reverse the process (for each sheet in both the sending and receiving
workbooks)

Select all the cells
edit|Replace
what: $$$$$=
with: =
replace all

Recording a macro when you do it manually will give you a good start on the
code.

 

[davy]

Thanks Dave Peterson, I'm new to VSTO, it will take some time to examine our
solutons, if you can give a simple example in c#, that would be perfect!
cheers.

 

[Dave Peterson]

Not from me!

I don't speak any flavors of C.

Sorry.