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!

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: