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

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