Tag Archives: .NET

Start Indices of VBA Collection and ArcGIS IPointCollection

Note that VBA Collection starts with an annoying 1, while IpointCollection starts with 0. phew.

The much hated Excel Chart Object

For more details, visit here:

http://msdn.microsoft.com/en-us/library/aa213725(v=office.11).aspx

 

When you find that the Xapp.SHEET call in VB.NET hangs, this might be the problem:
What you are trying to call is a CHART OBJECT, not a SHEET, although it looks like one!
Chart objects only have a tab at the bottom of Excel, but they do not contain cells.

To activate a sheet, make this call:
XApp.Charts(Sheetnumber).Activate()

I hope this helps some people out there!

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!