Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Go Back  Xtreme Visual Basic Talk > > > Copy Multiple sheets to another workbook


Reply
 
Thread Tools Display Modes
  #1  
Old 09-20-2011, 03:43 PM
pells pells is offline
Newcomer
 
Join Date: Jul 2004
Posts: 17
Default Copy Multiple sheets to another workbook


I have 2 sheets in a workbook that have links on them. I need to copy both sheets into this new workbook at the same time to update the links and not have sheets looking for the original workbook (it just temporary and deleted after the copy). I thought I had it, but I cannot figure out how to deal with the array that is needed (I copied the code from macro builder in excel). Any ideas (code below) and THANKS.

oExcel.Application.Windows("EP Labor Calculator.xlsm").Activate()
oExcel.Application.Sheets(Array("Rates", "Labor Calc")).Select()
oExcel.Application.Sheets(Array("Labor Calc", "Rates")).Copy(Before:=oExcel.Application.Workbooks("SAP Test.xlsx").Sheets(5))

I know I am close because I can do one sheet at a time no problem.
Reply With Quote
  #2  
Old 09-22-2011, 07:43 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

Yes, You are close but the main issue is that 'Array' in VB.NET does not refer to VBA.Array in Excel.

Excel expect an array of objects and the following code should fix it for You:

Code:
Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application = New Excel.Application Dim wbSourceBook As Excel.Workbook = xlApp.Workbooks.Open("C:\Users\DS\Documents\Source.xlsx") Dim wbTargetBook As Excel.Workbook = xlApp.Workbooks.Open("C:\Users\DS\Documents\Target.xlsx") Dim oSheetsList() As Object = {"A", "D"} wbSourceBook.Sheets(oSheetsList).Copy(After:=wbTargetBook.Worksheets(1)) With xlApp .Visible = True .UserControl = True End With 'Don't forget to clean up properly. End Sub End Class
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
 
Copy Multiple sheets to another workbook
Copy Multiple sheets to another workbook
 
-->