ConnectionStringEx

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>
   <System.Diagnostics.DebuggerStepThrough()> _
   <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>
   <System.Diagnostics.DebuggerStepThrough()> _
   <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>
   <System.Diagnostics.DebuggerStepThrough()> _
   <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
      Console.WriteLine(sender.ConnectionString)
      Console.WriteLine()

   End Sub
   <System.Diagnostics.DebuggerStepThrough()> _
   <Runtime.CompilerServices.Extension()> _
   Public Sub ConnectionStringExUpdatable(ByVal sender As OleDb.OleDbConnection, _
                                 ByVal DataSource As String, _
                                 ByVal TheProvider As ExcelProvider, _
                                 ByVal Header As UseHeader)

      SetVersions(TheProvider)

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

   End Sub
End Module

Private FileName As String = "MyExcel.xls"
. . .
If IO.File.Exists(FileName) Then
Dim cn As New System.Data.OleDb.OleDbConnection()
cn.ConnectionStringEx(FileName, ExcelProvider.XLS, ImportExportMode.AsText, UseHeader.No)
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)


Private FileName As String = "MyExcel.xlsx"
. . .
If IO.File.Exists(FileName) Then
Dim cn As New System.Data.OleDb.OleDbConnection()
cn.ConnectionStringEx(FileName, ExcelProvider.XLSX, ImportExportMode.AsText, UseHeader.Yes)
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)