ExcelADO - Using Range to fetch data from excel using VBScript

Question

Given the advantages of ExcelADO as described here I have decided to use Excel ADO for QTP Automation. So here is the code which I used -

'Open the ADO connection to the Excel workbook
Dim oConn
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=D:\Mine\QTP\Book1.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=NO;"""  


'Set Record Set Object       
Dim oRS 
Set oRS = CreateObject("ADODB.Recordset")

'Execute Query 
oRS.Open "Select * from qwerty", oConn, adOpenStatic 

'Get String
a = oRs.GetString() 

In the query mentioned above 'qwerty' is the name of cell range in Excel Sheet. Above mentioned piece of code works perfect provided cell range is name of two or more cells. I find it very useful to use named cell range instead of using 'sheetname' and/or 'row and column positions'

Now when I name just one cell and use above piece of code then following exception is thrown

" Microsoft Jet database engine could not find object 'qwerty'. Make sure object exists and that you spell its name and path name correctly

Code: 80040E37

"

It looks to me data can be fetched using range (named cells) only when range utilizes 2 or more cells. Is it correct? If so then how can I fetch data for only one named cell?

~ T

Answer 1

Both of the example below work for me, as you can see, it is very similar to your example except for IMEX=1, which may or may not make a difference.

MsgBox fExcelCellADO ("c:\docs\book1.xls","sheet1$b2:b2")
MsgBox fExcelCellADO ("c:\docs\book1.xls","therange")

Function fExcelCellADO(strFileName, strCell)

    Dim cn 
    Dim rs 

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source=" & strFileName & ";" _
             & "Extended Properties='Excel 8.0;HDR=No;IMEX=1';"

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT F1 FROM [" & strCell & "]", cn
    fExcelCellADO = rs.fields("F1")

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Function

Answer 2

Just checked with my colleague on Office 2007 and works like cream. I should soon get Office 2003 Remou - Thanks for your quick response