none
Opening Word and Excel Files from VB.net RRS feed

  • Question

  • Hello again everyone,

    I've looked through some of the related topics to this newly created thread but none have answered my question. Refer to the following code below:

    Imports Microsoft.Office.Interop
    Public Class Form1
        'declares the following Word and Excel variables as public
        Dim newWord As Word.Application
        Dim newDoc As Word.Document
        Dim newXL As Excel.Application
        Dim newWB As Excel.Workbook
        Dim newWS As Excel.Worksheet
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'opens the Excel file
            OpenFileDialog1.Title = "Please select a file to open"
            OpenFileDialog1.InitialDirectory = "C:"
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx"
            If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                Dim excelFileName As String = OpenFileDialog1.FileName
                newXL = New Microsoft.Office.Interop.Excel.Application
                newXL.Visible = True
                newWB = newXL.Workbooks.Open(excelFileName)
                newWS = newWB.Worksheets(1)
            End If
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            'opens the Word file
            OpenFileDialog2.Title = "Please select a file to open"
            OpenFileDialog2.InitialDirectory = "C:"
            OpenFileDialog2.Filter = "Word files (*.doc)|*.docx"
            If OpenFileDialog2.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                Dim wordFileName As String = OpenFileDialog2.FileName
                newWord = New Microsoft.Office.Interop.Word.Application()
                newWord.Visible = True
                newDoc = newWord.Documents.Open(wordFileName)
            End If
        End Sub
        Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
            TextBox1.Text = OpenFileDialog1.ShowDialog.ToString()
        End Sub
        Private Sub OpenFileDialog2_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog2.FileOk
            TextBox2.Text = OpenFileDialog2.ShowDialog.ToString()
        End Sub
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            End
        End Sub
    End Class

    The two subs where a file, chosen by the user, is opened are both from two separate programs that I created and then tried to bring into the program above. They both work separately and run as coded, however now that they're together (i'm not sure if this is the reason why), I get an error message at the same point in each sub.

    At these points (newWord = New Microsoft... & newXL = New Microsoft...) I get the following error:

    NullReferenceException was unhandled: Object reference not set to an instance of an object

    As I stated before, both of these subs in their own programs work perfectly fine so I've been scratching my head on this for some time and can't figure out why this error is occuring. I did notice that when the Button1 (or Button2) are clicked to initiate these subs, the Open File window gets stuck in an infinite loop causing the program to crash.

    Any help would be appreciated and thanks to all who reply.

    DP

    Thursday, April 12, 2012 9:41 PM

Answers

  • In your Dialog_FileOk routines should the textbox be set to the Filename rather than Showing the dialog again?

    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    • Marked as answer by Darth Probius Tuesday, April 24, 2012 7:51 PM
    Thursday, April 12, 2012 10:33 PM
  • Hello,

    Unless there is a really good reason to declare the Office variables as public it would be best to localize them to the routine being used. Also by indexing a Worksheet as done in your code the object will not be released until the application closes. Which is yet another point, once finished with automation objects you should disposed of them immediately. Lastly please note that no object uses more than one period, if more than one period is used than it is not possible to release that object correctly. See the last code block for forcing objects closed.

    Use a similar model for MS-Word automation as shown with Excel below. Hope this is of some assistance.

    Below is an example that opens an existing Excel file, reads A1 cell then disposes of all objects.

    Top of module

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office

    Code to read data from existing file and release objects properly

    ''' <summary>
    ''' Open an exists Excel file, get Sheet1:A1 value
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <remarks>
    ''' </remarks>
    Private Sub DemoRead(ByVal FileName As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
        Dim xlCells As Excel.Range = Nothing
        Dim SingleCellToRead = xlWorkSheet.Range("A1")
        MessageBox.Show(String.Format("A1 = '{0}'", SingleCellToRead.Value))
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        If Not SingleCellToRead Is Nothing Then
            Marshal.FinalReleaseComObject(SingleCellToRead)
            SingleCellToRead = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
    End Sub

    Code below will release properly but shows in the last few lines how to force objects closed

    Private Sub DemoRead(ByVal FileName As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
        Dim xlCells As Excel.Range = Nothing
        Dim SingleCellToRead = xlWorkSheet.Range("A1")
        MessageBox.Show(String.Format("A1 = '{0}'", SingleCellToRead.Value))
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        If Not SingleCellToRead Is Nothing Then
            Marshal.FinalReleaseComObject(SingleCellToRead)
            SingleCellToRead = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        ' GC needs to be called twice in order to get the Finalizers called 
        ' - the first time in, it simply makes a list of what is to be 
        ' finalized, the second time in, it actually the finalizing. Only 
        ' then will the object do its automatic ReleaseComObject.
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub


    KSG

    • Marked as answer by Darth Probius Tuesday, April 24, 2012 7:51 PM
    Thursday, April 12, 2012 11:52 PM
    Moderator
  • There are two issues with your Excel code, one easily to fix while one is not so easy to detect. The detectable issue is with how you selected the worksheet newWs = newWb.Worksheets(1) which should be casted as a WorkSheet i.e. new WS = Ctype(newWb.Worksheets(1) which will compile now with Option Strict On (my guess is you have Option Strict Off, otherwise you would not be able to compile).

    For the snake that bite you (and has bitten many but more likely not realized) is the code I used to correct your initializing newWS above. Using a numeric to index a WorkSheet keeps the object in memory while using newWS = CType(NewWB.ActiveSheet, Excel.Worksheet) allows the object to be later released and does select the first WorkSheet.

    In the example below Button1 Click event releases memory while Button2 Click event does not as per above.

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Public Class Form1
        Dim fileNameExcel As String
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim newXL As Excel.Application = Nothing
                Dim newWBS As Excel.Workbooks = Nothing
                Dim NewWB As Excel.Workbook = Nothing
                Dim newWS As Excel.Worksheet = Nothing
                newXL = New Excel.Application
                newXL.DisplayAlerts = False
                newWBS = newXL.Workbooks
                NewWB = newWBS.Open(OpenFileDialog1.FileName)
                newXL.Visible = True
                newWS = CType(NewWB.ActiveSheet, Excel.Worksheet)
                MessageBox.Show("Open!!!")
                NewWB.Close()
                newXL.UserControl = True
                newXL.Quit()
                If Not newWS Is Nothing Then
                    Marshal.FinalReleaseComObject(newWS)
                    newWS = Nothing
                End If
                If Not NewWB Is Nothing Then
                    Marshal.FinalReleaseComObject(NewWB)
                    NewWB = Nothing
                End If
                If Not newWBS Is Nothing Then
                    Marshal.FinalReleaseComObject(newWBS)
                    newWBS = Nothing
                End If
                If Not newXL Is Nothing Then
                    Marshal.FinalReleaseComObject(newXL)
                    newXL = Nothing
                End If
            End If
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim newXL As Excel.Application = Nothing
            Dim newWB As Excel.Workbook = Nothing
            Dim newWS As Excel.Worksheet = Nothing
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                fileNameExcel = OpenFileDialog1.FileName
                newXL = New Excel.Application()
                newXL.Visible = True
                newWB = newXL.Workbooks.Open(fileNameExcel)
                newWS = CType(newWB.Worksheets(1), Excel.Worksheet)
                'newWS.Name = "Test" to be used to change the worksheet's name
            End If
            'The following will release all COM objects from the sub
            'If Not fileNameExcel Is Nothing Then
            '    Marshal.FinalReleaseComObject(fileNameExcel)
            '    fileNameExcel = Nothing
            'End If
            MessageBox.Show("Open!!!")
            newWB.Close()
            newXL.UserControl = True
            newXL.Quit()
            If Not newWS Is Nothing Then
                Marshal.FinalReleaseComObject(newWS)
                newWS = Nothing
            End If
            If Not newWB Is Nothing Then
                Marshal.FinalReleaseComObject(newWB)
                newWB = Nothing
            End If
            If Not newXL Is Nothing Then
                Marshal.FinalReleaseComObject(newXL)
                newXL = Nothing
            End If
        End Sub
    End Class

    If I did not mention it before, Office automation when done properly takes time and you are indeed on the right track.


    KSG

    • Marked as answer by Darth Probius Tuesday, April 24, 2012 7:52 PM
    Tuesday, April 24, 2012 1:55 PM
    Moderator

All replies

  • In your Dialog_FileOk routines should the textbox be set to the Filename rather than Showing the dialog again?

    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    • Marked as answer by Darth Probius Tuesday, April 24, 2012 7:51 PM
    Thursday, April 12, 2012 10:33 PM
  • Hello,

    Unless there is a really good reason to declare the Office variables as public it would be best to localize them to the routine being used. Also by indexing a Worksheet as done in your code the object will not be released until the application closes. Which is yet another point, once finished with automation objects you should disposed of them immediately. Lastly please note that no object uses more than one period, if more than one period is used than it is not possible to release that object correctly. See the last code block for forcing objects closed.

    Use a similar model for MS-Word automation as shown with Excel below. Hope this is of some assistance.

    Below is an example that opens an existing Excel file, reads A1 cell then disposes of all objects.

    Top of module

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office

    Code to read data from existing file and release objects properly

    ''' <summary>
    ''' Open an exists Excel file, get Sheet1:A1 value
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <remarks>
    ''' </remarks>
    Private Sub DemoRead(ByVal FileName As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
        Dim xlCells As Excel.Range = Nothing
        Dim SingleCellToRead = xlWorkSheet.Range("A1")
        MessageBox.Show(String.Format("A1 = '{0}'", SingleCellToRead.Value))
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        If Not SingleCellToRead Is Nothing Then
            Marshal.FinalReleaseComObject(SingleCellToRead)
            SingleCellToRead = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
    End Sub

    Code below will release properly but shows in the last few lines how to force objects closed

    Private Sub DemoRead(ByVal FileName As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
        Dim xlCells As Excel.Range = Nothing
        Dim SingleCellToRead = xlWorkSheet.Range("A1")
        MessageBox.Show(String.Format("A1 = '{0}'", SingleCellToRead.Value))
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        If Not SingleCellToRead Is Nothing Then
            Marshal.FinalReleaseComObject(SingleCellToRead)
            SingleCellToRead = Nothing
        End If
        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If
        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If
        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        ' GC needs to be called twice in order to get the Finalizers called 
        ' - the first time in, it simply makes a list of what is to be 
        ' finalized, the second time in, it actually the finalizing. Only 
        ' then will the object do its automatic ReleaseComObject.
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub


    KSG

    • Marked as answer by Darth Probius Tuesday, April 24, 2012 7:51 PM
    Thursday, April 12, 2012 11:52 PM
    Moderator
  • Hello,

    you can try this C# / VB.NET Excel library to VB.NET open XLS / XLSX files like this:

    Dim ef As New ExcelFile
     
    ' Loads Excel file.
    ef.LoadXls("filename.xls")
     
    ' Selects first worksheet.
    Dim ws As ExcelWorksheet = ef.Worksheets(0)
     
    ' Displays the value of first cell in the messageBox.
    MessageBox.Show(ws.Cells("A1").Value.ToString())

    VB.NET DOCX files can easily be opened with this C# / VB.NET Word library like this:

    ' Load a document.
    Dim document = DocumentModel.Load("In.docx", DocxLoadOptions.DocxDefault)
     
    ' Iterate over all paragraphs in the document.
    For Each para As Paragraph In document.GetChildElements(True, ElementType.Paragraph)
    	' Iterate over all runs in the paragraph and write their text to console.
    	For Each run As Run In para.GetChildElements(True, ElementType.Run)
    		Console.Write(run.Text)
    	Next
    	Console.WriteLine()
    Next

    Monday, April 16, 2012 7:43 AM
  • Hello everyone,

    Thanks for responding to this question. I have been out for a couple of days and have not had a chance to look at this thread since I posted this question. I will look at it today and try some of your suggestions.

    In the meantime, do any of you have a book you would recommend that covers what you're talking about. I'm still pretty new to vb.net and am currently using Step by Step: Visual Basic 2008 by Michael Halvorson. It's a pretty good starter, but it doesn't seem to cover some of the techniques that most of you are suggesting.

    Thanks again to all.

    -DP

    Tuesday, April 17, 2012 2:58 PM
  • Hello everyone,

    Thanks for responding to this question. I have been out for a couple of days and have not had a chance to look at this thread since I posted this question. I will look at it today and try some of your suggestions.

    In the meantime, do any of you have a book you would recommend that covers what you're talking about. I'm still pretty new to vb.net and am currently using Step by Step: Visual Basic 2008 by Michael Halvorson. It's a pretty good starter, but it doesn't seem to cover some of the techniques that most of you are suggesting.

    Thanks again to all.

    -DP


    In the past most books I have paged thru never fully covered this topic properly.

    KSG

    Tuesday, April 17, 2012 6:53 PM
    Moderator
  • Kevin,

    Thanks for responding. Looking at your code, there's something that confuses me. I remember my C++ professor mentioning that objects should be released after use so that they will be free and available for future use if needed - I'm assuming this is the same concept you refer to at the bottom of the second block of code. What confuses me are the periods. I'm really not sure what you're referring to. I've looked over both of our codes and can't seem to find these (please forgive my lack of knowledge on this).

    Also, since you mentioned it, I'm not really sure why I made those variables public. Maybe I was going to add more subs and use them in those, but as of right now, I don't remember. Correct me if I'm wrong, but by setting those variables as '= Nothing' does this initialize them to a 'zero (or null - probably not the correct term) state' so that there nothing stored on them?

    I've also noticed something else. What is the difference between the top block of both of our codes when you use:

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

    versus me using: Imports Microsoft.Office.Interop

    I've noticed users on several posts using one or the other but was not sure which one was appropriate to use in what case.

    Finally, when I mentioned in the later post about a book regarding the process you and other users are recommending on this post, I should have clarified and asked for a book that shows advanced techniques to make what I'm doing easier or at least give me better insight into what users are suggesting.

    Thanks again,

    -DP

    Friday, April 20, 2012 2:02 PM
  • Kevin,

    Thanks for responding. Looking at your code, there's something that confuses me. I remember my C++ professor mentioning that objects should be released after use so that they will be free and available for future use if needed - I'm assuming this is the same concept you refer to at the bottom of the second block of code. What confuses me are the periods. I'm really not sure what you're referring to. I've looked over both of our codes and can't seem to find these (please forgive my lack of knowledge on this).

    Also, since you mentioned it, I'm not really sure why I made those variables public. Maybe I was going to add more subs and use them in those, but as of right now, I don't remember. Correct me if I'm wrong, but by setting those variables as '= Nothing' does this initialize them to a 'zero (or null - probably not the correct term) state' so that there nothing stored on them?

    I've also noticed something else. What is the difference between the top block of both of our codes when you use:

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

    versus me using: Imports Microsoft.Office.Interop

    I've noticed users on several posts using one or the other but was not sure which one was appropriate to use in what case.

    Finally, when I mentioned in the later post about a book regarding the process you and other users are recommending on this post, I should have clarified and asked for a book that shows advanced techniques to make what I'm doing easier or at least give me better insight into what users are suggesting.

    Thanks again,

    -DP

    In regards to what your professor suggested, that is what I am indicating also.

    In regards to the Import statement question, best to read about aliasing within an Import Statement here at MSDN documentation.

    Concerning the period (or dot operator), in the first code block we initialize a Workbook by calling the Add method of WorkBooks. The first line of code has one period as does the second line. In the second code block we are invoking the Add Method of the WorkBooks object from the Excel Application Object using (on the right of the equal sign) two periods which is referencing the high level object tunneling thru which leaves a pointer in memory that will not release using the method I showed.

    Dim oWB As Excel.Workbook = Nothing
    oWB = oWBs.Add()Dim oWB As Excel.Workbook = oXL.Workbooks.Add()
    Dim oWB As Excel.Workbook = oXL.Workbooks.Add()

    Below are two code examples from Microsoft All-In-One Code Framework demos. The first creates and releases objects correctly while the second does not. Please refer to the comments within the examples. I would highly suggest working thru the comments and code.

    '****************************** Module Header ******************************'
    ' Module Name:  Solution1.vb
    ' Project:      VBAutomateExcel
    ' Copyright (c) Microsoft Corporation.
    ' 
    ' Solution1.AutomateExcel demonstrates automating Microsoft Excel application by 
    ' using Microsoft Excel Primary Interop Assembly (PIA) and explicitly assigning 
    ' each COM accessor object to a new varaible that you would explicitly call 
    ' Marshal.FinalReleaseComObject to release it at the end. When you use this 
    ' solution, it is important to avoid calls that tunnel into the object model 
    ' because they will orphan Runtime Callable Wrapper (RCW) on the heap that you 
    ' will not be able to access in order to call Marshal.ReleaseComObject. You need 
    ' to be very careful. For example, 
    ' 
    '   Dim oWB As Excel.Workbook = oXL.Workbooks.Add()
    ' 
    ' Calling oXL.Workbooks.Add creates an RCW for the Workbooks object. If you 
    ' invoke these accessors via tunneling as this code does, the RCW for Workbooks 
    ' is created on the GC heap, but the reference is created under the hood on the 
    ' stack and are then discarded. As such, there is no way to call 
    ' MarshalFinalReleaseComObject on this RCW. To get such kind of RCWs released, 
    ' you would either need to force a garbage collection as soon as the calling 
    ' function is off the stack (see Solution2.AutomateExcel), or you would need to 
    ' explicitly assign each accessor object to a variable and free it.
    ' 
    '   Dim oWBs As Excel.Workbooks = oXL.Workbooks
    '   Dim oWB As Excel.Workbook = oWBs.Add()
    ' 
    ' This source is subject to the Microsoft Public License.
    ' See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    ' All other rights reserved.
    ' 
    ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
    ' EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
    ' WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
    '***************************************************************************'
    #Region "Imports directives"
    Imports System.Reflection
    Imports System.IO
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    #End Region
    Class Solution1
        Public Shared Sub AutomateExcel()
            Dim oXL As Excel.Application = Nothing
            Dim oWBs As Excel.Workbooks = Nothing
            Dim oWB As Excel.Workbook = Nothing
            Dim oSheet As Excel.Worksheet = Nothing
            Dim oCells As Excel.Range = Nothing
            Dim oRng1 As Excel.Range = Nothing
            Dim oRng2 As Excel.Range = Nothing
            Try
                ' Create an instance of Microsoft Excel and make it invisible.
                oXL = New Excel.Application
                oXL.Visible = False
                Console.WriteLine("Excel.Application is started")
                ' Create a new workbook.
                oWBs = oXL.Workbooks
                oWB = oWBs.Add()
                Console.WriteLine("A new workbook is created")
                ' Get the active Worksheet and set its name.
                oSheet = oWB.ActiveSheet
                oSheet.Name = "Report"
                Console.WriteLine("The active worksheet is renamed as Report")
                ' Fill data into the worksheet's cells.
                Console.WriteLine("Filling data into the worksheet ...")
                ' Set the column header
                oCells = oSheet.Cells
                oCells(1, 1) = "First Name"
                oCells(1, 2) = "Last Name"
                oCells(1, 3) = "Full Name"
                ' Construct an array of user names
                Dim saNames(,) As String = {{"John", "Smith"}, _
                                            {"Tom", "Brown"}, _
                                            {"Sue", "Thomas"}, _
                                            {"Jane", "Jones"}, _
                                            {"Adam", "Johnson"}}
                ' Fill A2:B6 with an array of values (First and Last Names).
                oRng1 = oSheet.Range("A2", "B6")
                oRng1.Value2 = saNames
                ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
                oRng2 = oSheet.Range("C2", "C6")
                oRng2.Formula = "=A2 & "" "" & B2"
                ' Save the workbook as a xlsx file and close it.
                Console.WriteLine("Save and close the workbook")
                Dim fileName As String = Path.GetDirectoryName( _
                Assembly.GetExecutingAssembly().Location) & "\Sample1.xlsx"
                oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook)
                oWB.Close()
                ' Close the Excel application.
                Console.WriteLine("Quit the Excel application")
                ' Excel will stick around after Quit if it is not under user 
                ' control and there are outstanding references. When Excel is 
                ' started or attached programmatically and 
                ' Application.Visible = false, Application.UserControl is false. 
                ' The UserControl property can be explicitly set to True which 
                ' should force the application to terminate when Quit is called, 
                ' regardless of outstanding references.
                oXL.UserControl = True
                oXL.Quit()
            Catch ex As Exception
                Console.WriteLine("Solution1.AutomateExcel throws the error: {0}", _
                                  ex.Message)
            Finally
                ' Clean up the unmanaged Excel COM resources by explicitly call 
                ' Marshal.FinalReleaseComObject on all accessor objects. 
                ' See http://support.microsoft.com/kb/317109.
                If Not oRng2 Is Nothing Then
                    Marshal.FinalReleaseComObject(oRng2)
                    oRng2 = Nothing
                End If
                If Not oRng1 Is Nothing Then
                    Marshal.FinalReleaseComObject(oRng1)
                    oRng1 = Nothing
                End If
                If Not oCells Is Nothing Then
                    Marshal.FinalReleaseComObject(oCells)
                    oCells = Nothing
                End If
                If Not oSheet Is Nothing Then
                    Marshal.FinalReleaseComObject(oSheet)
                    oSheet = Nothing
                End If
                If Not oWB Is Nothing Then
                    Marshal.FinalReleaseComObject(oWB)
                    oWB = Nothing
                End If
                If Not oWBs Is Nothing Then
                    Marshal.FinalReleaseComObject(oWBs)
                    oWBs = Nothing
                End If
                If Not oXL Is Nothing Then
                    Marshal.FinalReleaseComObject(oXL)
                    oXL = Nothing
                End If
            End Try
        End Sub
    End Class

    '****************************** Module Header ******************************'
    ' Module Name:  Solution2.vb
    ' Project:      VBAutomateExcel
    ' Copyright (c) Microsoft Corporation.
    ' 
    ' Solution2.AutomateExcel demonstrates automating Microsoft Excel 
    ' application by using Microsoft Excel Primary Interop Assembly (PIA) and 
    ' forcing a garbage collection as soon as the automation function is off the 
    ' stack (at which point the Runtime Callable Wrapper (RCW) objects are no 
    ' longer rooted) to clean up RCWs and release COM objects.
    ' 
    ' This source is subject to the Microsoft Public License.
    ' See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    ' All other rights reserved.
    ' 
    ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
    ' EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
    ' WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
    '***************************************************************************'
    #Region "Import directives"
    Imports System.IO
    Imports System.Reflection
    Imports Excel = Microsoft.Office.Interop.Excel
    #End Region
    Class Solution2
        Public Shared Sub AutomateExcel()
            AutomateExcelImpl()
            ' Clean up the unmanaged Excel COM resources by forcing a garbage 
            ' collection as soon as the calling function is off the stack (at 
            ' which point these objects are no longer rooted).
            GC.Collect()
            GC.WaitForPendingFinalizers()
            ' GC needs to be called twice in order to get the Finalizers called 
            ' - the first time in, it simply makes a list of what is to be 
            ' finalized, the second time in, it actually the finalizing. Only 
            ' then will the object do its automatic ReleaseComObject.
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Sub
        Private Shared Sub AutomateExcelImpl()
            Try
                ' Create an instance of Microsoft Excel and make it invisible.
                Dim oXL As New Excel.Application
                oXL.Visible = False
                Console.WriteLine("Excel.Application is started")
                ' Create a new workbook.
                Dim oWB As Excel.Workbook = oXL.Workbooks.Add()
                Console.WriteLine("A new workbook is created")
                ' Get the active Worksheet and set its name.
                Dim oSheet As Excel.Worksheet = oWB.ActiveSheet
                oSheet.Name = "Report"
                Console.WriteLine("The active worksheet is renamed as Report")
                ' Fill data into the worksheet's cells.
                Console.WriteLine("Filling data into the worksheet ...")
                ' Set the column header
                oSheet.Cells(1, 1) = "First Name"
                oSheet.Cells(1, 2) = "Last Name"
                oSheet.Cells(1, 3) = "Full Name"
                ' Construct an array of user names
                Dim saNames(,) As String = {{"John", "Smith"}, _
                                            {"Tom", "Brown"}, _
                                            {"Sue", "Thomas"}, _
                                            {"Jane", "Jones"}, _
                                            {"Adam", "Johnson"}}
                ' Fill A2:B6 with an array of values (First and Last Names).
                oSheet.Range("A2", "B6").Value2 = saNames
                ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
                oSheet.Range("C2", "C6").Formula = "=A2 & "" "" & B2"
                ' Save the workbook as a xlsx file and close it.
                Console.WriteLine("Save and close the workbook")
                Dim fileName As String = Path.GetDirectoryName( _
                Assembly.GetExecutingAssembly().Location) & "\Sample2.xlsx"
                oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook)
                oWB.Close()
                ' Close the Excel application.
                Console.WriteLine("Quit the Excel application")
                ' Excel will stick around after Quit if it is not under user 
                ' control and there are outstanding references. When Excel is 
                ' started or attached programmatically and 
                ' Application.Visible = false, Application.UserControl is false. 
                ' The UserControl property can be explicitly set to True which 
                ' should force the application to terminate when Quit is called, 
                ' regardless of outstanding references.
                oXL.UserControl = True
                oXL.Quit()
            Catch ex As Exception
                Console.WriteLine("Solution2.AutomateExcel throws the error: {0}", _
                                  ex.Message)
            End Try
        End Sub
    End Class



    KSG

    Friday, April 20, 2012 6:22 PM
    Moderator
  • Kevin,

    Thanks for responding. Looking at your code, there's something that confuses me. I remember my C++ professor mentioning that objects should be released after use so that they will be free and available for future use if needed - I'm assuming this is the same concept you refer to at the bottom of the second block of code. What confuses me are the periods. I'm really not sure what you're referring to. I've looked over both of our codes and can't seem to find these (please forgive my lack of knowledge on this).

    Also, since you mentioned it, I'm not really sure why I made those variables public. Maybe I was going to add more subs and use them in those, but as of right now, I don't remember. Correct me if I'm wrong, but by setting those variables as '= Nothing' does this initialize them to a 'zero (or null - probably not the correct term) state' so that there nothing stored on them?

    I've also noticed something else. What is the difference between the top block of both of our codes when you use:

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

    versus me using: Imports Microsoft.Office.Interop

    I've noticed users on several posts using one or the other but was not sure which one was appropriate to use in what case.

    Finally, when I mentioned in the later post about a book regarding the process you and other users are recommending on this post, I should have clarified and asked for a book that shows advanced techniques to make what I'm doing easier or at least give me better insight into what users are suggesting.

    Thanks again,

    -DP

    For books, I don't know as I have not read any books on this subject just worked thru what others have done and strive to tighten said code up. Personally at work I use OpenXML SDK or Aspose cells. Aspose Cells is a great product but does cost you money yet it has saved me from hours of coding time and time again. The only down side to OpenXML SDK is that it is Office 2007 or later and there is (at least from my searching) not a great deal of starter examples out on the web.

    Here are my experiments with OpenXML SDK if you want to expore possibilities

    http://www.vbforums.com/showthread.php?t=664975


    KSG

    Friday, April 20, 2012 6:48 PM
    Moderator
  • Riced,

    At first I didn't understand what you were talking about, until I realized that what you were referencing in the following line:

    TextBox1.Text = OpenFileDialog1.ShowDialog.ToString()

    Once I switched this line to: TextBox1.Text = fileNameExcel I didn't recieve the error. I had not realized that I had gotten myself stuck in an infinite loop. LOL

    Thanks

    -DP

    Tuesday, April 24, 2012 11:26 AM
  • Kevin,

    I'm happy to say that I now understand what you were referring to when you (and others) have said that the objects need to be released. After trying the code you suggested above and the one below (I found from the MS Code Framework site) everything makes sense (yay!).

    Imports Microsoft.Office.Interop
    Public Class Form1
        Private Sub NAR(ByVal o As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
            Catch
            Finally
                o = Nothing
            End Try
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim oApp As New Excel.Application()
            Dim oBooks As Excel.Workbooks = oApp.Workbooks
            Dim oBook As Excel.Workbook = oBooks.Add
            Dim oSheet As Excel.Worksheet = oApp.ActiveSheet
            NAR(oSheet)
            oBook.Close(False)
            NAR(oBook)
            NAR(oBooks)
            oApp.Quit()
            NAR(oApp)
            Debug.WriteLine("Sleeping...")
            System.Threading.Thread.Sleep(5000)
            Debug.WriteLine("End Excel")
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            End
        End Sub
    End Class

    When I checked the task manager, I could see the Excel.EXE process being called and then immediately removed after the object was released. With this and what you had suggested above, I 'redesigned' my code to incorporate the object releases in each sub:

    Imports Microsoft.Office.Interop
    Imports System.Windows.Forms
    Imports System.Runtime.InteropServices 'needed for Marshal
    Public Class Form1
        Dim fileNameExcel As String = Nothing
        Dim fileNameWord As String = Nothing
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim newXL As Excel.Application = Nothing
            Dim newWB As Excel.Workbook = Nothing
            Dim newWS As Excel.Worksheet = Nothing
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                fileNameExcel = OpenFileDialog1.FileName
                newXL = New Excel.Application()
                newXL.Visible = True
                newWB = newXL.Workbooks.Open(fileNameExcel)
                newWS = newWB.Worksheets(1)
                'newWS.Name = "Test" to be used to change the worksheet's name
            End If
            'The following will release all COM objects from the sub
            'If Not fileNameExcel Is Nothing Then
            '    Marshal.FinalReleaseComObject(fileNameExcel)
            '    fileNameExcel = Nothing
            'End If
            If Not newWS Is Nothing Then
                Marshal.FinalReleaseComObject(newWS)
                newWS = Nothing
            End If
            If Not newWB Is Nothing Then
                Marshal.FinalReleaseComObject(newWB)
                newWB = Nothing
            End If
            If Not newXL Is Nothing Then
                Marshal.FinalReleaseComObject(newXL)
                newXL = Nothing
            End If
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim newWord As Word.Application = Nothing
            Dim newDoc As Word.Document = Nothing
            'opens the Word file
            OpenFileDialog2.Title = "Please select a file to open"
            OpenFileDialog2.InitialDirectory = "C:"
            OpenFileDialog2.Filter = "Word files (*.doc)|*.docx"
            If OpenFileDialog2.ShowDialog() = DialogResult.OK Then
                fileNameWord = OpenFileDialog2.FileName
                newWord = New Word.Application()
                newWord.Visible = True
                newDoc = newWord.Documents.Open(fileNameWord)
            End If
            'The following will release all COM objects from the sub
            'If Not fileNameWord Is Nothing Then
            '    Marshal.FinalReleaseComObject(fileNameWord)
            '    fileNameWord = Nothing
            'End If
            If Not newDoc Is Nothing Then
                Marshal.FinalReleaseComObject(newDoc)
                newDoc = Nothing
            End If
            If Not newWord Is Nothing Then
                Marshal.FinalReleaseComObject(newWord)
                newWord = Nothing
            End If
        End Sub
        Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
            TextBox1.Text = fileNameExcel
        End Sub
        Private Sub OpenFileDialog2_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog2.FileOk
            TextBox2.Text = fileNameWord
        End Sub
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            End
        End Sub
    End Class

    With the new code, I am now properly releasing all objects I use. There is only one issue I have. In the first block of code above, the Excel process is removed once the 5000ms (I assume it's in ms) has passed instead of when the Excel application is closed as it should be. When I run my code, the Word section functions properly and removes the process once Word is closed instead of when the vb app is closed. However, the excel portion does not run this way. After closing Excel, the process still remains in task manager and is only removed once the vb app is closed. While this does 'work', I realize that if multiple Excel files need to be opened, the processes will continue to stack on each other until the vb app is closed.

    I was going to release the 'fileNameExcel' object but commented that part out when I recieved the following error:

    The object's type must be _ComObject or derived from _ComObject. Parameter name: o

    The same part is also commented out in the Word section as well. However, since that section of code works, I'm assuming I don't need it.

    Finally, once this is resolved, the part of marking an answer will come into question and I was wondering if you or anyone else could help with this. Riced did answer the question I had originally posted since that error no longer occurs, but you have given me so much input as to properly releasing objects and cleaning up my code that I definitely needed. I'm not really sure which post to mark as an answer.

    Thanks again to you and everyone else

    -DP



    Tuesday, April 24, 2012 11:44 AM
  • There are two issues with your Excel code, one easily to fix while one is not so easy to detect. The detectable issue is with how you selected the worksheet newWs = newWb.Worksheets(1) which should be casted as a WorkSheet i.e. new WS = Ctype(newWb.Worksheets(1) which will compile now with Option Strict On (my guess is you have Option Strict Off, otherwise you would not be able to compile).

    For the snake that bite you (and has bitten many but more likely not realized) is the code I used to correct your initializing newWS above. Using a numeric to index a WorkSheet keeps the object in memory while using newWS = CType(NewWB.ActiveSheet, Excel.Worksheet) allows the object to be later released and does select the first WorkSheet.

    In the example below Button1 Click event releases memory while Button2 Click event does not as per above.

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Public Class Form1
        Dim fileNameExcel As String
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim newXL As Excel.Application = Nothing
                Dim newWBS As Excel.Workbooks = Nothing
                Dim NewWB As Excel.Workbook = Nothing
                Dim newWS As Excel.Worksheet = Nothing
                newXL = New Excel.Application
                newXL.DisplayAlerts = False
                newWBS = newXL.Workbooks
                NewWB = newWBS.Open(OpenFileDialog1.FileName)
                newXL.Visible = True
                newWS = CType(NewWB.ActiveSheet, Excel.Worksheet)
                MessageBox.Show("Open!!!")
                NewWB.Close()
                newXL.UserControl = True
                newXL.Quit()
                If Not newWS Is Nothing Then
                    Marshal.FinalReleaseComObject(newWS)
                    newWS = Nothing
                End If
                If Not NewWB Is Nothing Then
                    Marshal.FinalReleaseComObject(NewWB)
                    NewWB = Nothing
                End If
                If Not newWBS Is Nothing Then
                    Marshal.FinalReleaseComObject(newWBS)
                    newWBS = Nothing
                End If
                If Not newXL Is Nothing Then
                    Marshal.FinalReleaseComObject(newXL)
                    newXL = Nothing
                End If
            End If
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim newXL As Excel.Application = Nothing
            Dim newWB As Excel.Workbook = Nothing
            Dim newWS As Excel.Worksheet = Nothing
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                fileNameExcel = OpenFileDialog1.FileName
                newXL = New Excel.Application()
                newXL.Visible = True
                newWB = newXL.Workbooks.Open(fileNameExcel)
                newWS = CType(newWB.Worksheets(1), Excel.Worksheet)
                'newWS.Name = "Test" to be used to change the worksheet's name
            End If
            'The following will release all COM objects from the sub
            'If Not fileNameExcel Is Nothing Then
            '    Marshal.FinalReleaseComObject(fileNameExcel)
            '    fileNameExcel = Nothing
            'End If
            MessageBox.Show("Open!!!")
            newWB.Close()
            newXL.UserControl = True
            newXL.Quit()
            If Not newWS Is Nothing Then
                Marshal.FinalReleaseComObject(newWS)
                newWS = Nothing
            End If
            If Not newWB Is Nothing Then
                Marshal.FinalReleaseComObject(newWB)
                newWB = Nothing
            End If
            If Not newXL Is Nothing Then
                Marshal.FinalReleaseComObject(newXL)
                newXL = Nothing
            End If
        End Sub
    End Class

    If I did not mention it before, Office automation when done properly takes time and you are indeed on the right track.


    KSG

    • Marked as answer by Darth Probius Tuesday, April 24, 2012 7:52 PM
    Tuesday, April 24, 2012 1:55 PM
    Moderator
  • In regards to marking the question resolved, I believe you should mark one for Riced and one for me as proposed as answer in this case. Of course the other choice is to mark one as proposed as answered which I will not make a suggestion, you need too. Either way good to see you are on the right track now which is most important.

    KSG

    Tuesday, April 24, 2012 1:58 PM
    Moderator
  • Thanks to all who have helped with this question. Special thanks to Kevin and Riced for their answers.

    One last thing before closing this:

    In the final application, the user would select an excel file from which the contents would be copied into a table in word (more like a form in a table format). In this case, the user would want to see the final product in the Word app and not see the excel file they chose for copying. Would I shorten the excel portion of the above code to this:

    Imports Microsoft.Office.Interop
    Imports System.Windows.Forms
    Imports System.Runtime.InteropServices 'needed for Marshal
    Public Class Form1
        Dim fileNameExcel As String = Nothing
            Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim newXL As Excel.Application = Nothing
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                fileNameExcel = OpenFileDialog1.FileName
                newXL = New Excel.Application()
                newXL.Visible = False
            End If
            If Not newXL Is Nothing Then
                Marshal.FinalReleaseComObject(newXL)
                newXL = Nothing
            End If
        End Sub
        Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
            TextBox1.Text = fileNameExcel
        End Sub
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            End
        End Sub
    End Class

    I would then use the file chosen to do all the data merging...If this should be in another thread let me know. I was just trying to get an idea on this part and make sure i'm heading in the right direction.

    Thanks again to all!!



    Tuesday, April 24, 2012 8:05 PM
  • Thanks to all who have helped with this question. Special thanks to Kevin and Riced for their answers.

    One last thing before closing this:

    In the final application, the user would select an excel file from which the contents would be copied into a table in word (more like a form in a table format). In this case, the user would want to see the final product in the Word app and not see the excel file they chose for copying. Would I shorten the excel portion of the above code to this:

    Imports Microsoft.Office.Interop
    Imports System.Windows.Forms
    Imports System.Runtime.InteropServices 'needed for Marshal
    Public Class Form1
        Dim fileNameExcel As String = Nothing
            Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim newXL As Excel.Application = Nothing
            OpenFileDialog1.Title = "Please select a file to open" 'Prompts user for a file to open
            OpenFileDialog1.InitialDirectory = "C:" 'sets initial directory to the C drive
            OpenFileDialog1.Filter = "Excel files (*.xls)|*.xlsx" 'sets the open filter to only allow Excel files
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                fileNameExcel = OpenFileDialog1.FileName
                newXL = New Excel.Application()
                newXL.Visible = False
            End If
            If Not newXL Is Nothing Then
                Marshal.FinalReleaseComObject(newXL)
                newXL = Nothing
            End If
        End Sub
        Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
            TextBox1.Text = fileNameExcel
        End Sub
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            End
        End Sub
    End Class

    I would then use the file chosen to do all the data merging...If this should be in another thread let me know. I was just trying to get an idea on this part and make sure i'm heading in the right direction.

    Thanks again to all!!




    I would start another thread for more exposure and reference back to this thread so others who are looking to assist you will have history. Personally I have done little with MS-Word automation, not enough to assist.

    KSG

    Wednesday, April 25, 2012 12:24 AM
    Moderator
  • OK, will start a new one - thanks for the help Kevin
    Wednesday, April 25, 2012 12:39 PM
  • i got issue on this lines:

    xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open("D:\Users\morin\Desktop\test.xls")

    after second line its stac... when i type:

    xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlApp.Workbooks.Open("D:\Users\morin\Desktop\test.xls")

    its works why ?

    Tuesday, November 13, 2012 9:42 AM
  • i got issue on this lines:

    xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open("D:\Users\morin\Desktop\test.xls")

    after second line its stac... when i type:

    xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlApp.Workbooks.Open("D:\Users\morin\Desktop\test.xls")

    its works why ?

    Hello, I am not clear what you mean by "after second line its stac... when I type", specifically stac.

    What I can tell you that when working with Excel with .NET solutions/projects it is easy for things not to work as expected and can be difficult to track down. One of the common things is using public variables to work with Excel automation rather than variables local to a procedure or function. Below is an example of working with Excel with local variables to a procedure which opens a file, reads a cell from a specific worksheet then disposes all objects used.

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module OpenWorkSheets2
        Public Sub DemoRead1(ByVal FileName As String, ByVal SheetName As String, ByVal TheCell As String)
            If IO.File.Exists(FileName) Then
                Dim Proceed As Boolean = False
                Dim CellValue As String = ""
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim SingleCellToRead As Excel.Range = Nothing
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
                xlWorkSheets = xlWorkBook.Sheets
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
                Next
                If Proceed Then
                    Dim xlCells As Excel.Range = Nothing
                    SingleCellToRead = xlWorkSheet.Range(TheCell)
                    CellValue = String.Format("{0} = '{1}'", TheCell, SingleCellToRead.Value)
                    SingleCellToRead.Value = "Some value"
                Else
                    MessageBox.Show("'" & SheetName & "' not located in '" & FileName & "'")
                End If
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
                If Not SingleCellToRead Is Nothing Then
                    Marshal.FinalReleaseComObject(SingleCellToRead)
                    SingleCellToRead = Nothing
                End If
                If Not xlWorkSheets Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkSheets)
                    xlWorkSheets = Nothing
                End If
                If Not xlWorkSheet Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
                End If
                If Not xlWorkBook Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkBook)
                    xlWorkBook = Nothing
                End If
                If Not xlWorkBooks Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkBooks)
                    xlWorkBooks = Nothing
                End If
                If Not xlApp Is Nothing Then
                    Marshal.FinalReleaseComObject(xlApp)
                    xlApp = Nothing
                End If
                If Not String.IsNullOrWhiteSpace(CellValue) Then
                    MessageBox.Show(CellValue)
                End If
            Else
                MessageBox.Show("'" & FileName & "' not found.")
            End If
        End Sub
    End Module

    Also here is a good sample project to learn the basics of working with Excel in VB.NET http://code.msdn.microsoft.com/Basics-of-using-Excel-4453945d

    Another one with many examples http://www.siddharthrout.com/vb-dot-net-and-excel/


    KSG

    Tuesday, November 13, 2012 12:50 PM
    Moderator