Reading over 254 columns from an Excel 2010 file using ACE SQL server
<p>I am using the connection string </p>

<pre><code>szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
"Data Source=" &amp; fpath &amp; fname &amp; ";" &amp; _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"

Set rsData = New ADODB.Recordset
'Run the query as adCmdText
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

'Check if data exists for query
If Not rsData.EOF Then
    rtnArr = rsData.GetRows  'if the recordset contains data populate the array
Else
    dEmp = True 'no data flag true
    ReDim rtnArr(0 To 0, 0 To 0)
End If
</code></pre>

<p>to run SQL queries in VBA from an external Excel 2010 file. The file has over 40 sheets and each sheet is approx. 369 columns wide, currently the SQL is only returning the first 255 columns of data as per the SQL server limitations. A number of forums discussing this problem suggest transforming the workbook into a flat (.csv) however this wouldn't be ideal. Does anyone have an alterative solution to this? Or a way of forcing through the extra columns? </p>

<p>Thanks!</p>