pb101

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



Re: Visual Studio Tools for Office ADO limitation to number of excel 2007 columns??

Dennis Wallentin

Hi,

Probably due to the driver in use - "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; - Or do You find the same limitation when using the Microsoft.ACE.OLEDB.12.0 driver






Re: Visual Studio Tools for Office ADO limitation to number of excel 2007 columns??

pb101

No, I geet the same problem whichever driver is used.



Re: Visual Studio Tools for Office ADO limitation to number of excel 2007 columns??

Dennis Wallentin

Phil,

I've made some tests with another approach then Yours and got the same output. I will make some tests with ADO.NET to see if it's the same outcome as with classic ADO.

Let see if anyone else can come up with an explanation for it or if we should consider it to be a bug.






Re: Visual Studio Tools for Office ADO limitation to number of excel 2007 columns??

pb101

Thanks Dennis.

It is quite obviously a bug or a 'design feature'. How can we get microsoft to give us the official answer

Phil





Re: Visual Studio Tools for Office ADO limitation to number of excel 2007 columns??

Dennis Wallentin

Phil,

I recently discovered that MSFT has released a package of Data Connectivity Components but I've not had the time to check out out:

2007 Office System Driver: Data Connectivity Components

I'm not sure if they differ or not from the one shipped with Office 2007.

MSFT scan the forums on a regular basis so hopefully they will have the time for it.






Re: Visual Studio Tools for Office ADO limitation to number of excel 2007 columns??

pb101

Dennis,

These are the drivers I used as I don't have office 2007, so I guess they are the same.

Phil