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

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.

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.