title: Visual Basic Import Excel Sheet to DataTable
body: <p>I'm currently working with a huge amount of data in Excel, and I want uploaded into a DataTable in VB.Net (70,000 rows per 30 columns) with mixed datatypes.</p>
  
  <p>I'm using the next code to import the information:</p>
  
  <pre><code>Public Function mc_ExcelTableToDataTable(ByRef ExcelApp As Excel.Application, _
                                            ByVal SheetName As String, _
                                            ByVal TableName As String, _
                                            Optional ByVal FilePath As String = "", _
                                            Optional ByVal SQLsentence As String = "") As DataTable
  
  
          Dim vPath As String
          Dim vCloseExcelWorkbook As Boolean = False
  
          If ExcelApp.ActiveWorkbook IsNot Nothing Then
              vPath = IIf(FilePath = "", ExcelApp.ActiveWorkbook.FullName, FilePath)
          Else
              vPath = FilePath
          End If
  
          If SQLsentence = "" And ExcelApp.ActiveWorkbook Is Nothing Then
              vCloseExcelWorkbook = True
              ExcelApp.Workbooks.Open(vPath)
          End If
  
          Dim vRange As String = ExcelApp.Sheets(SheetName).ListObjects(TableName).Range.AddressLocal
  
          vRange = vRange.Replace("$", "")
  
          Dim vCNNstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
                                      "Data Source= " &amp; vPath &amp; ";" &amp; _
                                      "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
  
          Dim ExcelCNN As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(vCNNstring)
  
          Dim vSQL As String = IIf(SQLsentence = "", _
                                   "SELECT * FROM [" + SheetName + "$" &amp; vRange &amp; "]", _
                                   SQLsentence)
  
          Dim ExcelCMD As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(vSQL, ExcelCNN)
  
          ExcelCNN.Open()
          Dim ExcelDS As System.Data.DataSet = New DataSet
          ExcelCMD.Fill(ExcelDS)
  
          mc_ExcelTableToDataTable = ExcelDS.Tables(0).Copy
  
          ExcelCMD = Nothing
          ExcelCNN.Close()
  
          ExcelDS.mc_Dispose()
  
          If vCloseExcelWorkbook Then ExcelApp.ActiveWorkbook.Close(False)
  
          GCcleaner()
      End Function
  </code></pre>
  
  <p>But, VB.Net give me the following error:</p>
  
  <p><img src="https://i.stack.imgur.com/osjjv.png" alt="err"></p>
  
  <p>Somebody knows what is the maximum capacity of the provider ACE.OLEDB.12.0?
  Or how fix this issue?</p>
  
  <p>FYI, this is the SQL select sentence:</p>
  
  <pre><code>SELECT * FROM [Workflow data$A1:AC70276]
  </code></pre>
  
  <p>It's important mention that if I limit the rows to 20000 (SELECT * FROM [Workflow data$A1:AC20000]), the process works fine!</p>