ADO read xlsx with HDR=No
Trying to use ADO in Excel VBA to open an xlsx file with HDR=No. When I try to set HDR=NO I get a runtiime error. What am I doing wrong?
Code:
Sub TestADO()
'--------------------------------------------------------
' Reference to Microsoft ActiveX Data Objects 6.1 Library
'--------------------------------------------------------
Const strPath As String = "C:\myfolder\"
Const strFN As String = "myfile.xlsx"
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cnString As String, rsString As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
rsString = "Select * from [A1:XFD1048576]"
'---------
' works...
'---------
cnString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
"FIL=Excel " & Application.Version & ";DriverID=1046;ReadOnly=1;DBQ=" & _
strPath & strFN
cn.Open cnString
rs.Open rsString, cn, adOpenStatic, adLockOptimistic, adCmdText
Debug.Print "this one works..."
Debug.Print rs.RecordCount, rs.Fields.Count
Debug.Print rs.Fields(0).Name, rs.Fields(0).Value
Debug.Print "provider = " & cn.Provider
Debug.Print "data source = " & cn.Properties("Data Source")
Debug.Print "extended properties = " & cn.Properties("Extended Properties")
Debug.Print
rs.Close
cn.Close
'---------
' fails...
'---------
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Properties("Data Source").Value = strPath & strFN
cn.Properties("Extended Properties").Value = "Excel 12.0;HDR=No"
cn.Open
rs.Open rsString, cn, adOpenStatic, adLockOptimistic, adCmdText
Debug.Print "this one fails..."
Debug.Print rs.RecordCount, rs.Fields.Count
Debug.Print "provider = " & cn.Provider
Debug.Print "data source = " & cn.Properties("Data Source")
Debug.Print "extended properties = " & cn.Properties("Extended Properties")
Debug.Print rs.Fields(0).Name, rs.Fields(0).Value '<== this line fails...
'--------------------------------------------------------------------------
'Run-time error '-2147217887(80040e21)':
'
'You cannot record your changes because a value you entered voilates
'the settings defined for this table or list (for example, a value is less)
'than the minimum or greater than the maximum). Correct the error and
'try again.
'--------------------------------------------------------------------------
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End SubEnd Sub