Opening Word and Excel Files from VB.net

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

OK, will start a new one - thanks for the help Kevin

Answer 16

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 ?

Answer 17

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