Using Excel with VB.net


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: Using Excel with VB.net

Hybrid View

  1. #1
    Join Date
    Mar 2006
    Posts
    100

    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

    Code:
    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

  2. #2
    Join Date
    Apr 2006
    Location
    Brisbane Australia
    Posts
    5
    excellent ..i was looking for some code like this a while back
    cheers m8

  3. #3
    Join Date
    May 2013
    Posts
    1
    Quote Originally Posted by toecutter View Post
    excellent ..i was looking for some code like this a while back
    cheers m8
    check this one...its has a lot of easy lessons regarding excel vb.net

    Excel VB.Net Samples

  4. #4
    Join Date
    Dec 2006
    Posts
    2

    Please help

    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

  5. #5
    Join Date
    Dec 2006
    Posts
    2

    Please help

    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

  6. #6
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    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?

    ...joe

  7. #7
    Join Date
    Jul 2009
    Posts
    1

    Great Post!

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

  8. #8
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    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.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  9. #9
    Join Date
    Nov 2009
    Posts
    2

    Excel not closing

    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

    Code:
    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

  10. #10
    Join Date
    Nov 2009
    Posts
    1

    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

    Quote Originally Posted by kbord_jockey View Post
    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

  11. #11
    Join Date
    Nov 2009
    Posts
    2

    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.

    HTML Code:
    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:
    Code:
    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...

  12. #12
    Join Date
    May 2010
    Posts
    3

    writing to an existing Excel file in VB.net

    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:
    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
    Last edited by Hack; 05-19-2010 at 07:19 AM. Reason: Added Code Tags

  13. #13
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    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

    Code:
    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.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  14. #14
    Join Date
    May 2010
    Posts
    3
    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.

  15. #15
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    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
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

Similar Threads

  1. Use function Solver of Excel in VB.NET 2003
    By Pliroforikarios in forum .NET
    Replies: 1
    Last Post: 01-12-2006, 07:05 PM
  2. Replies: 0
    Last Post: 07-08-2002, 11:37 AM
  3. Excel and VB.NET
    By Dave Doknjas in forum .NET
    Replies: 0
    Last Post: 05-26-2002, 11:17 PM
  4. Excel & Vb.Net
    By Depechie in forum .NET
    Replies: 1
    Last Post: 03-22-2002, 09:51 AM
  5. How is a Excel file to read in VB.NET
    By Emilia in forum .NET
    Replies: 1
    Last Post: 10-15-2001, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center