surenhere07

 Profile Answers by surenhere07 

• Jun 18th, 2008

 

Set objDB = CreateObject("ADODB.Connection")
objDB.ConnectionString = "DSN=mysql_qtp"
objDB.Open

If objDB.state=1 Then
msgbox("Connection Is Establsihed")
else
msgbox("Connection is not opened")
exittest
End If

sql_query = "select zipcode, cityName from ZipMapRegion limit 3"
Set objResults = objDB.Execute(sql_query )

Set s=datatable.GetSheet ("Global")
Set zipcode=s.addparameter("cityName"," ")
Set cityName=s.addparameter("cityName"," ")

Do Until objResults.EOF
DataTable.SetNextRow
zipcode.value = objResults("zipcode")
cityName.value = objResults("cityName")
msgbox zipcode & " " & " "& cityName
objResults.MoveNext
Loop

DataTable.ExportSheet "C:Tempzips.xls" , 1
msgbox "The file is saved in C:Tempzips.xls"

objResults.Close
objDB.Close

In above code you are always setting to zipcode, cityName, by default it must be adding to the fist row only. For this you have to iterate thru the rows as well. At the end you are exporting to XL.

As i do not have QTP as of now. I am dorectly writing values to the Xl itself. Here is the code;

Set objDB = CreateObject("ADODB.Connection")
objDB.ConnectionString = "DSN=mysql_qtp"
objDB.Open

If objDB.state=1 Then
msgbox("Connection Is Establsihed")
else
msgbox("Connection is not opened")
exittest
End If

sql_query = "select zipcode, cityName from ZipMapRegion limit 3"
Set objResults = CreateObject("ADODB.RecordSet")
Set objResults = objDB.Execute(sql_query )

Set xlapp = CreateObject("Excel.Application")
Set wb = xlapp.Workbooks.open("C:Tempzips.xls")
set ws = xlapp.worksheets("emps")
i=1
Do Until objResults.EOF
 ws.cells(i,1).value = objResults("zipcode")
 ws.cells(i,2).value = objResults("cityName")
 objResults.MoveNext
i = i+1
Loop
wb.save

wb.close

Set xlapp=Nothing
objResults.Close
objDB.Close