Recently I answered a question regarding how to copy several sheets from one workbook to another. I then realized that it may be of common interest to publish it here.
Beside that I’m happy to post an article related to code.
Here it is assumed that we automate Microsoft Excel where we open two workbooks, the source and the target workbooks, and copy the wanted sheets to the target workbook.
The key to the solution is that Microsoft Excel expects an array of sheet names declared as Object. As Yoy know, when working with worksheets we need to cast them to objects before we can access them and work with them.
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\Dennis Wallentin\Documents\Source.xlsx")
Dim wbTargetBook As Excel.Workbook = xlApp.Workbooks.Open _
("C:\Users\Dennis Wallentin\Documents\Target.xlsx")
'Excel expects to receive an array of objects that
'represent the worksheets to be copied or moved.
Dim oSheetsList() As Object = {"A", "D"}
wbSourceBook.Sheets(oSheetsList).Copy(After:=wbTargetBook.Worksheets(1))
wbTargetBook.Save()
With xlApp
.Visible = True
.UserControl = True
End With
'Don't forget to clean up properly.
End Sub
End Class
If anyone face any problem with this approach please let me know.
Enjoy!
Kind regards,
Dennis
VSTO & .NET & Excel 