HI All,
In this video i have told about connectivity of Excel with SQL using VBA to understand the concept of connectivity of SQL & Excel. In this video along with connectivity we have also pulled the data from SQL Server into the excel worksheet.
Below is the code :
Sub Connectivity_with_Sql()
Dim cnn As ADODB.Connection, Rst As ADODB.Recordset
Dim Str As String, Qry As String
Set cnn = New ADODB.Connection
Set Rst = New Recordset
Qry = "Select * from sheet3"
Str = "Provider=SQLOLEDB.1;integrated security=SSPI;Data source=DESKTOP-V76UGV0;initial catalog=Python_Data"
cnn.Open Str
Rst.Open Qry, cnn
Dim wkb As Workbook, k As Integer
Set wkb = Workbooks.Add
For k = 0 To Rst.Fields.Count - 1
wkb.Sheets(1).Range("A1").Offset(0, k).Value = Rst.Fields(k).Name
Next k
wkb.Sheets(1).Range("A2").CopyFromRecordset Rst
cnn.Close
MsgBox "Done!"
End Sub
If you guys have any Query you may ask me on [email protected]
