Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Hi,
I wish to copy several worksheets from one workbook to another using VB.NET interop code. I have recorded the actions in a Excel macro to view the code and it seems straight forward. However, if I repeat the code in VB.NET, after the worksheets have been copied any references to named ranges are corrupt, they don't even reference the original workbook. Is there any way within Excel or in code to reset the named ranges to reference the workbook the worksheets where copied to. Sample Copde: SourceWorkbook.Worksheets.Copy(,TargetWorkbook.Wor ksheets.Item(TargetWorkbook.Worksheets.Count)) Regards Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Hi Tim,
I'd suggest you can use the code below to perform the copy operation. '--------------------------------------------------------------------------- -------- Imports nsOff = Microsoft.Office.Core Imports nsExcel = Microsoft.Office.Interop.Excel '... Dim xl As New nsExcel.Application 'specify the source workbook Dim owb As nsExcel.Workbook = xl.Workbooks.Open("G:\OfficeTest\psd.xls") 'specify the target workbook Dim oTargetWb As nsExcel.Workbook = xl.Workbooks.Open("G:\OfficeTest\scatter.xls") owb.Worksheets.Copy(oTargetWb.Worksheets("sheet3") , ) oTargetWb.Save() xl.Quit() '--------------------------------------------------------------------------- -------- Please feel free to let me know if you have any question. Best Regards, Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Thanks For the reply,
However, I understand the code below and I am able to copy worksheets between workbooks, but If the worksheets contain references to named ranges these become currupt. This happens even if I copy all the worksheets in a workbook, individually, as an array or all the worksheet collection. Tim "Wei-Dong XU [MSFT]" wrote in message ... Hi Tim, I'd suggest you can use the code below to perform the copy operation. '--------------------------------------------------------------------------- -------- Imports nsOff = Microsoft.Office.Core Imports nsExcel = Microsoft.Office.Interop.Excel '... Dim xl As New nsExcel.Application 'specify the source workbook Dim owb As nsExcel.Workbook = xl.Workbooks.Open("G:\OfficeTest\psd.xls") 'specify the target workbook Dim oTargetWb As nsExcel.Workbook = xl.Workbooks.Open("G:\OfficeTest\scatter.xls") owb.Worksheets.Copy(oTargetWb.Worksheets("sheet3") , ) oTargetWb.Save() xl.Quit() '--------------------------------------------------------------------------- -------- Please feel free to let me know if you have any question. Best Regards, Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Hi Tim,
Have you tried my suggestion, if you still have any concern on this issue ,please feel free to post here. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Hi Tim,
I also have tried to make a test with a workbook which has a reference to a named range. Here is my test result. 1. If the reference refer to named range on the the same sheet, the reference will still work e.g. Book1.xls!Sheet1:A1 refer to namedrange(Book1.xls!Sheet1:B1) ,after copy the reference will still work 2. If the reference refer to named range on another sheet, the reference will be kept. e.g. Book1.xls!Sheet1:A1 refer to namedrange(Book1.xls!Sheet2:B1) ,after copy the reference will work depend on the two scenarios as below. 2.1 The Book1 is at the orignal position we did not move it to the new position, in this way, the reference will still work, and when you open the book2.xls, the excle will ask you if you want to update the reference. 2.2 The Book1 have been removed or moved to another position, the update will fail and the reference will break. If I have any misunderstanding, please feel free to correct me. Also please provide sample test xls files and detailed reproduce steps so that we can figure out the problem. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Hi,
Did my reply help you? If you still have any concern on this issue, please feel free to post here. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Thanks Peter,
I have a bit of work to do to resolve my issues, I will post my finding here. Tim ""Peter Huang"" wrote in message ... Hi, Did my reply help you? If you still have any concern on this issue, please feel free to post here. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets
Hi Tim,
Thank you for your efforts, I think that will benifit the whole community. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying worksheets? | Excel Worksheet Functions | |||
Copying Worksheets | Excel Worksheet Functions | |||
copying to worksheets | Excel Discussion (Misc queries) | |||
Copying Worksheets | Excel Discussion (Misc queries) | |||
Copying worksheets | Excel Worksheet Functions |