Header Ads

How to Connect Database and Import Data From Database in Excel through VBScript or QTP

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.

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=; Database=Northwind; UID=sa; PWD="
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"
Set objSheet =Nothing

Powered by Blogger.