Reading from excel - ADODB

[jainaakash]

Hi Team,

I am trying to create a ADODB connection object and connect to excel to read the data, but I am getting the below error:
-----------
Unexpected exception occured: System.Runtime.InteropServices.COMException (0x80004005): [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at excel_ADO.Recording1.readExcel() in C:\excel_ADO\Recording1.UserCode.vb:line 43
-----------

My Code:
====================================
Dim objAdoConn, objAdoRs
Dim strFile, strSql
objAdoConn = CreateObject("ADODB.Connection")
objAdoRs = CreateObject("ADODB.Recordset")

strFile = "C:\excel_ADO\ADOExcel.xlsx"

objAdoConn.Open ("DRIVER={Microsoft Excel Driver (*.xlsx)};DBQ="& strFile &";Readonly=True;")

strSql = "Select * from [sheet1$]"

objAdoRs.CursorLocation=3
objAdoRs.Open (strSql, objAdoConn)

While objAdoRs.EOF = False
msgbox(objAdoRs.fields("col1").Value)
msgbox(objAdoRs.fields("col2").Value)
End while

objAdoRs.Close
objAdoRs.ActiveConnection = Nothing
objAdoConn.Close
objAdoConn = Nothing
====================================

I have also tried below:
objAdoConn.Open ("DRIVER={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};DBQ="& strFile &";Readonly=True;")
and still get the same error.

I get another error as below with the connection string as:
objAdoConn.Open ("DRIVER={Microsoft Excel Driver (*.xls)};DBQ="& strFile &";Readonly=True;")
Error:
===============
Unexpected exception occured: System.Runtime.InteropServices.COMException (0x80004005): [Microsoft][ODBC Excel Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x1cd8 Thread 0x1c60 DBC 0x3d810c Excel'.
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at excel_ADO.Recording1.readExcel()
==============

There might be some simple thing I am missing. Please let me know if I need to add any reference or something. I have used this with QTP and works well.

======
Also, I have added the following code (not sure if thats needed though)
Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.ComClassAttribute
=======

Thanks and Regards,
Aakash

[Support Team]

Hi,

Could that be a security issue, i.e. the user not having the rights to access the registry? Maybe try starting the executable with administrative rights!

Regards,
Peter
Ranorex Support Team

[jainaakash]

Hi Peter,

Thanks for directing me to the link. That has help a bit.
Now, coming back to the issue with doing it the way I mentioned in my post earlier, I was able to do that using a system DSN. But looking at the error below, I think that there is something missing from the Ranorex library. I guess Ranorex only supports DRIVER={Microsoft Excel Driver (*.xls)} (i.e. till Excell 2003) and is missing support for the DRIVER={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb) (excel 2007).

=== Error===
Unexpected exception occured: System.Runtime.InteropServices.COMException (0x80004005): [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
=====

I could be completely wrong in this, but thats what I felt as the code worked just fine when I used System DSN. Please suggest.

Thanks and Regards,
Aakash

[Support Team]

Hi,

I think that there is something missing from the Ranorex library. I guess Ranorex only supports DRIVER={Microsoft Excel Driver (*.xls)} (i.e. till Excell 2003) and is missing support for the DRIVER={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb) (excel 2007).

Ranorex library doesn't include any Drivers. The driver support is from .Net Framework. If you are using Ranroex Studio, you have also fully access to the .Net Framework of Microsoft.

But please take care with the connection string for *.xlsx files. Use following connection string for *.xlsx files:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + fileName + "\";Extended Properties=\"Excel 12.0;HDR=NO\";";

Regards,
Peter
Ranorex Support Team

[jainaakash]

Thanks Peter,

makes complete sense
So, I was right in saying that I could be wrong

Thanks
Aakash