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.

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

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
>

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