PDA

View Full Version : VBA code to export SQL extract result and formatting



pkdash83
11-21-2011, 08:59 AM
Hi Guys,

I need to take an extract from SQL Server table and populate the data in a no of excel sheets. I generally copy paste it and do some manually formatting. The formatting is same for all the sheets. Can somebody let me know the steps involved if I want to automate the process with the help of macro?

A detail description would be appreciated.

Thanks in advance
Prasan

mohanvijay
11-21-2011, 08:22 PM
here is the simple code for get data from SQl



Dim R_Con As Object
Dim R_Rec As Object
Dim Qqury As String

Const DB_Con As String = "Provider=SQLNCLI;Server=YourServerNAme;Database=YourDatabaseName;Uid=UserNa me;Pwd=Password;"

Set R_Con = CreateObject("ADODB.Connection")
Set R_Rec = CreateObject("ADODB.Recordset")

Qqury = "" ' You SQL Query here

R_Con.Open DB_Con
R_Rec.Open Qqury, R_Con

Range("a1").CopyFromRecordset R_Rec 'SQL Data into Excel

R_Rec.Close
Set R_Rec = Nothing
R_Con.Close
Set R_Con = Nothing