The Complete guide to calling Excel Objects in VB .NET

This guide explains how to use Excel Objects in VB .NET

1. Import Microsoft.Office.Interop

2. Call Excel inside a Try-Catch Statement

Dim XApp As Excel.Application
Dim XBooks As Excel.Workbooks
Dim XBook As Excel.Workbook

Dim XSheet As Excel.Worksheet
Dim XRange As Excel.Range
XApp = CreateObject("Excel.Application")

XApp.Visible = False 'Run Excel in the background

XBooks = XApp.Workbooks
XBook = XBooks.Open(templateXLSX)

XSheet = XApp.Application.Sheets("Raw Data")

3. Manipulate Excel Object

XRange = XApp.Range("A1")

XRange.Value2 = "NEW VALUE"

4. Save the Excel Files

XBook.Close(False) ‘The boolean argument indicates whether the file is saved before closing

5. Clean all Excel objects

Cleaning objects are required, or else the Excel object will not go away in Task Manager

     While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XBook_New) <> 0)
            End While
            XBook = Nothing

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XBooks) <> 0)
            End While
            XBooks = Nothing
            XApp.Quit()

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XApp) <> 0)
            End While
            XApp = Nothing

For each object, do ReleaseComObject and then set its value to Nothing

If the spreadsheets are updated via a Loop, check carefully that every object is cleaned before it is called again.

For example:

XRange = XApp.Range(“A1″)

XRange.Value2=”NEW VALUE 1”

System.Runtime.InteropServices.Marshal.ReleaseComObject(XRange)

XRange = XApp.Range(“A2”) ‘XRange is cleaned before it is called.

 

Also, call Garbage Collector twice as follows:

       GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

6. For all language environments to run your program without MUI installed, add this to your code:

   Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
   System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

at the beginning after setting Xapp, and at the end

    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

That’s all!