WORKING WITH QTP

TestEveryThinG

How to export data from database table to Excel sheet and Datatable

Posted by rajivkumarnandvani on March 25, 2011

Hi All
‘ REM  Adding NewDataSheet in Run Time
DataTable.AddSheet “MySheet”

‘ REM  Adding Column name in Run time
DataTable.GetSheet(“MySheet”).AddParameter “bname”,” ”

REM Create DataBase Connection
Set objCon = CreateObject(“ADODB.Connection”)
objCon.Open”Provider=SqlOledb.1;Server=sys;uid=sa;pwd=;database=db1″
REM (OR)  Open DataBase Connection

objCon.Open”Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\NewDB.mdb”

REM  Creating Record set for DataSet
Set objRs=CreateObject(“adodb.recordset”)

REM Opening RecodSet form DataBase( storing the values in recordsetb )
objRs.open”select * from Emp”,objCon

REM Loop for Getting DataBase Column name
For intCount = 1 to objRs.Fields.Count-1
i = 1

REM column name in run time data sheet using add parameter method
DataTable.GetSheet(“MySheet”).AddParameter objRs.Fields(intCount).Name,” ”

REM Loop for Checking end of the Recod
While objRs.eof<>true
REM  Seting the current row
DataTable.SetCurrentRow(i)
REM   increments for data table row
i=i+1
REM  Assigning the DataBase Values into DataTable
DataTable.Value(objRs.Fields(intCount).Name,”MySheet”) = objRs.Fields(intCount).Value
REM’ ——->  moving the record  pointed to next record
objRs.MoveNext
Wend
REM  moving the record  pointed to First record
objRs.MoveFirst
Next 

REM Exporting the results in local
DataTable.ExportSheet”D:\MyTest.xls”,3
Advertisements

One Response to “How to export data from database table to Excel sheet and Datatable”

  1. TJ said

    Thanks… it was very helfpul

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: