我将张贴两个例子，所以请参考。
第一个例子

Sub ExampleSQL()

   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strSQL As String

   Set cnn = New ADODB.Connection

   'Set the provider property to the OLE DB Provider for ODBC.
   'cnn.Provider = "MSDASQL"
   'cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
   'cnn.Provider = "MSOLAP"
   'cnn.Provider = "SQLOLEDB.1"
   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=severname;uid=sa;pwd=password;database=test"

    Set rs = New ADODB.Recordset

    strSQL = "SELECT * FROM [your Table] "

    rs.Open strSQL, cnn.ConnectionString, adOpenForwardOnly, adLockReadOnly, adCmdText

    cnn.Open
    If cnn.State = adStateOpen Then
    Else
       MsgBox "Sever is not connected!! "
       Exit Sub
    End If

    If Not rs.EOF Then
         With Ws
            .Range("a4").CurrentRegion.ClearContents
            For i = 0 To rs.Fields.Count - 1
               .Cells(4, i + 1).Value = rs.Fields(i).Name
            Next
            .Range("a5").CopyFromRecordset rs
            .Columns.AutoFit
        End With
    Else
        MsgBox "No Data!!", vbCritical
    End If
    rs.Close
    Set rs = Nothing

    cnn.Close
    Set cnn = Nothing
End Sub

第二个例子

Sub getDataFromServer()
    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    Dim i As Integer

    con.ConnectionString = "Provider=SQLOLEDB.1;" _
             & "Server=(local);" _
             & "Database=TEST;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;"

    con.Open

    Set cmd.ActiveConnection = con
    cmd.CommandText = "SELECT * FROM [your Table]"
    Set rs = cmd.Execute
    Range("A1").CopyFromRecordset rs

    con.Close
    Set con = Nothing

End Sub