I need to get data from database and store in Excel

[shyam_meghansh]

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

[adisgee]

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.

[praveenchandupatla]

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.

[smartarun]

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

[jason]

(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( ; "SELECT * FROM your_table")


Hope it helps someone