ExcelADO - 使用 Range 从 Excel 中使用 VBScript 获取数据

发布于 2024-08-21 21:25:27 字数 992 浏览 4 评论 0原文

鉴于此处所述的 ExcelADO 的优点，我决定使用 Excel ADO用于 QTP 自动化。这是我使用的代码 -

'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()

在上面提到的查询中，“qwerty”是 Excel 工作表中单元格区域的名称。如果单元格范围是两个或多个单元格的名称，上述代码段可以完美工作。我发现使用命名单元格范围而不是使用“工作表名称”和/或“行和列位置”非常有用

现在，当我仅命名一个单元格并使用上面的一段代码时，会引发以下异常

“ Microsoft Jet 数据库引擎找不到对象“qwerty”。确保对象存在并且您正确拼写其名称和路径名

代码：80040E37

“

在我看来，只有当范围使用 2 个或更多单元格时，才可以使用范围（命名单元格）获取数据。这是正确的吗？如果是这样，那么如何才能获取数据？我只获取一个指定单元格的数据？

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

提笔落墨 2024-08-28 21:25:27

下面的两个示例都适合我，如您所见，除了 IMEX=1 之外，它与您的示例非常相似，这可能会或可能不会产生影响。

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

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

回复收藏 0 原文

~没有更多了~