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=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

How to Connect Database and Import Data From Database in Excel through VBScript or QTP How to Connect Database and Import Data From Database in Excel through VBScript or QTP Reviewed by Animesh Chatterjee on January 15, 2009 Rating: 5

No comments:

Powered by Blogger.