Getting data from SQL database using VBA.
Connection info:
Dim connString as String, sqlString as String
Dim conn As Object, rs As Object
connString = "Provider=SQLOLEDB;Server=SERVER_NAME;Database=DATABASE_NAME; Integrated Security=SSPI;"
Set conn = New ADODB.Connection
conn.Open connString
Output all results
Set rs = New ADODB.Recordset
sqlString = "SELECT statement"
Set rs = conn.Execute(sqlString)
If Not rs.EOF Then
sheet.Range("A1").CopyFromRecordset rs
End If
For use when there is going to be specific outcome
Set rs = New ADODB.Recordset
sqlString = "SELECT statement"
Set rs = conn.Execute(sqlString)
If Not rs.EOF Then
getResult = rs!SQLFieldName
End If
Tidy stuff up.
Set rs = Nothing
Set conn = Nothing
Source
http://www.geeksengine.com/article/vba-function-multiple-values3.html
https://www.mrexcel.com/forum/excel-questions/617788-vba-connect-sql-server.html
