Open Excel using OleDb with a language extension method

kevininstructor · 07-01-2011, 01:24 AM

Requires Framework 3.5 or higher

Example opening a workbook where the sheets have the first row as the column header and the Excel type is Office 2007

Code:
Private ExcelFile As String = "Testing02.xlsx"
. . .
        Dim cn As New System.Data.OleDb.OleDbConnection()
        cn.ConnectionStringEx(ExcelFile, ExcelProvider.XLSX, ImportExportMode.AsText, UseHeader.Yes)
        cn.Open()

Tinker with the params i.e.
IMEX can make a difference
Use or not using headers will be important.

The main thing is you do not need to play with the actual connection string.

If something is not clear ask.

Place the following in a code module

Code:
Module ExcelConnectionHelpers
    Public Enum ImportExportMode
        Normal = 0
        ''' <summary>
        ''' Read "intermixed" (numbers, dates, strings etc) data columns as text. 
        ''' </summary>
        ''' <remarks></remarks>
        AsText = 1
    End Enum
    Public Enum UseHeader
        ''' <summary>
        ''' Indicates that the first row contains columnnames, no data
        ''' </summary>
        ''' <remarks></remarks>
        Yes
        ''' <summary>
        ''' Indicates that the first row does not contain columnnames
        ''' </summary>
        ''' <remarks></remarks>
        No
    End Enum
    Public Enum ExcelProvider
        ''' <summary>
        ''' Microsoft.Jet.OLEDB.4.0
        ''' </summary>
        ''' <remarks></remarks>
        XLS
        ''' <summary>
        ''' Microsoft.ACE.OLEDB.12.0
        ''' </summary>
        ''' <remarks></remarks>
        XLSX
    End Enum

    Private OleExelVersion As String = "8.0"
    Private Provider As String = "Microsoft.Jet.OLEDB.4.0"

    Private Sub SetVersions(ByVal TheProvider As ExcelProvider)
        If TheProvider = ExcelProvider.XLSX Then
            OleExelVersion = "12.0"
            Provider = "Microsoft.ACE.OLEDB.12.0"
        Else
            OleExelVersion = "8.0"
            Provider = "Microsoft.Jet.OLEDB.4.0"
        End If

    End Sub
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="DataSource">File name to open including path if needed</param>
    ''' <param name="TheProvider"></param>
    ''' <remarks></remarks>
    <Runtime.CompilerServices.Extension()> _
    Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                  ByVal DataSource As String, _
                                  ByVal TheProvider As ExcelProvider)

        SetVersions(TheProvider)

        sender.ConnectionString = _
        <Connection>
         provider=<%= Provider %>;  
         data source='<%= DataSource %>';
         Extended Properties=Excel <%= OleExelVersion %>;
      </Connection>.Value

    End Sub
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="DataSource">File name to open including path if needed</param>
    ''' <param name="TheProvider"></param>
    ''' <param name="MixType"></param>
    ''' <remarks></remarks>
    <Runtime.CompilerServices.Extension()> _
    Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                  ByVal DataSource As String, _
                                  ByVal TheProvider As ExcelProvider, _
                                  ByVal MixType As ImportExportMode)

        SetVersions(TheProvider)

        sender.ConnectionString = _
        <Connection>
         provider=<%= Provider %>; 
         data source='<%= DataSource %>'; 
         Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;"
      </Connection>.Value

    End Sub
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="DataSource">File name to open including path if needed</param>
    ''' <param name="TheProvider"></param>
    ''' <param name="MixType"></param>
    ''' <param name="Header"></param>
    ''' <remarks></remarks>
    <Runtime.CompilerServices.Extension()> _
    Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                  ByVal DataSource As String, _
                                  ByVal TheProvider As ExcelProvider, _
                                  ByVal MixType As ImportExportMode, _
                                  ByVal Header As UseHeader)

        SetVersions(TheProvider)

        sender.ConnectionString = _
        <Connection>
         provider=<%= Provider %>; 
         data source='<%= DataSource %>'; 
         Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;HDR=<%= Header.ToString %>;"
      </Connection>.Value

    End Sub
End Module