Copying Worksheets

[Tim Marsden]

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.Worksheets.Item(TargetWorkbook.Worksheets.Count))

Regards
Tim

 

[Wei-Dong XU [MSFT]]

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.

 

[Tim Marsden]

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

 

[Peter Huang]

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.

 

[Peter Huang]

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.

 

[Peter Huang]

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.

 

[Tim Marsden]

Thanks Peter,

I have a bit of work to do to resolve my issues, I will post my finding
here.

Tim

 

[Peter Huang]

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.