Referencing an excel sheet from a Windows Form in

Question

I thought I'd try to learn VB.net and start with some VSTO (VS2012). I'm trying to do an Excel Document Customization with a separate Windows Form in it (.show on load). I can get the form to open on loading the doc. I found lots of examples of how to put Windows Form Controls in the document on MSDN (and figured out how to do that), but I'm having trouble referencing parts of the Excel document from the Windows Form.

So for example on the windows form called Main Control I tried to add this sub to a button:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Try
        Dim baba As Sheet1
        baba.Cells(1, 2).Value = "Llaslmasd"
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
 End Sub

I also tried:

        Dim baba As New Sheet1

and

        Dim baba As WorkSheet
        baba = Sheet1

What is the correct way to reference the sheet if it is not a shared member, or if I should make it shared, how do I do that?

Will things break if I programatically try to insert sheets from other (non-incorporated workbooks and therefore change sheet index?).

Sorry if my English is wrong or my formatting of the question is bad, I'm still going over examples.

Answer 1

It's much easier than calling for the index. In a document level add-in, the sheets are going to be under Globals, so you just have to fully qualify what you are trying to do.

Globals.Sheet1.Cells(1, 2).Value = "Llaslmasd"

Should work.

Answer 2

I believe the best way is to get sheets from the excel application.

Dim WB as Workbook
WB = Application.Workbooks(IndexOrName) 'By "Application" I mean the Excel Application object.

Dim WS as Worksheet
WS = WB.Worksheets(IndexOrName)

To add sheets:

WB.Worksheets.Add(...)