In this post I tried a simple mechanism by a highly customizable code for connecting Database from Excel or QTP by using a VBA or QTP code.
Import Data From Database
The code will look like-
Dim cnnt
Set cnnt = CreateObject(“ADODB.Connection”)
cnnt.Provider = “SQLOLEDB”
//provider can be any…..like sql,mysql
cnnt.ConnectionString= ” Driver={SQL Server}; Server=127.0.0.1; Database=Northwind; UID=sa; PWD=”
Set cnnt = CreateObject(“ADODB.Connection”)
cnnt.Provider = “SQLOLEDB”
//provider can be any…..like sql,mysql
cnnt.ConnectionString= ” Driver={SQL Server}; Server=127.0.0.1; Database=Northwind; UID=sa; PWD=”
cnnt.Open
Set recordSet = CreateObject(“ADODB.Recordset”)
Set recordSet1=cnnt.Execute(“Select * from Employees”)
Set objStream=CreateObject(“ADODB.Stream”)
recordSet1.Save objStream,1
Set objExcel=CreateObject(“Excel.Application”)
Set objSheet=CreateObject(“Excel.Sheet”)objSheet.Application.visible=true
objSheet.activesheet.cells (1,1).CopyFromRecordset recordSet1
objSheet.Saveas “D:database.xls”
objExcel.quit
Set objSheet =Nothing
recordSet1.Close
Set recordSet = CreateObject(“ADODB.Recordset”)
Set recordSet1=cnnt.Execute(“Select * from Employees”)
Set objStream=CreateObject(“ADODB.Stream”)
recordSet1.Save objStream,1
Set objExcel=CreateObject(“Excel.Application”)
Set objSheet=CreateObject(“Excel.Sheet”)objSheet.Application.visible=true
objSheet.activesheet.cells (1,1).CopyFromRecordset recordSet1
objSheet.Saveas “D:database.xls”
objExcel.quit
Set objSheet =Nothing
recordSet1.Close
Import Data From Database
