If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Using Excel with VB.net

For all of you that have been having trouble getting VB.net to work right with the Excel interops it can be a bit of a bear so I will post the code I got made to get it to work right. It is a simple example but it may help som people

Gurpreet, your code works fine for me. Created a simple form with your code run from a button. Code runs, excel opens, code completes. There is no instance of excel running. Could you be instantiating it somewhere else in your application?

Unless you really know what you are doing, do not call the garbage collector (the lines of code calling System.GC) in your applications.

The garbage collector is a complex thing and calling it can have effects you did not want.

First of all, it is used to control managed objects (.NET objects). Excel being an ActiveX object, it is not cleared from memory when you call GC.Collect. In fact, doing so can have a detrimental effect. Because GC does not know the references held by or to Excel, in calling GC on the interop you risk closing a copy of Excel that is used by other applications or manually by the user.

It's been my experience that the garbage collector is global in the .NET environment, and does not work per application. In calling it, you temporarily freeze all the running .NET applications while the GC clean up the memory. And temporarily can be quite a long time as far as the user is concerned.

The opposite scenario

Hi kbord_jockey,

Thanks for yr post, it really does the job when the object is to be released after having closed it from the application.

What I actually have is a slightly different scenario:
I pass some data to an Excel workbook and I need to keep Excel open as the user operates the exported data at his own.
The application that sends data to Excel may remain running 24/7 but the user often quits Excel upon data are handled as saved.
Still with this scenario Excel would keep staying in memory even after beeing closed by the user unless the .net aplication is closed.

To have it coded:

Code:

Private Sub XlSendData()
Dim locExcel As Excel.Application = New Excel.Application
Dim locWorkbook As Excel.Workbook = locExcel.Workbooks.Add
Dim locWorksheet As Excel.Worksheet = locWorkbook.Worksheets(1)
' Start sending data to Excel
'
' Finish sending data to Excel
' Set Excel visible so that the user can work with it
locExcel.Visible = True
' At this point Excel must be running but the sub is to release the references
Try
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorksheet)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locExcel)
Catch ex As Exception
locWorksheet = Nothing
locWorkbook = Nothing
locExcel = Nothing
End Try
' The user is still working with Excel
' This application will keep running for many more hours / days
' When the user closes Excel it remains in memory until this application is closed
End Sub

Have you got an idea on how to let Excel release the memory in this scenario?

Rgrds
Viaceslav

Originally Posted by kbord_jockey

Thought I would post an addition to whoever was trying to get this to work as it was very frustrating and I spent hours on these forums and msdn researching.

This is a really basic example but the keys to me were to ensure you had the loop for releasing the com objects and also that you used Excel.Workbooks in addition to Excel.Workbook.

I got this to work without calling the Garbage collector, as the jury is still out on whether or not that is recommended. Best of luck!

Excel not closing - process kill

Hey dymarski,

So basically you are letting your user have the version of Excel you opened running and you want to make sure it gets closed out properly after they are done? My scenario was a bit different as I knew when Excel needed to be closed out. One comment on your code would be that I recommend you use the Excel Workbooks object as well as the Excel Workbook, and are you looping through to release all the objects? Did you add GC.collect where recommended as well?

That being said I did have some trouble with one application making sure that Excel was closed out properly so I added in code to kill the Excel process if the process was still hanging around after I closed everything out properly. Doing this also helped showed bugs in my code I had missed as when I killed the Excel process (before releasing everything properly) I saw Excel errors in the Office Event log.

You can add more to your code like checking to see if the process has exited and if the Process Main Window Title is = "" then you know the Excel process has its visibility set to false or the user has closed Excel.

Getting the Excel process ID is somewhat tricky if you are starting excel and its visibility is set to false (you cant tell which version of Excel is yours by looking at title if multiple versions are running) but I wrote a function that I call before starting excel and immediately after which I can use to determine which Excel process is mine:

Anyway I just wanted to add this as I should have probably added in original post.

In your situation I would recommend either having your program monitor for the Excel program to close and then shut it down properly or have Excel notify it asynchronously (MSMQ or other method) when the user closes. Another thought would be to automate Excel, close and save, and then just open for the user it with a shell type command without any of the COM interfacing.

You are releasing objects in the improper order. Always release the content before the container. They should be in the reverse order.

If there was an error, one of the objects might not have been created. I am not sure that releaseObject works correctly with an object set to Nothing.

As for the general discussion in this thread, a lot of it is useless considerations.

I have been working with Excel and Word extensively since I started working in .NET, in the first Beta of Visual Studio 2002, and I never had to use GC.Collect or ReleaseComObject.

Most people do not understand what the GC does, and it shows in the discussion.

Calling Collect many times in a row as a few pieces of code in this discussion do is an even worst idea. You could release all your objects, and call Collect at the end, that would be more efficient.

But anyway, calling GC.Collect is usually a bad idea. The role of the garbage collector is to optimize memory usage and reclamation. When you call Collect, you override that optimisation and slow down the system for nothing.

As for ReleaseComObject, read the documentation: "You should use this method to free the underlying COM object that holds references to resources." Resources mean files, timers, database connections and a few others that are not under the control of managed code.

If you are careful to always close the Excel or Word file before getting rid of your objects, you will have released the resources, and ReleaseComObject is useless. Simply call Quit on the Application, you will never have memory leaks. The application will have released the files and the objects associated with it in unmanaged memory, your object variables will be set to Nothing automatically at the end of the method, the garbage collector will reuse available memory as necessary.

Thank you for the fast response, JBourgeois. Your patience and insight are both greatly appreciated. I am new to .NET and this project has been causing some headaches.

Perhaps I should take a step back and describe what I am trying to do and the questions I have for setting this up.

I want to create a set of public functions that allow me to check if an excel file exists, open it, if it does, format it, update it, and finally clean up any loose ends when the overall process is finished.

The code I sent in earlier works in a single form that I built. This is a multi-form project, and my first .NET program.. so, I wanted to build each part, test it, and then integrate them together at the end.

When I run the project as a Class (Form 1 in this case) and dimension all of the variables (strFilename, strPathname, xlApp, etc..) in the class, all of my functions operate as expected.. except for the release of the resources which you mentioned. I want to do that correctly, but I also need to figure this out.

Since I want to take this form class and be able to call it's functions from outside the class, I commented all of the variables out, built another form (Form 2) with a button, and added the function calls to button_click actions of Form 2.

In one example I built, I converted the functions in form 1 to Public, but when I try to call them in form 2, each of the function names is "undeclared" and I get the squiggly blue underline.

In another, I tried to pass the application name (xlApp above), workbook name (xlWorkbook above), and worksheet name (xlWorksheet above) to the functions. Each time the function returned to the procedure that called it, the values for each became Nothing. Even though I was only passing ByVal and not ByRef.

I feel like I know (or have found) enough to be dangerous.. but, only to myself

A form is a class. When you declare a Public function in a class (thus in a form), it becomes a method of that class. You cannot call it directly.

In order to be able to call a function in Form2 from Form1, Form1 needs to have a reference to Form2.

You would have code like this one in Form1:

Dim frm As New Form2
frm.YourPublicFunctionInForm2()

------

Looks as if you want to call the code from a button in Form2 from Form1? Not a good thing.

If to use the same function in many different forms. In this case, the best solution is often to put the function in a Module. A function stored in a module is available everywhere in the application, without having to create an object (variable) to call it.

So by putting the function in a module, Form1 can call it, Form2.button_click can call it, any form in your application or dll can call it.

------

[QUOTE]I tried to pass the application name (xlApp above), workbook name (xlWorkbook above), and worksheet name (xlWorksheet above) to the functions.[QUOTE]These variables are not the names, they are the application, the workbook and the worksheet itself.

An application does not have a name, it is a copy of Excel.

For the Workbook and the Worksheet, if you want to pass the name, you should pass xlWorkbook.Name, xlSheet.Name.

------

A very very very common misconception about ByVal vs ByRef.

They are significant only for value objects, small objects made from the basic types (Date, Integer, String, etc.) or structures (Size, Point, the type of objects that have an icon showing 3 blocks glued together in the lists provided by Intellisense in the code when you are declaring a variable).

Any object built on a class (the type of objects that have an icon showing 3 blocks linked with lines) is a reference object.

Explained simply, a variable that holds a value object holds the value itself. A variable that holds a reference object is not the object, it is a pointer to the object, the address of its location in memory.

When you pass a value object ByVal, you pass a copy of the value.

When you pass a reference object ByVal, you pass a copy of the pointer, a copy if the address where the object resides in memory. When the called method works with the parameter, it is working at the same address in memory, thus working with the original object, not a copy of the object.

Put simply, ByVal and ByRef makes not difference for objects that are built on a class.

Since Excel.Application, Excel.Workbook and Excel.Worksheet are all classes, it makes not difference if you pass those ByVal or ByRef. Although underneath the system does not pass the paramater the same way, the result in your code is the same as if you had passed the object ByRef.

This is quite normal, because normally, if you passe an Excel sheet to a procedure, it is because you want to work with that Excel sheet.

So if you send a Worksheet or a Workbook to a procedure and that procedure releases the object (sets it to Nothing), the object you passed will be set to Nothing when you come back from the call.

------

Hope this clears things a bit. You are getting yourself into something quite hard for a first project.

Excel is a COM application (also called an ActiveX application), not a .NET application. COM was the standard structure of applications using objects in Windows before .NET.

.NET and COM are 2 different worlds, they are not handled the same way in the computer memory. You must sometimes understand both in order to make them work together, not counting the fact that a .NET application does not talk directly to a COM application. It's hidden from you, but between your .NET application and Excel, there is a special dll called an interop, that is used as a bridge between the 2 worlds.

The interop handles the disprecancies between the structure of both types of applications, as well as some conversions.

For instance, a date in COM is stored as a Double that counts the days since January 31, 1899, while in .NET a date is stored as a Long that counts the number of ticks (a small fraction of a second) since January 1, 0001. When you pass a date to Excel from .NET, it needs to be converted, and the reverse the other way around.

This "translation" mechanism sometimes makes things harder than working only in .NET or only in COM, such as the
System.Runtime.InteropServices.Marshal.FinalReleaseComObject and ReleaseComObject that are, in my opinion, not necessary in the situations presented in this thread.

Thank you for taking the time to explain things so well.
I am learning very much... and it makes me feel better when a professional says "This is a difficult first application". Maybe I am not as slow as I thought.

I'll take a look at your information in more detail. I have a few ideas.. I think I can reconfigure things such that all of the calls are in the same "Run" form for the data collection. That may make things easier.