Public Variables with windows forms

I have two windows forms. The first windows form has a tool strip that allows the user to open an excel file. How do I set the excel file as a public variable that I can use from any windows form? Here is what I have to open the excel file:

    Protected oExcel As Object
    Protected oBook As Object
    Protected oSheet As Object
    Protected MainDirectory As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetAssembly(Me.GetType()).Location)
    Protected HoldingReportsPath As String = MainDirectory & "\Holdings Reports\"

Private Sub OpenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenToolStripMenuItem.Click
        Dim myStream As IO.Stream = Nothing
        Dim openFileDialog1 As New OpenFileDialog()
        openFileDialog1.InitialDirectory = HoldingReportsPath
        openFileDialog1.Filter = "excel files (*.xls)|*.xls|All files (*.*)|*.*"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True
        If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim fileName As String = openFileDialog1.FileName
                myStream = openFileDialog1.OpenFile()
                If (myStream IsNot Nothing) Then
                    oExcel = CreateObject("Excel.Application")
                    oBook = oExcel.Workbooks.Open(fileName)
                    oSheet = oBook.Sheets("Sheet1")
                End If
            Catch Ex As Exception
                MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
                ' Check this again, since we need to make sure we didn't throw an exception on open.
                If (myStream IsNot Nothing) Then
                End If
            End Try
        End If
    End Sub
Who is Participating?
Mike TomlinsonMiddle School Assistant TeacherCommented:

    Protected oExcel As Object
    Protected oBook As Object
    Protected oSheet As Object


    Public Shared oExcel As Object
    Public Shared oBook As Object
    Public Shared oSheet As Object

Then you can access these values from anywhere using the forms name:

    Form1.oExcel = something

replacing "Form1" with the name of your "first" form.

Or you can simply add a Module to your project and declare those variables as Public there.  Then they will be accessible from anywhere...
tiehazeAuthor Commented:
Continuing with the above example.. In the second windows form, I have a datagridview. When the second windows form is loaded, I want the datagridview to load in the first seven columns of data. The problem is the total number of rows is variable. Additionaly, how to I set up binding so that if any information is changed in the datagridview, it will change it on the excel file?
Mike TomlinsonMiddle School Assistant TeacherCommented:
I have no idea sorry...I don't work with Excel or bound data...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.