Extract Data Using Select Query in VBA

Option Explicit

Dim conn As New ADODB.Connection, querystring As String, blockname As String, hadoopblockrowcount As Long
Dim rst As New ADODB.Recordset

Sub extractData()
blockname = Application.InputBox("Please Mention Block Name to be Created")
querystring = "Select t1.Cid,t1.CName,t1.PSName,t1.StDuration,t2.CourseExamId from MST_COURSE t1"
querystring = querystring & " join LNK_COURSE_EXAM t2 on t1.CId=t2.courseid"
querystring = querystring & " where t1.CId in(Select distinct CourseId from Lnk_COURSE_VENDOR where VendorId=32)and PSName is not null"
querystring = querystring & " Union Select t1.Cid, t1.CName,t1.PSName,t1.StDuration,null from MST_COURSE t1"
querystring = querystring & " where t1.CId in(Select distinct CourseId from Lnk_COURSE_VENDOR where VendorId=32)and PSName is not null;"

hadoopblockrowcount = 3
Application.ScreenUpdating = False
    conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Initial Catalog=CourseMasterDB;"
    conn.Open
    rst.Open querystring, conn, adOpenStatic
    Do While Not rst.EOF
    hadoopblockrowcount = hadoopblockrowcount + 1
    ThisWorkbook.Sheets(1).Range("A" & (hadoopblockrowcount)) = rst.Fields(0).Value
    ThisWorkbook.Sheets(1).Range("B" & (hadoopblockrowcount)) = rst.Fields(1).Value
    ThisWorkbook.Sheets(1).Range("C" & (hadoopblockrowcount)) = rst.Fields(2).Value
                If (rst.Fields(4).Value <> vbNullString) Then
                        ThisWorkbook.Sheets(1).Range("E" & (hadoopblockrowcount)) = "Y"
                Else
                        ThisWorkbook.Sheets(1).Range("E" & (hadoopblockrowcount)) = "N"
                End If
    ThisWorkbook.Sheets(1).Range("G" & (hadoopblockrowcount)) = rst.Fields(3).Value
        rst.MoveNext
    Loop
   
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
   
End Sub

Code for Retrieving Data from SQL Server 2005 to Excel Spreadheet

Altough you can retrieve data from SqlServer 2005 to excel spreadsheet under   'Get External Data ' option; sometimes you need to run your own SQL query to pull back required fields from different tables to work with.

Here is  a simple VBA code to run your own SQL query  using SQL  Server 2005's in-bulit driver SQLOLEDB.1


Option Explicit
Sub retrievedata()
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Application.ScreenUpdating = False
On Error Resume Next
    con.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=NEW\SQLEXPRESS;"
    con.Open
    rst.ActiveConnection = con
    rst.Source = "Select * from Member where Gender='F' "
    rst.Open
    Range("A2").CopyFromRecordset rst
    rst.Close
    con.Close
    Set rst = Nothing
    Set con = Nothing
    Application.ScreenUpdating = True
End Sub