VSTO & .NET & Excel

September 22, 2011

Copy Worksheets with VB

Filed under: .NET & Excel — Dennis M Wallentin @ 4:07 pm

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

About these ads

1 Comment »

  1. Hi Friend, Awesome blog. I will post a link on my tumblr page.

    Comment by Patrice — October 10, 2011 @ 6:00 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers

%d bloggers like this: