ADO limitation to number of excel 2007 columns??

P

pb

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top