Option Explicit
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Public Sub PullData()

Dim iColumn As Integer
Dim wsData As Worksheet

Call CreateConnection("SELECT * From Table1")

Set wsData = ThisWorkbook.Worksheets("PullData")
With wsData
     .Cells.ClearContents
     
     'Pull data
     .Range("A3").CopyFromRecordset rst
     
     'Pull Fields Name
     For iColumn = 1 To rst.Fields.Count
         .Cells(2, iColumn).Value = rst.Fields(iColumn - 1).Name
     Next iColumn
End With

End Sub
Private Sub CreateConnection(ByVal sSql As String)

Dim strConn As String
Dim strPassword As String, strUserId As String

'strConn = "Provider=SQLOLEDB.1;"

strUserId = "login"
strPassword = "password"
strConn = "Provider=MSDASQL.1;"
strConn = strConn & "driver= (SQL Server);"
strConn = strConn & "Server=PC-PC\SQLEXPRESS;"
strConn = strConn & "Database=DBName;"
strConn = strConn & "uid=" & strUserId & ";"
strConn = strConn & "pwd:=" & strPassword

'Create connection to SQL Server
Set cnn = New ADODB.Connection
With cnn
     .Open strConn                      'Debugger указывает на ошибку в этой строке 
     .CursorLocation = adUseClient
End With

'Establish record so Excel can pull data
Set rst = New ADODB.Recordset
With rst
     .CursorLocation = adUseServer
     .Open Source:=sSql, ActiveConnection:=cnn, CursorType:=adOpenForwardOnly, _
     LockType:=adLockOptimistic, Options:=adCmdText
End With


End Sub