Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL 2003 VBA form Listbox-limitation in max number of columns | Excel Programming | |||
Number of Excel 2007 Columns | Excel Discussion (Misc queries) | |||
creating worksheet with code - limitation on number of worksheets? | Excel Programming | |||
Excel 2007 Row Height Limitation | Excel Discussion (Misc queries) | |||
Excel limitation on number of hyperlinks in worksheet? | Links and Linking in Excel |