Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy last data entered into mulitiple sheets within a workbook? | Excel Discussion (Misc queries) | |||
Change source data to all sheets | Excel Programming | |||
Copy tabs(sheets) from workbook without link to original source | Excel Discussion (Misc queries) | |||
Copy tabs(sheets) from workbook without link to original source | Links and Linking in Excel | |||
copy data in one sheet to multiple sheets in same workbook | Excel Worksheet Functions |