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
|
Similar Threads
