Using Excel with VB.net

[partyk1d24]

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

Imports Excel = Microsoft.Office.Interop.Excel...

    Private Sub getInfo()
        'declares the variables
        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)
        Dim partySheet As Excel.Worksheet = Nothing
        'try to open the workbook and a worksheet
        Try
            excelBook = excelApp.Workbooks.Open("C:\Program Files\DISClient\Templates\TemplateMerge.xls")
            partySheet = excelBook.Worksheets("MergeData")
        Catch ex As Exception
            MsgBox(ex)
        Finally
            'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this
            'The service (excel.exe) will continue to run
            NAR(partySheet)
            excelBook.Close(False)
            NAR(excelBook)
            excelApp.Workbooks.Close()
            NAR(excelApp.Workbooks)
            'quit and dispose app
            excelApp.Quit()
            NAR(excelApp)
            'VERY IMPORTANT
            GC.Collect()
        End Try
    End Sub
    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
        Finally
            o = Nothing
        End Try
    End Sub

Thanks to the forums for helping me to get it to work

[toecutter]

excellent ..i was looking for some code like this a while back
cheers m8

[gurpreet]

Please help me release excel from memory in this simple example.

objExcel = New Excel.Application


bExcelOpen = True
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objWorkbook = objExcel.Workbooks.Add(My.Application.Info.DirectoryPath & "\Templates\TestTemplate.xls")
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
objWorkbook.Close()

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel.Workbooks)
objWorkbook = Nothing

objExcel.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)
objExcel = Nothing

[gurpreet]

The program I submitted releases excel if I close my application.
My problem is that the application is a 24x7 and may not close in months.
Any help/suggestion will be highly appreciable.
Thanks

[joewmaki]

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?

[wesgoad]

Brilliant and simplistic! Every other coder on the planet wants to make it more complicated. Thanks!

[JBourgeois]

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.

[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!

Check out this msdn link as well:

http://support.microsoft.com/kb/317109

Dim xlApp As Excel.Application          'Excel Template Application
    Dim xlWbs As Excel.Workbooks            'Excel Application workbooks
    Dim wbTemplate As Excel.Workbook        'Template Workbook
    Dim wsTemplateSheet As Excel.Worksheet        'Template Sheet
    Dim wsRange As Excel.Range

public sub XLMain()

dim strReportPath as string = "C:\Some Directory\Excel is in\and junk.xls"

                xlApp = New Excel.Application

                xlWbs = xlApp.Workbooks

                wbTemplate = xlWbs.Open(strReportPath, , True)

'Now Excel is open, do stuff
'My code used references to template and named ranges

'Close Excel:

closeExcel  'Call Close Excel Sub

End Sub

'Functions/Subs

Public Sub RCO(ByVal obj As Object)
        Try
            Dim intRel As Integer = 0
            Do
                intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While intRel > 0
            msgbox("Final Released obj # " & intRel)
        Catch ex As Exception
            msgbox("Error releasing object" & ex.ToString)
            obj = Nothing
        End Try
    End Sub

Private Sub CloseExcel()
        'Sub to ensure Excel has been properly closed out

        'Close out Range
        Try
                RCO(wsRange)
                wsRange = Nothing
        Catch

        End Try

        'Close out Worksheet
        Try
                RCO(wsTemplateSheet)
                wsTemplateSheet = Nothing
        Catch

        End Try


        'Close out Workbook
        Try
            'wbTemplate.Close(False)
            RCO(wbTemplate)
            wbTemplate = Nothing
        Catch

        End Try


        'Close out Workbooks
        Try
            xlWbs.Close()
                RCO(xlWbs)
                xlWbs = Nothing
        Catch

        End Try

        'Close out Excel App
        Try
                xlApp.Quit()
                RCO(xlApp)
                xlApp = Nothing
        Catch

        End Try

'Could call GC.Collect at this point if needed....

    End Sub

[dymarski]

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:

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

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!

Check out this msdn link as well:

http://support.microsoft.com/kb/317109

[kbord_jockey]

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.

intXLPid = stored Excel PID

pXL = Excel Process

If IsNothing(Process.GetProcessById(intXLPid)) = False Then
                    pXL.Kill()
End If

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:

Private Sub getPIDs(ByRef strPID() As String)
        intXLPid = 0
        Dim pXL As Process = Nothing
        Try
            'Find my process ID 
            Dim processes As Process() = Process.GetProcessesByName("Excel")

            If processes.GetUpperBound(0) >= 0 Then

                ReDim strPID(processes.GetUpperBound(0))

                For i As Integer = 0 To processes.GetUpperBound(0)
                    ' 
                    strPID(i) = processes(i).Id
                Next
            Else
                ReDim strPID(0)
                strPID(0) = ""
            End If
        Catch
            ReDim strPID(0)
            strPID(0) = ""
        End Try

    End Sub

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.

Hope this helps...

[infundibulum1]

I am trying to do something similar... but am having a heck of a time getting it to work properly.

Steps:

1) Collect data in another form and pass it to variables.

2) Pass variables to a procedure that checks to see if the excel file exists. If no, create file. If yes, or new file created, then write to the file.

3) Periodically, I want to send new data to open rows in the excel file.

The issue is updating the Excel file. I want to be able to call a function from another form that writes to the open file.

I tried passing excel.application, excel.workbook, and excel.worksheet to the functions below, but I was throwing exceptions... and have probably been staring at this for too long

Any help is greatly appreciated.

Here is my code:

Imports System
Imports System.Reflection ' For Missing.Value and BindingFlags
Imports System.Runtime.InteropServices ' For COMException
Imports Excel
Imports System.IO

Public Class Form1

    Dim strPathname As String = "c:\"
    Dim strFileName As String = "test1.xls"
    Dim blnFileOpen As String = False
    Dim blnFileExists As String = False
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim intGroupNum As Integer = 1
    Dim intGroupNumMax As Integer = 10

    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

        Try

            'Check to see if File exists
            If IO.File.Exists(strPathname & strFileName) = True Then
                blnFileExists = True
           
                'Check to see if existing file is open
                blnFileOpen = fnFileOpen(strPathname & strFileName)
             
               'File exists, but is not open. Open file.
                If blnFileOpen = False Then

                    fnOpenExcelFile() 'Open Excel File

                End If

                fnWriteToExcel() 'Write to excel

                'Else file does not exist
            Else
                blnFileExists = False
               
                fnNewExcelFile() 'Create and format Excel workbook

                fnWriteToExcel() 'Write to Excel
               
            End If

         Catch GenErr As Exception 'General Error
            MsgBox(GenErr.ToString) 'Display error

        Finally
            releaseObject(xlApp) 'Release Excel Application
            releaseObject(xlWorkBook) 'Release Excel workbook
            releaseObject(xlWorkSheet) 'Release Excel Worksheet
        End Try

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Function fnOpenExcelFile() As Boolean

        'Function opens excel file

        Try
            xlApp = New Application() 'Create new Excel application
            xlWorkBook = xlApp.Workbooks.Open(strPathname & strFileName) 'Open file
            xlWorkBook.Application.Visible = True 'Make visible
            xlWorkBook.Activate() '
        Catch GenErr As Exception 'General Error
            MsgBox(GenErr.ToString) 'Display error
   
        End Try

    End Function

    Private Function fnNewExcelFile() As Boolean

        Dim i As Integer = 1

        xlApp = New Application() 'Create new Excel application
        xlWorkbook = xlApp.Workbooks.Add()  'Add new workbook
        xlWorkbook.SaveAs(strPathName & strFileName) 'Save new workbook

        'xlWorkBook = xlApp.Workbooks.Add("c:\test.xls")
        xlWorkbook = xlApp.Workbooks.Open(strPathName & strFileName) 'Open file
        xlWorkbook.Application.Visible = True
        xlWorkbook.Activate()

        'Format Excel Workbook
        'Delete two of the default worksheets
        For i = 1 To 2
            xlWorkbook.Sheets(1).Delete()
        Next

        i = 1 'Reset counter

        'Rename existing worksheet as rightmost (last) group
        xlWorksheet = xlWorkbook.Sheets(1)
        xlWorksheet.Name = "Group " & intGroupNumMax
        xlWorksheet.Cells(1, 1) = "Worksheet" & intGroupNumMax

        'xlWorkSheet.Name = "Group " & i
        'xlWorkSheet.Cells(1, 1) = "Worksheet" & i

        'Create enough worksheets to cover max group number
        Try
            For i = 1 To (intGroupNumMax - 1)
                xlWorksheet = xlWorkbook.Worksheets.Add
            Next

            'Name tabs
            For i = intGroupNumMax To 1 Step -1

                xlWorksheet = xlWorkbook.Sheets(i)
                xlWorksheet.Name = "Group " & i
                xlWorksheet.Cells(1, 1) = "Worksheet" & i

            Next

            xlWorkbook.SaveAs(strPathName & strFileName) 'Save changes to workbook

        Catch GenErr As Exception 'General Error
            MsgBox("The function fnNewExcelFile() failed." & vbCrLf & vbCrLf & _
                   "The 'write to Excel Checkbox' has been selected." _
                    & vbCrLf & "This requires Excel to be installed on this computer" _
                    & vbCrLf & "Make sure Excel is installed or deselect the checkbox") 'Display error
     
        End Try

    End Function

    Private Function fnFileOpen(ByVal FilePath As String) As Boolean
        'This function checks to see if the given file is open

        Dim fs As System.IO.FileStream

        Dim blnFileOpen As Boolean = True

        Try

            ' If Open() succeeds, then we know the file is not currently in use.

            fs = System.IO.File.Open(FilePath, FileMode.Open, FileAccess.Read, FileShare.None)

            blnFileOpen = False

            fs.Close()

        Catch ex As Exception

            ' any "file already in use" code here

        End Try

        fnFileOpen = blnFileOpen
    End Function

    Private Function fnWriteToExcel()

        'Function writes to specified Excel worksheet 

        Dim i As Integer = 1

        Try
            'Look through open workbooks for matching file
            For j As Integer = 1 To xlApp.Workbooks.Count '
                If xlApp.Workbooks(j).Name = strFileName Then '
                    xlWorkBook = xlApp.Workbooks(j)
                End If
            Next

            'Write to worksheet
            For i = intGroupNumMax To 1 Step -1

                xlWorkSheet = xlWorkBook.Sheets(i)
                xlWorkSheet.Cells(2, 1) = "Writing new stuff Here " & i
            Next

        Catch ex As Exception

            MsgBox(ex.ToString)

            MsgBox("The function fnWriteToExcel failed." & vbCrLf & vbCrLf &    "Check to make sure that Excel Workbook " _
                   & strFileName & " is available and open.")

        End Try

    End Function

End Class

[JBourgeois]

infundibulum1

I tried passing excel.application, excel.workbook, and excel.worksheet to the functions below, but I was throwing exceptions

What type of exception, what message, and if a COMException, what was the error code?

On wich line do you have a problem?

I see a few problems that can arise from the following

Finally
releaseObject(xlApp) 'Release Excel Application
releaseObject(xlWorkBook) 'Release Excel workbook
releaseObject(xlWorkSheet) 'Release Excel Worksheet
End Try

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.

[infundibulum1]

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

My gut tells me I am making this more difficult than it really is.

Thanks again.

[JBourgeois]

Lot of stuff. Hope I can clarify a few things.

------

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.

------

Good luck

[infundibulum1]

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.

Thanks again.

Back to the code!