51Testing软件测试论坛

标题: Help -- error happened on QTP reading data from Excel file [打印本页]

作者: billxin 时间: 2010-5-3 09:07
标题: Help -- error happened on QTP reading data from Excel file
I met problem as follow, when I read excel data from sheet1, column2, first 3 rows can be retrieved from QTP, but the row4, row 5 data couldnot get retrieved from QTP.

data in excel sheet1, column2---
Row0: Column2 ---> header
Row1: 1       ---> can be read
Row2: 2       ---> can be read
Row3: 3       ---> can be read
Row4: line5    ---> error happened here
Row5: line6    ---> error happened here

My scripts:
=====================================================================
Dim Connect1 'As New ADODB.Connection
Dim strQuery
Dim DataRecordSet
Dim arrFields(10)

Set Connect1 = Createobject("ADODB.Connection")
With Connect1
.Provider = "Microsoft.JET.OLEDB.4.0"
.ConnectionString = "Data Source = D:\QTP\MySpreadsheet.xls;Extended Properties=Excel 8.0;"
.Open
End with

' retrieve excel data value
'strQuery = "Select  *  from ["  & strTableName & "$] where " &  strColumnNM1 & " = " &  "'" & strColValue1 & "'"

strQuery = "Select  *  from [Sheet1$] "
'  msgbox strQuery

Set DataRecordSet = Connect1.execute (strQuery)

  '  DataRecordSet.movefirst

'  set objField = DataRecordSet.Fields

Do While Not DataRecordSet.EOF
intRecordCount = intRecordCount + 1
CurrentValue = DataRecordSet("Column2").value
msgbox  CurrentValue
DataRecordSet.MoveNext
Loop

Set DataRecordSet = nothing
Set Connect1= nothing
===============================================================

PS: excel saved on D:\QTP\MySpreadsheet.xls

Please all masters help me to solve this problem.

作者: billxin 时间: 2010-5-3 09:39
Can not type chinese in office.

help help ....waiting on line

作者: TIB 时间: 2010-5-3 10:22
请把excel中的所有数据都存成字符串，因为前面的1、2、3被认为是数字类型，因此读到后面的字符串时解析错误了

作者: zhehaoshi 时间: 2010-5-3 18:45
处理excel还是用Set objExcel = CreateObject("Excel.Application") 好

作者: billxin 时间: 2010-5-3 18:58
我想的也是这个原因。

通常来说，QTP 用什么来对 excel操作来好呢？
请各位老师指教~~~

作者: billxin 时间: 2010-5-3 18:59

原帖由 TIB 于 2010-5-3 10:22 发表
请把excel中的所有数据都存成字符串，因为前面的1、2、3被认为是数字类型，因此读到后面的字符串时解析错误了

怎么把 excel 存成字符串呢？

谢谢

作者: 泥泥虫 时间: 2010-5-4 13:53
cstr( )

作者: billxin 时间: 2010-5-5 07:25

原帖由 泥泥虫 于 2010-5-4 13:53 发表
cstr( )

my problem is when retrieve the data "1" in excel, assign to variable , qtp consider it is null value.

don't know the reason.

still get confused. ..

作者: TIB 时间: 2010-5-5 08:49
标题: 回复 6# 的帖子
数字前加单引号就可以在excel中以文本格式存储数字

作者: billxin 时间: 2010-5-7 12:53

原帖由 TIB 于 2010-5-5 08:49 发表
数字前加单引号就可以在excel中以文本格式存储数字

You are right... quick fix my problem....
thanks

Keep in touch.

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)