Reading from excel - ADODB

Ask general questions here.
jainaakash
Posts: 48
Joined: Thu Jun 10, 2010 12:06 pm

Reading from excel - ADODB

Post by jainaakash » Tue Jul 13, 2010 12:08 pm

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

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Reading from excel - ADODB

Post by Support Team » Tue Jul 13, 2010 12:39 pm

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
.
Image

jainaakash
Posts: 48
Joined: Thu Jun 10, 2010 12:06 pm

Re: Reading from excel - ADODB

Post by jainaakash » Fri Jul 16, 2010 11:26 am

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

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Reading from excel - ADODB

Post by Support Team » Fri Jul 16, 2010 1:33 pm

Hi,
jainaakash wrote: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
.
Image

jainaakash
Posts: 48
Joined: Thu Jun 10, 2010 12:06 pm

Re: Reading from excel - ADODB

Post by jainaakash » Fri Jul 16, 2010 1:36 pm

Thanks Peter,

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

Thanks
Aakash