Public Variables with windows forms

Posted on 2007-08-08
Medium Priority
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
  • 2
LVL 86

Accepted Solution

Mike Tomlinson earned 2000 total points
ID: 19656430

    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

ID: 19656490
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 86

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

850 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