-
Jan 23rd, 2022, 02:52 PM
#1
Thread Starter
Lively Member
Keeping excel opened during project utilization
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:
Code:
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.
-
Jan 23rd, 2022, 07:34 PM
#2
Re: Keeping excel opened during project utilization
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.
-
Jan 24th, 2022, 06:41 AM
#3
Thread Starter
Lively Member
Re: Keeping excel opened during project utilization
Dear jmcilhinney,
thanks a lot.
I've followed your suggestion and added the following pieces of codes in the Startup and Shutdown events:
Code:
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.
-
Jan 24th, 2022, 08:24 AM
#4
Re: Keeping excel opened during project utilization
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
-
Jan 24th, 2022, 02:56 PM
#5
Thread Starter
Lively Member
Re: Keeping excel opened during project utilization
Dear tg,
thanks for your suggestion.
Here what I've done:
- Added in my Namespace the following module:
Code:
Public Module Globals
Public xlApp As Excel.Application
Public xlWorkBook As Excel.Workbook
End Module
- Added in the Startup event the following code:
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:
Code:
My.xlWorkBook.Close(SaveChanges:=True)
My.xlApp.Quit()
- To activate a specific sheet of the excel, added the following code in a form:
Code:
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.
-
Jan 24th, 2022, 08:55 PM
#6
Re: Keeping excel opened during project utilization
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|