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!