Dim Baglanti As New ADODB.Connection
Dim KayitSeti As New ADODB.Recordset
Dim Firma As String, SERVER As String, Database As String, Kullanıcı As String, Parola As String, DONEM As String, Tutar As String, TUTAR2 As String, Tutar1 As String, tarih1 As String, tarih2 As String
Sub bilanco()
Firma = Format(Range("g1"), "000"): SERVER = Range("L1").Value: Database = Range("L2").Value: Kullanıcı = Range("L3").Value: Parola = Range("L4").Value
Tutar = Range("I1").Value: Tutar1 = Range("I2").Value: tarih1 = Format(Range("G3").Value, "d.M.yyyy"): tarih2 = Format(Range("G4").Value, "d.M.yyyy")
TUTAR2 = Range("I3").Value: dijit10 = Range("I4").Value: DONEM = Format(Range("g2"), "00"):
Range("A7:Z65536").ClearContents
Baglanti.Open "Provider=SQLOLEDB; Data Source=" & SERVER & "; Initial Catalog=" & Database & "; User ID=" & Kullanıcı & "; Password=" & Parola & ";"
S = S & vbLf & " SELECT "
S = S & vbLf & " ORT_FISSATIR.Hesapkebirkodu , ORT_FISSATIR.hesapkodu, "
S = S & vbLf & " dbo.fnc_ort_plankodadi(ORT_FISSATIR.hesapkodu) as hesapadi, "
S = S & vbLf & " SUM(IsNull(ORT_FISSATIR.tlborctutar,0)) AS borctoplam, "
S = S & vbLf & " SUM(IsNull(ORT_FISSATIR.tlalactutar,0)) AS alactoplam, "
S = S & vbLf & " CASE WHEN SUM(IsNull(ORT_FISSATIR.tlborctutar,0)) > SUM(IsNull(ORT_FISSATIR.tlalactutar,0)) THEN SUM(IsNull(ORT_FISSATIR.tlborctutar,0)) - SUM(IsNull(ORT_FISSATIR.tlalactutar,0)) ELSE 00 END borcbakiye, "
S = S & vbLf & " CASE WHEN SUM(IsNull(ORT_FISSATIR.tlalactutar,0)) > SUM(IsNull(ORT_FISSATIR.tlborctutar,0)) THEN SUM(IsNull(ORT_FISSATIR.tlalactutar,0)) - SUM(IsNull(ORT_FISSATIR.tlborctutar,0)) ELSE 00 END alacbakiye "
S = S & vbLf & " FROM ORT_FISBASLIK WITH (NOLOCK) "
S = S & vbLf & " LEFT JOIN ORT_FISSATIR WITH (NOLOCK) "
S = S & vbLf & " ON ORT_FISBASLIK.ID = ORT_FISSATIR.IDFisbaslik "
S = S & vbLf & " WHERE ORT_FISBASLIK.fistipi >= 1 "
S = S & vbLf & " AND (ORT_FISBASLIK.fistarihi > CONVERT(DATETIME, '" & tarih1 & "', 103)) --Mizan Başlangıç Tarihi Parametre Olacak Girilecek "
S = S & vbLf & " AND (ORT_FISBASLIK.fistarihi < CONVERT(DATETIME, '" & tarih2 & "', 103)) --Mizan Bitiş Tarihleri Parametre Olarak Girilecek "
S = S & vbLf & " GROUP BY ORT_FISSATIR.Hesapkebirkodu, ORT_FISSATIR.hesapkodu ORDER BY ORT_FISSATIR.hesapkodu "
KayitSeti.Open S, Baglanti
Cells(7, 1).CopyFromRecordset KayitSeti
'y = [A65536].End(xlUp).Row + 1
'Range("F" & y) = "TOPLAM"
'Range("G" & y) = WorksheetFunction.Sum(Range("G7:G" & y))
'Range("H" & y) = WorksheetFunction.Sum(Range("H7:H" & y))
KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
End Sub