Macro to import data from SQL using ADO connection string:
Sub Import_data_from_SQL()
' Tools -> References -> Microsoft Active Data object 2.0
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim sConnString As String
Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection
' create the connection
'Server=;UserID=myUsername;password=myPassword;
sConnString = "Provider=SQLOLEDB;Data Source=servername;" & _
"Initial Catalog=NORTHWIND;" & _
"Integrated Security=SSPI;"
'Open connection
cnn.Open sConnString
strQry = "SELECT * FROM ORDERS"
With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strQry, cnn
End With
'paste data
Sheets(1).Range("A1").CopyFromRecordset rs
'close
rs.Close
cnn.Close
End Sub
Sub Import_data_from_SQL()
' Tools -> References -> Microsoft Active Data object 2.0
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim sConnString As String
Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection
' create the connection
'Server=;UserID=myUsername;password=myPassword;
sConnString = "Provider=SQLOLEDB;Data Source=servername;" & _
"Initial Catalog=NORTHWIND;" & _
"Integrated Security=SSPI;"
'Open connection
cnn.Open sConnString
strQry = "SELECT * FROM ORDERS"
With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strQry, cnn
End With
'paste data
Sheets(1).Range("A1").CopyFromRecordset rs
'close
rs.Close
cnn.Close
End Sub