How to Use Use Late Binding to Get Excel Instance

How to use use late binding to get excel instance?

First: Late binding in C# is quite a pain. It's best to avoid it.
Second: Late binding in C# is a pain. Use the PIA!

Ok, that being said, here is what you need to do in order to use late binding: Remove the reference to the Office 2003 PIAs and instead add a COM import of the interface required by AccessibleObjectFromWindow, i.e. the Excel.Window interface:

[Guid("00020893-0000-0000-C000-000000000046")]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface ExcelWindow
{
}

You can retrieve this interface using a tool like Reflector (or by simply pressing F12 on the type Excel.Window while the reference to the Excel PIA is still in your project)

That being done you will have to modify the signature of AccessibleObjectFromWindow to match the imported ExcelWindow interface:

[DllImport("Oleacc.dll")]
static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, out ExcelWindow ptr);

Finally, you must use reflection to get the Excel.Application object from the ExcelWindow object:

object xlApp = ptr.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, ptr, null);

If your code is going to make a lot of calls into Excel's OM it might be easier to use VB with Option Strict turned off (or wait for C#4.0 ;-). Or, if you don't want to change from C#, it might be a good idea to create a wrapper class for the late binding calls.

Full Sample

Here is a fully functional sample (based on an article by Andrew Whitechapel):

using System;
using System.Globalization;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;

namespace ExcelLateBindingSample
{
    /// <summary>
    /// Interface definition for Excel.Window interface
    /// </summary>
    [Guid("00020893-0000-0000-C000-000000000046")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface ExcelWindow
    {
    }

    /// <summary>
    /// This class is needed as a workaround to http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
    /// Excel automation will fail with the follwoing error on systems with non-English regional settings:
    /// "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" 
    /// </summary>
    class UILanguageHelper : IDisposable
    {
        private CultureInfo _currentCulture;

        public UILanguageHelper()
        {
            // save current culture and set culture to en-US 
            _currentCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
        }

        public void Dispose()
        {
            // reset to original culture 
            System.Threading.Thread.CurrentThread.CurrentCulture = _currentCulture;
        }
    }

    class Program
    {
        [DllImport("user32.dll", SetLastError = true)]
        static extern IntPtr FindWindow(string lpClassName, string lpWindowName);

        [DllImport("Oleacc.dll")]
        static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, out ExcelWindow ptr);

        public delegate bool EnumChildCallback(int hwnd, ref int lParam);

        [DllImport("User32.dll")]
        public static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);

        [DllImport("User32.dll")]
        public static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);

        public static bool EnumChildProc(int hwndChild, ref int lParam)
        {
            StringBuilder buf = new StringBuilder(128);
            GetClassName(hwndChild, buf, 128);
            if (buf.ToString() == "EXCEL7")
            {
                lParam = hwndChild;
                return false;
            }
            return true;
        }

        static void Main(string[] args)
        {
            // Use the window class name ("XLMAIN") to retrieve a handle to Excel's main window.
            // Alternatively you can get the window handle via the process id:
            // int hwnd = (int)Process.GetProcessById(excelPid).MainWindowHandle;
            //
            int hwnd = (int)FindWindow("XLMAIN", null); 
            
            if (hwnd != 0)
            {
                int hwndChild = 0;

                // Search the accessible child window (it has class name "EXCEL7") 
                EnumChildCallback cb = new EnumChildCallback(EnumChildProc);
                EnumChildWindows(hwnd, cb, ref hwndChild);

                if (hwndChild != 0)
                {
                    // We call AccessibleObjectFromWindow, passing the constant OBJID_NATIVEOM (defined in winuser.h) 
                    // and IID_IDispatch - we want an IDispatch pointer into the native object model.
                    //
                    const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                    Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
                    ExcelWindow ptr;

                    int hr = AccessibleObjectFromWindow(hwndChild, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), out ptr);

                    if (hr >= 0)
                    {
                        // We successfully got a native OM IDispatch pointer, we can QI this for
                        // an Excel Application using reflection (and using UILanguageHelper to 
                        // fix http://support.microsoft.com/default.aspx?scid=kb;en-us;320369)
                        //
                        using (UILanguageHelper fix = new UILanguageHelper())
                        {
                            object xlApp = ptr.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, ptr, null);

                            object version = xlApp.GetType().InvokeMember("Version", BindingFlags.GetField | BindingFlags.InvokeMethod | BindingFlags.GetProperty, null, xlApp, null);
                            Console.WriteLine(string.Format("Excel version is: {0}", version));
                        }
                    }
                }
            }
        }
    }
}

And this would be the same solution without PIAs in VB (Note that OM call are much more readable; however, the code to get access to the OM would be the same):

Option Strict Off

Imports System.Globalization
Imports System.Runtime.InteropServices
Imports System.Text

Module ExcelLateBindingSample

    ''' <summary>
    ''' Interface definition for Excel.Window interface
    ''' </summary>
    <Guid("00020893-0000-0000-C000-000000000046"), _
    InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> _
    Public Interface ExcelWindow
    End Interface

    ''' <summary>
    ''' This class is needed as a workaround to http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
    ''' Excel automation will fail with the follwoing error on systems with non-English regional settings:
    ''' "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" 
    ''' </summary>
    Class UILanguageHelper
        Implements IDisposable

        Private _currentCulture As CultureInfo

        Public Sub New()
            ' save current culture and set culture to en-US 
            _currentCulture = System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
        End Sub

        Public Sub Dispose() Implements System.IDisposable.Dispose
            'reset to original culture 
            System.Threading.Thread.CurrentThread.CurrentCulture = _currentCulture
        End Sub

    End Class

    <DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
    Private Function FindWindow(ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
    End Function

    <DllImport("Oleacc.dll")> _
    Private Function AccessibleObjectFromWindow(ByVal hwnd As Integer, ByVal dwObjectID As UInt32, ByVal riid() As Byte, ByRef ptr As ExcelWindow) As Integer
    End Function

    Public Delegate Function EnumChildCallback(ByVal hwnd As Integer, ByRef lParam As Integer) As Boolean

    <DllImport("User32.dll")> _
    Public Function EnumChildWindows(ByVal hWndParent As Integer, ByVal lpEnumFunc As EnumChildCallback, ByRef lParam As Integer) As Boolean
    End Function

    <DllImport("User32.dll")> _
    Public Function GetClassName(ByVal hWnd As Integer, ByVal lpClassName As StringBuilder, ByVal nMaxCount As Integer) As Integer
    End Function

    Public Function EnumChildProc(ByVal hwndChild As Integer, ByRef lParam As Integer) As Boolean
        Dim buf As New StringBuilder(128)
        GetClassName(hwndChild, buf, 128)
        If buf.ToString() = "EXCEL7" Then
            lParam = hwndChild
            Return False
        End If
        Return True
    End Function

    Sub Main()
        ' Use the window class name ("XLMAIN") to retrieve a handle to Excel's main window.
        ' Alternatively you can get the window handle via the process id:
        ' Dim hwnd As Integer = CInt(Process.GetProcessById(excelPid).MainWindowHandle);
        '
        Dim hwnd As Integer = CInt(FindWindow("XLMAIN", Nothing))

        If hwnd <> 0 Then
            Dim hwndChild As Integer = 0

            ' Search the accessible child window (it has class name "EXCEL7") 
            Dim cb As New EnumChildCallback(AddressOf EnumChildProc)
            EnumChildWindows(hwnd, cb, hwndChild)

            If hwndChild <> 0 Then
                ' We call AccessibleObjectFromWindow, passing the constant OBJID_NATIVEOM (defined in winuser.h) 
                ' and IID_IDispatch - we want an IDispatch pointer into the native object model.
                '
                Const OBJID_NATIVEOM As UInteger = &HFFFFFFF0&
                Dim IID_IDispatch As New Guid("{00020400-0000-0000-C000-000000000046}")
                Dim ptr As ExcelWindow

                Dim hr As Integer = AccessibleObjectFromWindow(hwndChild, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), ptr)

                If hr >= 0 Then
                    ' We successfully got a native OM IDispatch pointer, we can QI this for
                    ' an Excel Application using reflection (and using UILanguageHelper to 
                    ' fix http://support.microsoft.com/default.aspx?scid=kb;en-us;320369)
                    '
                    Using fixCrash As New UILanguageHelper
                        Console.WriteLine(String.Format("Excel version is: {0}", ptr.Application.Version))
                    End Using
                End If
            End If
        End If

    End Sub

End Module

Using late binding to Excel, how do I move from the active cell to the first cell in the next row?

The answer is, as usual, easy when you know how.

My code a bit further down from the sample in my question looked a bit like this:

object activeCell;
object result;
// in here code to get the correct value into activeCell
if(activeCell.GetType().GetProperty("Offset") == null){
  result = activeCell.GetType().InvokeMember("Offset", BindingFlags.InvokeMethod, null, activeCell, new object[] {1, 0});
}

It was that InvokeMember that threw an exception with the message "Member not found".

When I changed the parameters to InvokeMember to read as follows:

result = activeCell.GetType().InvokeMember("Offset", BindingFlags.InvokeMethod | BindingFlags.GetProperty, null, activeCell, new object[] {1, 0});

then it worked.

Just why it works is still a mystery, which I am sure will earn someone some reputation if they answer it.

VBA Excel instance doesn't close when opened from MS Access - late binding

The underlying process remains since the workbook object was not fully released like you did with the app object. However, this requires you to assign the workbook object in order to release later.

Dim ExcelApp As object, ExcelWbk as Object

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWbk = ExcelApp.Workbooks.Open(CurPath & MainProjectName & ".xlsm", True)
ExcelApp.Visible = False     ' APP RUNS IN BACKGROUND

'... DO STUFF

' CLOSE OBJECTS
ExcelWbk.Close
ExcelApp.Quit

' RELEASE RESOURCES
Set ExcelWbk = Nothing
Set ExcelApp = Nothing

This is true for any COM-connected language like VBA, including:

  • Python: WIN32COM saving/exporting multiple sheets as PDF;
  • R: How to export an Excel sheet range to a picture, from within R, and
  • PHP: Converting excel to pdf using PHP

As shown, even open source can connect to Excel externally like VBA and should always release initialized objects in their corresponding semantics.

Consider refactoring of Excel VBA code to for best practices:

  • Explicitly declare variables and types;
  • Integrate proper error handling (that without can leave resources running);
  • Use With...End With blocks and avoid Activate, Select, ActiveWorkbook, and ActiveSheet (that can cause runtime errors);
  • Declare and use Cell, Range, or Workbook objects and at end uninitialize all Set objects;
  • Use ThisWorkbook. qualifier where needed (i.e., workbook where code resides).

NOTE: Below is untested. So carefully test, debug especially due to all the names being used.

Option Explicit       ' BEST PRACTICE TO INCLUDE AS TOP LINE AND 
                      ' AND ALWAYS Debug\Compile AFTER CODE CHANGES

Public Sub MainProcedure()
On Error GoTo ErrHandle
    ' EXPLICITLY DECLARE EVERY VARIABLE AND TYPE
    Dim FormattedDate As Date, RunDate As Date

    Dim ReportPath As String, MonthlyPath As String, CurPath As String
    Dim ProjectName As String, ExcelFileName As String, FinalExcelFileName As String
    Dim TableName As String, TemplateFileName As String
    Dim SheetToSelect As String, ExcelSheetName As String
    Dim CurSheetName As String
    
    Dim i As Integer, CurRowNum As Long, LastRow As Long
    Dim FirstRowOfSection As Long, LastRowOfSection As Long
    Dim CurCell As Variant, curRange As Range
    
    Dim wb As Workbook
        
    Application.EnableCancelKey = xlDisabled
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    CurPath = ThisWorkbook.Path & "\"                     ' USE ThisWorkbook

    With ThisWorkbook.Worksheets("QReportDates")          ' USE WITH CONTEXT
        FormattedDate = .Range("A2").Value
        RunDate = .Range("B2").Value
        ReportPath = .Range("C2").Value
        MonthlyPath = .Range("D2").Value
        ProjectName = .Range("E2").Value
    End With
    
    CurRowNum = 2
    With ThisWorkbook.Worksheets("QFilesToExportEMail")   ' USE WITH CONTEXT
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
        Set curRange = .Range("B" & CurRowNum & ":B" & LastRow)

        For Each CurCell In curRange
            If CurCell <> "" Then
                FirstRowOfSection = .Columns(2).Find(ExcelFileName).Row
                                                            
                If ExcelSheetName = "" Then
                    ExcelSheetName = TableName
                End If
                                                            
                If CurRowNum = FirstRowOfSection Then
                    SheetToSelect = ExcelSheetName
                End If
                                       
                ' USE WORKBOOK OBJECT
                If IsNull(TemplateFileName) Or TemplateFileName = "" Then
                    Set wb = Workbooks.Add
                Else
                    Set wb = Workbooks.Open(CurPath & TemplateFileName)
                End If
                                       
                wb.SaveAs MonthlyPath & FinalExcelFileName
            End If
                         
            ' USE WORKBOOK OBJECT
            wb.Worksheets(SheetToSelect).Select
            wb.Save
            wb.Close
            Set wb = Nothing                              ' RELEASE RESOURCE
            
            If LastRowOfSection >= LastRow Then
                Exit For
            End If
        Next CurCell

        Set curRange = .Range("A2:A" & LastRow)
        For Each CurCell In curRange
            If CurCell <> "" Then
                CurSheetName = CurCell
    
                If CheckSheet(CurSheetName) Then         ' ASSUMED A SEPARATE FUNCTION
                    ThisWorkbook.Worksheets(CurSheetName).Delete
                End If
    
            End If
        Next CurCell
    End With
    
    ' USE ThisWorkbook QUALIFIER
    ThisWorkbook.Worksheets("QFilesToExportEMail").Delete
    ThisWorkbook.Worksheets("QReportDates").Delete
    ThisWorkbook.Save
    ' ThisWorkbook.Close                                 ' AVOID CLOSING IN MACRO

ExitHandle:
    ' ALWAYS RELEASE RESOURCE (ERROR OR NOT)
    Set curCell = Nothing: Set curRange = Nothing: Set wb = Nothing
    Exit Sub
    
ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume ExitHandle
End Sub

Get UsedRange with Late Binding

Try LateBindingApi.Excel: http://excel.codeplex.com

Same syntax like early bind but its latebind.

C# Read Excel using late binding

It sounds like you're looking at using COM interop/automation with the Excel application installed on client machines.

If your sole requirement is to extract data from an Excel file, you'll be better off using a library that can simply read data out of the file itself, rather than launching the Excel process in the background.

It is faster, cleaner, and more testable.

I've used NPOI for .xls files (and there are certainly others), and there are LOTS of options for .xlsx files. This SO question is about creating a file, but any of the suggested libraries can of course read files as well.

The only time I'd use COM automation is to interact with a running instance of Excel.

Edit (in response to comments)

Here is a sample of getting the values of column B as strings:

using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var stream = new FileStream(@"c:\my_workbook.xls", FileMode.Open);
            var workbook = new HSSFWorkbook(stream);
            stream.Close();

            var sheet = workbook.GetSheet("My Sheet Name");
            var row_enumerator = sheet.GetRowEnumerator();

            while (row_enumerator.MoveNext())
            {
                var row = (Row)row_enumerator.Current;
                var cell = row.GetCell(1); // in Excel, indexes are 1-based; in NPOI the indexes are 0-based
                Console.WriteLine(cell.StringCellValue);
            }

            Console.ReadKey();
        }
    }
}

Related Topics

Navigation Property Should Be Virtual - Not Required in Ef Core

How to Post Data Using Httpclient

How to Render a Razor View to a String in ASP.NET MVC 3

Ajax Method Call

Super-Simple Example of C# Observer/Observable with Delegates

Mirroring Console Output to a File

Should C# Methods That *Can* Be Static Be Static

Is There Any Simple Way to Convert .Xls File to .CSV File? (Excel)

The Input Is Not a Valid Base-64 String as It Contains a Non-Base 64 Character

How to Distinguish Between Multiple Input Devices in C#

Question Mark and Colon in Statement. What Does It Mean

Built in .Net Algorithm to Round Value to the Nearest 10 Interval

How to Dispatch Events in C#

Why Was "Switchto" Removed from Async Ctp/Release

Inno Setup for Windows Service

No Connection String Named 'Myentities' Could Be Found in the Application Config File

How to Read File Using Npoi

Set Timeout to an Operation


Leave a reply



Submit