Help -- error happened on QTP reading data from Excel file

billxin

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

Can not type chinese in office.

help help ....waiting on line

TIB

请把excel中的所有数据都存成字符串，因为前面的1、2、3被认为是数字类型，因此读到后面的字符串时解析错误了

zhehaoshi

处理excel还是用Set objExcel = CreateObject("Excel.Application") 好

billxin

我想的也是这个原因。

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

billxin

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

怎么 把 excel 存成 字符串呢？

谢谢

泥泥虫

cstr( )

billxin

原帖由 泥泥虫 于 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

数字前加单引号就可以在excel中以文本格式存储数字

billxin

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

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

Keep in touch.