Import data from SQL Server into Excel

excel_sql_server.vba

// http://stevesohcot.com/tech-lessons-learned/2016/05/17/get-data-from-sql-server-in-excel

Public Sub GetData()
     
    Dim strSQL As String
    Dim strCnn As String
    Dim cnn As Object
    Dim rst As Object
            
    strCnn = "Driver={SQL Server};" & _
          "Server=SERVER_NAME_HERE;" & _
          "Database=DATABASE_NAME_HERE;" & _
          "Uid=USER_NAME_HERE;" & _
          "Pwd=PASSWORD_HERE"
            
    Set cnn = CreateObject("adodb.connection")
            
    cnn.Open strCnn
 
    Set rst = CreateObject("ADODB.Recordset")
   
    'SQL Server: Change dbo_ to dbo.
   
    strSQL = "SELECT * FROM ... "
 
 
    rst.Open strSQL, cnn, 3, 1 '3 = Keyset, 1 = Pessemistic
   
    If rst.RecordCount > 0 Then
       
        rst.MoveLast
        rst.MoveFirst
       
        Do Until rst.EOF
           
            Range("B1").FormulaR1C1 = rst.Fields("FIELD_NAME_HERE")
           
            rst.MoveNext
        Loop
       
        rst.Close
     
    Else
   
        MsgBox "No data"
       
    End If
   
    Set rst = Nothing
  
 
End Sub