Public Variables with windows forms

Posted on 2007-08-08
Last Modified: 2013-11-27
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
Question by:tiehaze
    LVL 85

    Accepted Solution


        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...

    Author Comment

    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?
    LVL 85

    Expert Comment

    by:Mike Tomlinson
    I have no idea sorry...I don't work with Excel or bound data...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    In my previous two articles we discussed Binary Serialization ( and XML Serialization ( In this article we will try to know more about SOAP (Simple Object Acces…
    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now