Keeping excel opened during project utilization

[AndreB82]

Dear Gurus,
I have a project which I'm working on that needs to have an excel file opened during its execution.
Now, I have the following code which is running on each single form:

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlApp.Visible = False
        xlWorkBook = xlApp.Workbooks.Open("C:\Users\Public\Documents\Modello.xlsm", [ReadOnly]:=False, Editable:=True)

....

xlWorkBook.Close(SaveChanges:=True)
xlApp.Quit()

Is it possible to place the code just once, so that the first part will be executed at the loading of the project (and then I just have to activate the worksheets that I need from time to time), and the last two rows just when I close the project?

Thanks in advance for your help,
A.

[jmcilhinney]

Open the project properties and then open the application events. The Startup event is raised when the applications starts up and the Shutdown event is raised when the application shuts down.

[AndreB82]

Dear jmcilhinney,
thanks a lot.

I've followed your suggestion and added the following pieces of codes in the Startup and Shutdown events:

Imports Microsoft.VisualBasic.ApplicationServices
Imports Excel = Microsoft.Office.Interop.Excel

Namespace My
    ' The following events are available for MyApplication:
    ' Startup: Raised when the application starts, before the startup form is created.
    ' Shutdown: Raised after all application forms are closed.  This event is not raised if the application terminates abnormally.
    ' UnhandledException: Raised if the application encounters an unhandled exception.
    ' StartupNextInstance: Raised when launching a single-instance application and the application is already active. 
    ' NetworkAvailabilityChanged: Raised when the network connection is connected or disconnected.
    Partial Friend Class MyApplication
        Private Sub MyApplication_Startup(sender As Object, e As StartupEventArgs) Handles Me.Startup

            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook

            xlApp = New Excel.Application
            xlApp.Visible = False
            xlWorkBook = xlApp.Workbooks.Open("C:\Users\Public\Documents\Modello.xlsm", [ReadOnly]:=False, Editable:=True)

        End Sub

        Private Sub MyApplication_Shutdown(sender As Object, e As EventArgs) Handles Me.Shutdown
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook

            xlApp = New Excel.Application
            xlWorkBook.Close(SaveChanges:=True)
            xlApp.Quit()

        End Sub
    End Class
End Namespace

but the following doubts came to my mind:
- in the Shutdown event related code xlWorkbook parameter is not recognized;
- when I try to access the excel file from other subroutine how can I refer to that specific opened file? I've tried initializing a generic xlWorkSheet variable, but when executing the code it throws me a system error (System.NullReferenceException).

Sorry for this - it is the very first time I'm working with this condition.

Thanks in advance for your support,
A.

[techgnome]

Do you understand the concept of variable scope? That's what's going on here. You're defining variables in a sub and expecting them to be available elsewhere. That's like making a sandwich at your friends house, then going home and expecting to have that same sandwich available to you ... no... it's at your friends house. That's why you had to re-define your xlApp a second time in the shutdown event ... because it is blissfully unaware of any other declarations. Meanwhile your original variables fell out of scope and no longer exist once the Startup event handler completed.
So your variables, like your sandwich you want later, need to be put somewhere where you can get to them. This can be done in one of two ways. Add a module and declare them as global variables (I'd use Friend, but Public also works) ... you can then "just use them" anywhere in the code after that. Or, you can define them right there at the top of your Namespace. To access them after that in the code you'd need to preface it with "My." so it would be "My.xlApp"

At this point, I might consider creating a class that moves all of this logic out of the app and into a self-contained class of some sort. But that might be over thinking things too ... there's something to be said for simplicity.

-tg

[AndreB82]

Dear tg,
thanks for your suggestion.

Here what I've done:
- Added in my Namespace the following module:

Public Module Globals
        Public xlApp As Excel.Application
        Public xlWorkBook As Excel.Workbook
End Module

- Added in the Startup event the following code:

xlApp = New Excel.Application
xlApp.Visible = False
xlWorkBook = xlApp.Workbooks.Open("C:\Users\Public\Documents\Modello.xlsm", [ReadOnly]:=False, Editable:=True)

- Added in Shutdown event the following code:

My.xlWorkBook.Close(SaveChanges:=True)
My.xlApp.Quit()

- To activate a specific sheet of the excel, added the following code in a form:

Dim xlWorkSheet As Excel.Worksheet

xlWorkSheet = My.xlWorkBook.Worksheets("Anagrafica")
xlWorkSheet.Activate()
tRows = xlWorkSheet.ListObjects("Tbl_Anagrafica").DataBodyRange.Rows.Count
Me.LabelGioOss.Text = tRows.ToString

Please let me know if you find any error in this or if I'm in the right direction.

Thanks,
A.

[jmcilhinney]

Get rid of the module. It's pointless. Just declare your variables outside the event handlers. Variables declared outside any method are accessible within all methods in that type.

Public fields in that MyApplication class are also accessible anywhere in the project via My.Application.FieldNameHere.