- Katılım
- 23 Mart 2006
- Mesajlar
- 303
- Excel Vers. ve Dili
-
Microsoft Office 2003
Excel 2003
Selam,
Excel tabloma SQL serverden verilerimi almaya çalışıyorum. Query de yazdığım sorgu çalışıyor ve verileri Query de görebiliyorum fakat Bunları Dıç veri al ile excel e almaya çalıştığımda AS19 Kaynağından sorguluyor diyor fakat veriler excele gelmiyor. Yazdığım Macro aşağıdaki gibidir.
Saygılar.
Private Sub CommandButton1_Click()
Dim SQL1, SQL2, SQL3, SQL4, SQL5, SQL6, SQL7, SQL8, SQL9, SQL10, SQL11, SQL12, SQL13, SQL14, SQL15
Dim SQL16, SQL17, SQL18, SQL19, SQL20, SQL21, SQL22, SQL23, SQL24, SQL25, SQL26, SQL27, SQL28, SQL29, SQL30
Dim SQL31, SQL32, SQL33, SQL34, SQL35, SQL36, SQL37, SQL38, SQL39, SQL40, SQL41
With ActiveWorkbook.Connections("192.168.1.100 CATR8450").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *from BTI_V_ECARS33")
.CommandType = xlCmdSql
.Connection = Array( _
"OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=kullanıcıadı;pwd=sifre;Initial Catalog=catalog;Data Source=ipadress;Extended Properti" _
, _
"es="""";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SERVMUDNTB-PC;Use Encryption for Data=Fals" _
, "e;Tag with column collation when possible=False")
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("xxxxxxxxxxxxxxxx")
.Name = "xxxxxxxxxxxxx"
.Description = ""
End With
ActiveWorkbook.Connections("xxxxxxxxxxxxx").Refresh
'--------------------------
SQL1 = "declare @from as smalldatetime,@to as smalldatetime"
SQL2 = "declare @odemeTIP as char(2)declare @musteri as bit declare @dahili as bit declare @garanti as bit declare @sigorta as bit"
SQL3 = "set @from = '01/10/2014' set @to = '10/10/2014' set @odemeTIP = '99' set @musteri = 1 set @dahili = 1 set @garanti = 1 set @sigorta = 1"
SQL4 = "declare @ondalik as tinyint set @ondalik = (select preci from wtdev where alt=1)"
SQL5 = "select IE = RO.numor, Servis = Ser.Libelle, [Fatura No] = doc.numdoc, Tip = T.Descr, [Odeme Tip] = CR.Description,[İşemri Tarihi] = doc.datDocCre,"
SQL6 = "[Fatura Tarihi] = doc.datF,[M.No] = doc.idCli, Müşteri = case doc.typCIGA when 3 then isnull(RO.nom,'') + ' ' + isnull(RO.prenom,'')"
SQL7 = "else isnull(doc.nom,'') + ' ' + isnull(doc.prenom,'') end, Sigorta = case doc.typCIGA when 3 then isnull(doc.nom,'') + ' ' + isnull(doc.prenom,'') Else ''end,"
SQL8 = "Şasi = doc.idVeh, Plaka = RO.immat, [Trafik Cikis Tarihi] = RO.datMec, ModelYıl=RO.anMod, KM=RO.KmHor, MotorNo=RO.noMoteur,"
SQL9 = "[Parca Toplam] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik),"
SQL10 = "[Parca Indirim] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik) -"
SQL11 = "round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik), --isnull(doc.totPRNet,0) , @ondalik),"
SQL12 = "[Parca Indirim Yuzde] = case when isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'),0)=0 then 0.00"
SQL13 = "Else round(((isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0) -"
SQL14 = "round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik))*100)/"
SQL15 = "isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'),0),@ondalik) end,"
SQL16 = "[Parca Net Toplam] = round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik), --round(isnull(doc.totPRNet,0), @ondalik),"
SQL17 = "[Parca Maliyet] = parMal,[Parca Kar] = round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0) - parMal,@ondalik),"
SQL18 = "[Parca Kar Yuzde] = case when parMal=0 then 0.00"
SQL19 = "else (isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0) - parMal)/parMal*100 end,"
SQL20 = "[Iscilik Toplam] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'), 0), @ondalik),"
SQL21 = "[Iscilik Harc.Sure] = LO.harcSure, [Iscilik Indirim] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'), 0) - isnull(doc.totMONet,0) , @ondalik),"
SQL22 = "[Iscilik Indirim Yuzde] = case"
SQL23 = "when isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'),0)=0 then 0.00"
SQL24 = "Else round(((isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'), 0) - isnull(doc.totMONet,0))*100)/"
SQL25 = "isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'),0), @ondalik) end,"
SQL26 = "[Iscilik Net Toplam] = round(doc.totMONet, @ondalik),"
SQL27 = "[Dahili İşçilik] = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='MC'), 0),"
SQL28 = "[Harici Iscilik] = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='MD' and MOP.idMOPreCat not in ('70')), 0),"
SQL29 = "Sarf = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and (L.idMONat='MD' or L.idMONat='ME') and MOP.idMOPreCat in ('70')), 0),"
SQL30 = "Taseron = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='ME' and MOP.idMOPreCat<>'70'), 0),"
SQL31 = "[TaseronAlışFiyatı] = isnull((select sum(L.PA) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='ME' and MOP.idMOPreCat<>'70'), 0),"
SQL32 = "Toplam = round(doc.totHT, @ondalik),[Genel Toplam] = round(doc.totTTC, @ondalik),"
SQL33 = "AY = case month(doc.datF) when 1 then 'Ocak' when 2 then 'Şubat' when 3 then 'Mart' when 4 then 'Nisan' when 5 then 'Mayıs' when 6 then 'Haziran'"
SQL34 = "when 7 then 'Temmuz' when 8 then 'Ağustos' when 9 then 'Eylül' when 10 then 'Ekim' when 11 then 'Kasım' when 12 then 'Aralık' end, RO.idRcp, isnull(Rcp.nomRcp, '') as Resepsiyonist -- [Etkilenilen Kampanya]=CA.Description"
SQL35 = "from wtDocAm doc (nolock) inner join wtOR RO (nolock) on RO.idOR = doc.idOR -- left Outer join wtORCamp CM (nolock) on CM.idOR = RO.idOR -- Left Outer join wtCampAS CA (nolock) on CA.idcampAS = CM.idCampAS"
SQL36 = "left outer join wtSer Ser (nolock) on Ser.idSer = RO.idSer left outer join wtDocTyp T (nolock) on T.idDocTyp = doc.typCIGA left outer join wtCliReg CR (nolock) on CR.idReg = doc.idReg"
SQL37 = "left outer join (select idDocAm, -1*sum(qte*pump) as parMal from wtPieMvt (nolock) group by idDocAm) Mvt on Mvt.idDocAm = doc.idDocAm"
SQL38 = "left outer join (select idor, iddocam, sum(tpsPasse)*100 as harcSure from wtDocLOuv (nolock) group by idOR, idDocAm) LO on doc.idOR = LO.idOR and doc.idDocAm = LO.idDocAm"
SQL39 = "left outer join wtRcp Rcp (nolock) on Rcp.idRcp = RO.idRcp"
SQL40 = "where doc.etaf = 1 and doc.etaa = 0 and doc.natdoc = 0 and (@odemeTIP='99' or doc.idReg = @odemeTIP) and ((doc.typCIGA = -1 or (@musteri=1 and doc.typCIGA = 0)) or (doc.typCIGA = -1 or (@dahili =1 and doc.typCIGA = 1)) or (doc.typCIGA = -1 or (@garanti=1 and doc.typCIGA = 2)) or"
SQL41 = "(doc.typCIGA = -1 or (@sigorta=1 and doc.typCIGA = 3)) ) and doc.datf >= @from and doc.datf < @to"
Dim SQLFRST, SQLSEC, SQLTHRD, SQL
SQLFRST = SQL1 & SQL2 & SQL3 & SQL4 & SQL5 & SQL6 & SQL7 & SQL8 & SQL9 & SQL10 & SQL11 & SQL12 & SQL13 & SQL14 & SQL15
SQLSEC = SQL16 & SQL17 & SQL18 & SQL19 & SQL20 & SQL21 & SQL22 & SQL23 & SQL24 & SQL25 & SQL26 & SQL27 & SQL28 & SQL29 & SQL30
SQLTHRD = SQL31 & SQL32 & SQL33 & SQL34 & SQL35 & SQL36 & SQL37 & SQL38 & SQL39 & SQL40 & SQL41
SQL = SQLFRST & SQLSEC & SQLTHRD
'--------------------------
With Sayfa3.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=ipadresi;UID=kullanıcıadı;pwd=sifre;APP=2007 Microsoft Office system;WSID=SERVMUDNTB-PC" _
, Destination:=Sayfa3.Range("$A$1")).QueryTable
.CommandText = Array(" & SQL & ")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Tablo_AS19_kaynağından_sorgula"
.Refresh BackgroundQuery:=True
End With
End Sub
Excel tabloma SQL serverden verilerimi almaya çalışıyorum. Query de yazdığım sorgu çalışıyor ve verileri Query de görebiliyorum fakat Bunları Dıç veri al ile excel e almaya çalıştığımda AS19 Kaynağından sorguluyor diyor fakat veriler excele gelmiyor. Yazdığım Macro aşağıdaki gibidir.
Saygılar.
Private Sub CommandButton1_Click()
Dim SQL1, SQL2, SQL3, SQL4, SQL5, SQL6, SQL7, SQL8, SQL9, SQL10, SQL11, SQL12, SQL13, SQL14, SQL15
Dim SQL16, SQL17, SQL18, SQL19, SQL20, SQL21, SQL22, SQL23, SQL24, SQL25, SQL26, SQL27, SQL28, SQL29, SQL30
Dim SQL31, SQL32, SQL33, SQL34, SQL35, SQL36, SQL37, SQL38, SQL39, SQL40, SQL41
With ActiveWorkbook.Connections("192.168.1.100 CATR8450").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *from BTI_V_ECARS33")
.CommandType = xlCmdSql
.Connection = Array( _
"OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=kullanıcıadı;pwd=sifre;Initial Catalog=catalog;Data Source=ipadress;Extended Properti" _
, _
"es="""";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SERVMUDNTB-PC;Use Encryption for Data=Fals" _
, "e;Tag with column collation when possible=False")
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("xxxxxxxxxxxxxxxx")
.Name = "xxxxxxxxxxxxx"
.Description = ""
End With
ActiveWorkbook.Connections("xxxxxxxxxxxxx").Refresh
'--------------------------
SQL1 = "declare @from as smalldatetime,@to as smalldatetime"
SQL2 = "declare @odemeTIP as char(2)declare @musteri as bit declare @dahili as bit declare @garanti as bit declare @sigorta as bit"
SQL3 = "set @from = '01/10/2014' set @to = '10/10/2014' set @odemeTIP = '99' set @musteri = 1 set @dahili = 1 set @garanti = 1 set @sigorta = 1"
SQL4 = "declare @ondalik as tinyint set @ondalik = (select preci from wtdev where alt=1)"
SQL5 = "select IE = RO.numor, Servis = Ser.Libelle, [Fatura No] = doc.numdoc, Tip = T.Descr, [Odeme Tip] = CR.Description,[İşemri Tarihi] = doc.datDocCre,"
SQL6 = "[Fatura Tarihi] = doc.datF,[M.No] = doc.idCli, Müşteri = case doc.typCIGA when 3 then isnull(RO.nom,'') + ' ' + isnull(RO.prenom,'')"
SQL7 = "else isnull(doc.nom,'') + ' ' + isnull(doc.prenom,'') end, Sigorta = case doc.typCIGA when 3 then isnull(doc.nom,'') + ' ' + isnull(doc.prenom,'') Else ''end,"
SQL8 = "Şasi = doc.idVeh, Plaka = RO.immat, [Trafik Cikis Tarihi] = RO.datMec, ModelYıl=RO.anMod, KM=RO.KmHor, MotorNo=RO.noMoteur,"
SQL9 = "[Parca Toplam] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik),"
SQL10 = "[Parca Indirim] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik) -"
SQL11 = "round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik), --isnull(doc.totPRNet,0) , @ondalik),"
SQL12 = "[Parca Indirim Yuzde] = case when isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'),0)=0 then 0.00"
SQL13 = "Else round(((isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0) -"
SQL14 = "round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik))*100)/"
SQL15 = "isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'),0),@ondalik) end,"
SQL16 = "[Parca Net Toplam] = round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0), @ondalik), --round(isnull(doc.totPRNet,0), @ondalik),"
SQL17 = "[Parca Maliyet] = parMal,[Parca Kar] = round(isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0) - parMal,@ondalik),"
SQL18 = "[Parca Kar Yuzde] = case when parMal=0 then 0.00"
SQL19 = "else (isnull((select sum(netHT) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='P'), 0) - parMal)/parMal*100 end,"
SQL20 = "[Iscilik Toplam] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'), 0), @ondalik),"
SQL21 = "[Iscilik Harc.Sure] = LO.harcSure, [Iscilik Indirim] = round(isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'), 0) - isnull(doc.totMONet,0) , @ondalik),"
SQL22 = "[Iscilik Indirim Yuzde] = case"
SQL23 = "when isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'),0)=0 then 0.00"
SQL24 = "Else round(((isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'), 0) - isnull(doc.totMONet,0))*100)/"
SQL25 = "isnull((select sum(PV*qtetps) from wtDocL docL (nolock) where docL.idDocAm = doc.idDocAm and docL.idOr = RO.idOR and docL.typePMTK='M'),0), @ondalik) end,"
SQL26 = "[Iscilik Net Toplam] = round(doc.totMONet, @ondalik),"
SQL27 = "[Dahili İşçilik] = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='MC'), 0),"
SQL28 = "[Harici Iscilik] = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='MD' and MOP.idMOPreCat not in ('70')), 0),"
SQL29 = "Sarf = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and (L.idMONat='MD' or L.idMONat='ME') and MOP.idMOPreCat in ('70')), 0),"
SQL30 = "Taseron = isnull((select sum(L.netHT) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='ME' and MOP.idMOPreCat<>'70'), 0),"
SQL31 = "[TaseronAlışFiyatı] = isnull((select sum(L.PA) from wtDocL L (nolock), wtMOPre MOP (nolock) where L.idDocAm = doc.idDocAm and L.typePMTK='M' and L.idMOPre=MOP.idMOPre and L.idMONat='ME' and MOP.idMOPreCat<>'70'), 0),"
SQL32 = "Toplam = round(doc.totHT, @ondalik),[Genel Toplam] = round(doc.totTTC, @ondalik),"
SQL33 = "AY = case month(doc.datF) when 1 then 'Ocak' when 2 then 'Şubat' when 3 then 'Mart' when 4 then 'Nisan' when 5 then 'Mayıs' when 6 then 'Haziran'"
SQL34 = "when 7 then 'Temmuz' when 8 then 'Ağustos' when 9 then 'Eylül' when 10 then 'Ekim' when 11 then 'Kasım' when 12 then 'Aralık' end, RO.idRcp, isnull(Rcp.nomRcp, '') as Resepsiyonist -- [Etkilenilen Kampanya]=CA.Description"
SQL35 = "from wtDocAm doc (nolock) inner join wtOR RO (nolock) on RO.idOR = doc.idOR -- left Outer join wtORCamp CM (nolock) on CM.idOR = RO.idOR -- Left Outer join wtCampAS CA (nolock) on CA.idcampAS = CM.idCampAS"
SQL36 = "left outer join wtSer Ser (nolock) on Ser.idSer = RO.idSer left outer join wtDocTyp T (nolock) on T.idDocTyp = doc.typCIGA left outer join wtCliReg CR (nolock) on CR.idReg = doc.idReg"
SQL37 = "left outer join (select idDocAm, -1*sum(qte*pump) as parMal from wtPieMvt (nolock) group by idDocAm) Mvt on Mvt.idDocAm = doc.idDocAm"
SQL38 = "left outer join (select idor, iddocam, sum(tpsPasse)*100 as harcSure from wtDocLOuv (nolock) group by idOR, idDocAm) LO on doc.idOR = LO.idOR and doc.idDocAm = LO.idDocAm"
SQL39 = "left outer join wtRcp Rcp (nolock) on Rcp.idRcp = RO.idRcp"
SQL40 = "where doc.etaf = 1 and doc.etaa = 0 and doc.natdoc = 0 and (@odemeTIP='99' or doc.idReg = @odemeTIP) and ((doc.typCIGA = -1 or (@musteri=1 and doc.typCIGA = 0)) or (doc.typCIGA = -1 or (@dahili =1 and doc.typCIGA = 1)) or (doc.typCIGA = -1 or (@garanti=1 and doc.typCIGA = 2)) or"
SQL41 = "(doc.typCIGA = -1 or (@sigorta=1 and doc.typCIGA = 3)) ) and doc.datf >= @from and doc.datf < @to"
Dim SQLFRST, SQLSEC, SQLTHRD, SQL
SQLFRST = SQL1 & SQL2 & SQL3 & SQL4 & SQL5 & SQL6 & SQL7 & SQL8 & SQL9 & SQL10 & SQL11 & SQL12 & SQL13 & SQL14 & SQL15
SQLSEC = SQL16 & SQL17 & SQL18 & SQL19 & SQL20 & SQL21 & SQL22 & SQL23 & SQL24 & SQL25 & SQL26 & SQL27 & SQL28 & SQL29 & SQL30
SQLTHRD = SQL31 & SQL32 & SQL33 & SQL34 & SQL35 & SQL36 & SQL37 & SQL38 & SQL39 & SQL40 & SQL41
SQL = SQLFRST & SQLSEC & SQLTHRD
'--------------------------
With Sayfa3.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=ipadresi;UID=kullanıcıadı;pwd=sifre;APP=2007 Microsoft Office system;WSID=SERVMUDNTB-PC" _
, Destination:=Sayfa3.Range("$A$1")).QueryTable
.CommandText = Array(" & SQL & ")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Tablo_AS19_kaynağından_sorgula"
.Refresh BackgroundQuery:=True
End With
End Sub