title: Word VBA ADO connection to Excel file with cells containing more than 255 characters
body: <p>I currently use the following ADODB connection string to retrieve data from an Excel workbook (the oConnection has been declared as a new ADODB.Connection and the oRecordSet as a new ADODB.RecordSet):</p>
  
  <pre><code>With oConnection
     .Provider = "Microsoft.ACE.OLEDB.12.0"
     .ConnectionString = "Data Source=C:\Myfile.xlsx;Mode=Read;Extended Properties=""Excel 12.0;HDR=Yes;"""
     .Open
     Set oRecordSet = .Execute(mySqlStatement)
  End With
  </code></pre>
  
  <p>I add the record set to an array and everything seems to be working fine. However if a cell in the Excel sheet contains more than 255 characters the text is being truncated/the characters above 255 are being cut off.</p>
  
  <p>The entire function that I use for connecting to the Excel file is the following:</p>
  
  <pre><code>Public Function RecordSet2Matrix(mSqlStatement As String) As Variant
      Dim oSettingsFolder  As String
      Dim oSettingsFile    As String
      Dim oConnection      As New ADODB.Connection
      Dim oRecordSet       As New ADODB.Recordset
      Dim oRecordArray     As Variant
      Dim oTempArray       As Variant
      Dim x                As Long
      Dim y                As Long
      Dim xUpper           As Long
      Dim yUpper           As Long
  
      'Error handling
      On Error GoTo errHandler
  
      'Get location and settings file
      oSettingsFolder = GetFolder(xFolderEnum.FolderOther)
      oSettingsFile = xSettingsFile
      oTempArray = Array()
  
      'Check if settings file exists
      If Len(Dir(oSettingsFolder &amp; oSettingsFile)) = 0 Then
          RecordSet2Matrix = oTempArray
          Exit Function
      End If
  
      'Create connection to settings file
      With oConnection
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .ConnectionString = "Data Source=" &amp; oSettingsFolder &amp; oSettingsFile &amp; _
                              ";Mode=Read;Extended Properties=""Excel 12.0;HDR=Yes;"""
          .Open
          Set oRecordSet = .Execute(mSqlStatement)
      End With
  
      'Read the settings file and add data to an array
      If oRecordSet.EOF Then
          oTempArray = Array()
      Else
          oRecordArray = oRecordSet.GetRows
  
          xUpper = UBound(oRecordArray, 2)
          yUpper = UBound(oRecordArray, 1)
  
          'Resize array to fit all records
          ReDim oTempArray(xUpper, yUpper)
          For x = 0 To xUpper
              For y = 0 To yUpper
                  oTempArray(x, y) = oRecordArray(y, x)
              Next y
          Next x
      End If
  
      'Close connection to file
      If CBool(oRecordSet.State And adStateOpen) Then
          oRecordSet.Close
      End If
  
      'Return record set
      RecordSet2Matrix = oTempArray
  
  errHandler:
      If Err.Number &lt;&gt; 0 Then
          ShowErrorMessage Err.Number, Err.Description, "modADO.RecordSet2Matrix"
          Set oRecordSet = Nothing
          RecordSet2Matrix = oTempArray
      End If
  
  cleanUp:
      Set oRecordSet = Nothing
      Set oTempArray = Nothing
  End Function
  </code></pre>
  
  <p>Then I run the actual SELECT statement to get the data from Excel:</p>
  
  <pre><code>Public Function GetFirmDetails(mFirmName As String) As xIniDataRecord
      Dim oSqlStatement  As String
      Dim oDataArray     As Variant
      Dim oReturnRec     As xIniDataRecord
  
      'Error handling
      On Error GoTo errHandler
  
      'SQL query for retrieving office details
      oSqlStatement = "SELECT [FirmDisplayName], [FirmPrintName], [FirmInfoA]" &amp; _
                      "FROM   [Firms$] " &amp; _
                      "WHERE  [FirmDisplayName] = '" &amp; mFirmName &amp; "';"
  
      'Return query to array
      oDataArray = RecordSet2Matrix(oSqlStatement)
  
      'Exit function if array is empty
      If UBound(oDataArray) &lt; 0 Then
          GetFirmDetails = oReturnRec
          Exit Function
      End If
  
      'Return array data to return record
      With oReturnRec
          .FirmDisplayName = oDataArray(0, 0)
          .FirmName = oDataArray(0, 1)
          .FirmInfoA = oDataArray(0, 2)
      End With
  
      'Return data record
      GetFirmDetails = oReturnRec
  
  errHandler:
      If Err.Number &lt;&gt; 0 Then
          ShowErrorMessage Err.Number, Err.Description, "modADO.GetFirmDetails"
          GetFirmDetails = oReturnRec
      End If
  
  cleanUp:
      Set oDataArray = Nothing
  End Function
  </code></pre>
  
  <p>I have been looking into other questions similar to mine but not really found the solution.</p>
  
  <p>Does anybody know how to avoid the 255 character limitation?</p>
  
  <p>NB! I am running Excel 2007.</p>