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

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.

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

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 wrote:

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

How to Change Source Data after copy sheets from another workb
 

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" wrote:

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 wrote:

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

How to Change Source Data after copy sheets from another workb
 

Not from me!

I don't speak any flavors of C.

Sorry.

davy wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson