MSSQL’den Excele veri çekme

Aşağıda örnek kodlar var. İncelenirse anlaşılacak mahiyette.


Sub DEVIRLER()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnectionString As String
Dim sorgu As String
Dim str As String
Dim ws As Worksheet
Dim son As Integer

Set ws = Sheets("Sayfa1")
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
son = ws.Cells(Rows.Count, 1).End(xlUp).Row

strConnectionString = "Provider=SQLOLEDB;Data Source=.....................;Initial Catalog=............;User ID=.............;Password=.....................;"

On Error Resume Next
cnn.ConnectionTimeout = 1
cnn.CommandTimeout = 0
cnn.Open strConnectionString

For i = 9 To son - 1
For j = 1 To 12
rs.Open "select AVG(" & ws.Range("a" & i + 1) & ") from PLC19.dbo.MakDevirler where MONTH(Date)=" & j, cnn
ws.Cells(i + 1, j + 1) = CDbl(rs.GetString)
rs.Close
Next
Next

End Sub

&nbsp;

&nbsp;

Not: ADODB.Connection ve ADODB.Recordset nesnelerini tanımlamak için Tools/References ‘ dan “Microsoft ActiveX Data Objects 2.0 Library” referansını projenize eklemeniz gerekir. Yoksa kodlar çalışmaz.

Paylaşmayı unutmayın!