I need to get data from database and store in Excel

I need to get some data from data base and store this (retrieved) data in a excel sheet using VB script in QTP 9.0 I have created connection for data base I have created as excels sheet by using Set XL=CreateObject("Excel.Application") XLworksheet.cells(1,1).value= rs.fields.item("<>")

I have taken a for loop and changed the cells values (1 as i and another 1 as j) But still I am not able to get Plz kindly tell me know this .

I am not getting this

Hi, try to use from some of the code below. Will help you navigate through excel. This should help. ' create an invisible instance of an excel object set objexcel = createobject("excel.application") ' open the excel work book ' open (path, confirmconversations, readonly) objexcel.workbooks.open vpath, false, false ' get teh count for the number of worksheets in the excel workbook vworksheetcount = objexcel.worksheets.count ' currently let us work only with the first sheet set currworksheet = objexcel.activeworkbook.worksheets(1) ' set the object to access the excel sheet cells set cells = currworksheet.cells ' get the name of the worksheet vcurrworksheetname = currworksheet.name ' get the rows count and get the columns count vusedcolcnt = currworksheet.usedrange.columns.count vusedrowcnt = currworksheet.usedrange.rows.count ' topmost row used and leftmost column used vsheettop = currworksheet.usedrange.row vsheetleft = currworksheet.usedrange.column ' display the worksheet information, that is being accessed wscript.echo "excel workbook under process = " & vpath & vbcr & vblf & " no of worksheets = " &vworksheetcount & _ vbcr & vblf & "sheet name = " &vcurrworksheetname & _ vbcr & vblf & "used col count # = " &vusedcolcnt & _ vbcr & vblf & "used row count # = " &vusedrowcnt & _ vbcr & vblf & "sheets top most row # = " &vsheettop & _ vbcr & vblf & "sheets left most column # = " &vsheetleft ' loop through each row in the worksheet for vrowctr = 0 to vusedrowcnt - 1 ' loop throuhg each column for vcolctr = 0 to vusedcolcnt - 1 ' get the row that is in the used range currrow = vrowctr + vsheettop ' get the column that is in the used range currcol = vcolctr + vsheetleft ' get the cell value by accessing the row and column vcellcontent = cells(currrow,currcol).value ' display the content wscript.echo "row# " &currrow & " column# " &currcol & _ vbcr + vblf + "cell content is " & vcellcontent next next ' release the cells object memory set cells = nothing ' set the currworksheets object to nothing - release resources set currworksheet = nothing ' close and quit the excel workbook objexcel.workbooks.close objexcel.quit ' set the excel object to nothing set objexcel = nothing ' ---- end of code ---------- ' to write the values simply use: ' cells(currrow,currcol).value = vvaluetowrite ---------------- hope this helps, u can always try out this using wsh and then implement in the qtp action. Njoy, aditya.

I have a drop down box to show selected number of records in result page.It contains 4 items like 10, 25,50 and 100. when i was recordes script for seelcting each item and checking selecting number of records in the result page( it is in the same page in the table format)it is generating a new page with same controls( objects) in the OR. how to avaid these in OR? is it possible to write DP for only these lines?, remaining(remaingin lines of code) objects i want use from OR only.

One more problemis if change ( add /remove properties of an OBJECT in OR , my ( recorded )application is not running.

Use database connection to get connected to database - table. Then making use of database functions get data and store into the variable. Then start assigning the variable value to the respective columns into excel sheet. Strval = datatable.value("table name",db)
*check for correct syntax. If excelfile <> "" then bfoundpath = gettestdatapath(excelfile, strfullpath) if bfoundpath then datatable.import(strfullpath) else exitrun(0) end if
end if ncount = datatable.getsheet("global").getrowcount
datatable.setcurrentrow(1)
i = 1
while i <= ncount datatable.value("column name", "global") = strval
wend similar to above example you can write as many as values you need to store into excel. Arun

(I know this is an old post but it might help)

there are very simple ways to do that, most of them by using addins for excel i prefer SQLodbc addin

sqlodbc.t35.com


Example

=sql.execute(<connection string> ; "SELECT * FROM your_table")


Hope it helps someone