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

Advertisements

September 14, 2011

MSDN English Forum Assistant (Gadget)

Filed under: .NET & Excel, Excel, Search Tools, Tools — Dennis M Wallentin @ 5:20 pm

During the summer Microsoft released a new tool, MSDN English Forum Assistant. It’s a gadget tool and it provides us with a shortcut to MSDN forums, i.e. we don’t need to use any web browser. The tool targets all visitors and makes it easy to ask questions, monitor threads or forums, access FAQs and search for knowledge.

I have been using it since June 2011 and I find it quite useful and easy to use. So I suggest that You at least checkup this free tool. (It reminds me about the nice utility we can use at Expert Exchange (EE) – QuickEE).

But since the tool only target MSDN forums it does not cover the native MS Excel Q&A forums. The forums included in MSDN forums that target MS Excel are; Excel for Developer, VBA and VSTO.

The first screen shot below shows the start page of the gadget (on the desktop):

The next screen shot shows the settings we can do:

The below screen shot shows the list of forums (I have selected) and where I can ask questions in. After selecting the forum the tool opens a page (in the web browser) and I can write my question.

Finally, the below screen shot shows the list of forums’s FAQs. What is noteable is that this list of FAQs is fixed and we cannot manipulate it. When selecting one FAQ the web browser opens the FAQ’s page:

If You are still interested You can find more information and download at:

MSDN English Forum Assistant (Gadget)

Kind regards,
Dennis

Blog at WordPress.com.