Sub FarkBul()
'Excel Excel Bağla Referanslı _________________________________________________
Dim SQL As String
Dim ADO_RS As ADODB.Recordset
Dim ADO_CN As ADODB.Connection
SQL = "SELECT G.FNO, T.ToplaTutar AS Tescil, G.ToplaTUTAR AS Genel, [Genel]-[Tescil] AS Fark " & _
"FROM " & _
"(SELECT [TESCILLER$].FatMus_IlkNo, Sum([TESCILLER$].Tutar) AS ToplaTutar " & _
"FROM [TESCILLER$] " & _
"GROUP BY [TESCILLER$].FatMus_IlkNo) as T " & _
"INNER Join " & _
"(SELECT [GELEN_BILGI$].FNO, Sum([GELEN_BILGI$].TUTAR) AS ToplaTUTAR " & _
"FROM [GELEN_BILGI$] " & _
"GROUP BY [GELEN_BILGI$].FNO) as G " & _
"ON T.FatMus_IlkNo = G.FNO " & _
"GROUP BY G.FNO, T.ToplaTutar, G.ToplaTUTAR;"
Set ADO_RS = New ADODB.Recordset
Set ADO_CN = New ADODB.Connection
ADO_CN.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;data source=" & ThisWorkbook.FullName & _
";extended properties=""excel 8.0;hdr=Yes"""
ADO_CN.Open
ADO_RS.Open SQL, ADO_CN, 3, 1 ' güncelleme yapabilmek için 1,3 0lmalı yada adOpenKeyset, adLockOptimistic
'
' Eğer Hiç Kayıt Yoksa
If ADO_RS.RecordCount = 0 Then
MsgBox "Kayıt Bulunamadı.", vbCritical, "Veri Yok"
GoTo son
End If
ADO_RS.MoveLast
ADO_RS.MoveFirst
Sheets("Fark").Cells.Clear
For x = 1 To ADO_RS.Fields.Count
Sheets("Fark").Cells(1, x) = ADO_RS(x - 1).Name
Next x
Sheets("Fark").Range("A2").CopyFromRecordset ADO_RS
son:
ADO_RS.Close
ADO_CN.Close
Set ADO_RS = Nothing
Set ADO_CN = Nothing
End Sub