ADO limitation to number of excel 2007 columns??
 

Hello all,

The following VBA code should theoretically return all the column
names from an Excel 2007 sheet. What I find is that it only returns
the first 255 even though there are many more in the spreadsheet (a
new feature of excel 2007).

Can anyone tell me how to resolve this?

Thanks,

Phil

Private Sub getXL2007ColumnNames()

Dim count As Integer
Dim fName As String
Dim sheetname As String

Dim cnSim As New ADODB.Connection
Dim rsSchema As New ADODB.Recordset

fName = "C:\demo data\myfile.xlsx"
sheetname = "mysheet$"

Set cnSim = New ADODB.Connection

'cnSim.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" & fName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
cnSim.ConnectionString = "Driver={Microsoft Excel Driver (*.xls,
*.xlsx, *.xlsm, *.xlsb)};DBQ=" & fName
cnSim.Open

Dim aRestrictions As Variant
aRestrictions = Array(Empty, Empty, sheetname, Empty)
Set rsSchema = cnSim.OpenSchema(adSchemaColumns, aRestrictions)

rsSchema.MoveFirst
Do Until rsSchema.EOF = True
count = count + 1
Debug.Print (rsSchema!Column_Name)
rsSchema.MoveNext
Loop

MsgBox "Fields = " & count

rsSchema.Close
Set rsSchema = Nothing
cnSim.Close
Set cnSim = Nothing

End Sub