CODEWITHSUNDEEP
vb.netinteropexcel-2010
ruedi
ruedi

Reputation: 5555

How to instance a worksheet with interop?

I am instance a Worksheet Object with Interop. The way MSDN explains it is:

xlsApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
xlsWB = xlsApp.Workbooks.Add
xlsWS = DirectCast(xlsApp.Worksheets(1), Excel.Worksheet)

This works properly but I still have two questions about it. Is it right, that xlsApp.Workbooks.add creates an Excel.Workbook and not an Object, since I dont have to Cast it into a Workbook as I have to do it with Application, Worksheet and Range?

Second. I was wondering why they use xlsApp.Worksheets(1) to generate a new worksheet. Why not using xlsWB.Worksheets(1) since it belongs to xlsWB (in my opinion). xlsWB.Worksheets works propertly as well, so is there any difference and why is xlsApp.Worksheets(1) even working?

The codeline would change to:

xlsWS = DirectCast(xlsWB.Worksheets(1), Excel.Worksheet)

EDIT: My variable declaration:

Private xlsApp As Excel.Application = Nothing
    Private xlsWB As Excel.Workbook
    Private xlsWS As Excel.Worksheet

Upvotes: 0

Views: 311

Answers (1)

Hans Passant
Hans Passant

Reputation: 942267

The Excel automation object model has some redundancy, allowing you to get the job done in more than one way. Be sure to read the MSDN documentation whenever you have a question like this. You'll see that the Application.WorkSheets says:

Returns a Sheets collection that represents all the worksheets in the active workbook

Relevant phrase highlighted. Probably works fine in your code because the spreadsheet has only one WorkBook, the one you added. So there is little risk of getting the wrong sheet. That is not necessarily true if you open a spreadsheet that has multiple work books of course. Or if your code interacts with the user actively editing the spreadsheet, a case where you might actually favor using the active workbook. You should probably favor the WorkBooks.WorkSheets property, no accidents that way.

The need for casts is also quite visible from the MSDN documentation. If you click through the Excel.Sheets links to arrive at the Sheets.Item property (the default property, you don't name it in your code) then you see it returns Object. Pretty common in the Office Automation interfaces, plenty of accessors that can return different kind of interfaces, depending the flavor of object you select. So casting from Object to Worksheet is necessary for that property.

And no need to cast when you use WorkBooks.Add(), it unambiguously only ever returns the WorkBook interface.

Do keep in mind that you are using VB.NET, a language that was optimized to make writing this kind of dynamic code easy. The technical name for it is late binding, discovering the members of an interface at runtime instead of compile time. It does have disadvantages, much easier to make a mistake in your code that you won't discover until runtime, like misspelling a member name. Option Strict On disables late binding and you'll have to cast explicitly. Unfortunately VB.NET lost the ability to switch back-and-forth between early and late binding on the fly, a victim of the added support for type inference, it doesn't have anything similar to the C# v4 dynamic keyword.

Upvotes: 3

Related Questions