Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
SQL usando variavel
 
Notifications
Clear all

SQL usando variavel

 
2024 - VBA & Macros
Post Recente por adGere 8 anos atrás
2 Posts
2 Usuários
0 Reactions
1,164 Visualizações
RSS
 artzn
(@artzn)
Posts: 32
Eminent Member
Topic starter
 

Prezados,

Olhei alguns tópicos, até achei alguns que me ajudaram como:
viewtopic.php?f=10&t=6249

Porem nao consigo executar.. continua dando erro

 zData = Range("A1")
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server;SERVER=Banco;UID=user;;APP=Microsoft Office 2013;WSID=note" _
        , Destination:=Range("$A$2")).QueryTable
        .CommandType = 0
        .CommandText = Array( _
        "SELECT " & Chr(13) & "" & Chr(10) & "*" & Chr(13) & "" & Chr(10) & "FROM" & Chr(13) & "" & Chr(10) & "vw_bas_backlog" & Chr(13) & "" & Chr(10) & "WHERE estacao IN" & Chr(13) & "" & Chr(10) & "('ARC','ATL8','ATL9','ATLA','ATT3','BBOA','BBR2','BBR3','BDR1','BICA','BOBG','BOBH','BOBI','BOBK','BOBL','BOBM','BOT'," & Chr(13) & "" & Chr(10) & "'BRD2','BRD4','CAM','CEF2','" _
        , _
        "CEM','CIN','CITI','COP','CRRD','CTA4','CTAX','CTLB','CX02','ECM','EQUA','FLA','FLO','GVT'," & Chr(13) & "" & Chr(10) & "'IPA','IPBQ','JCCQ','LBCM','LBCN','LBCO','LBCP','LBCQ','LBCR','LBCS','LBCT','LBCU','LBCV','LBCY','LBCW','LBC" _
        , _
        "Z','LEB'," & Chr(13) & "" & Chr(10) & "'LEM','LNCC','MCT','MRED','NTEL','PGJ1','POL','PRA','SCD','SPR2','SRT','SUL','TIR','TGVM','TPA','TVO','UOL'," & Chr(13) & "" & Chr(10) & "'ALG2','AUST','BERO','CAEL','CIBA','CODIC','COSE','DQX','ENPE','GNDU','IBIC','I" _
        , _
        "GI','ILDM','ITSA','JPRI','MCO '," & Chr(13) & "" & Chr(10) & "'MESQ','MRU','NIU','NLP','NOCP','PBI','PDAN','PIBT','PNCO','POBL','POSP','QUEA','SBTO','SMI','SUNA','UERL','VIMU'," & Chr(13) & "" & Chr(10) & "'ACH','BCRE','BGR','BGU','BPN','BRB','BRF','CAC','" _
        , _
        "CARD','CGD','COL','COS','CSR','CTA6','END','END2','ENN','GAL','HNP'," & Chr(13) & "" & Chr(10) & "'IMPS','INT2','INTG','IRJ','JAB','JAC','JDA','JGU','JOHV','ASM','MAG','MAR','MDR','MGBS','MRCN','MSO','MSP','NAO','NHL'," & Chr(13) & "" & Chr(10) & "'FLE','P" _
        , _
        "AV','PGU','PIE','PQT','RAM','RMA','SCR','SCZ','SLC','SPB','TIJ','TIM','TIMA','TIRN','TMC2','VGL','VIB','VKY'," & Chr(13) & "" & Chr(10) & "'VMI','VVA','ZIC','ZIP','ZIS','ABA','ABV','ALV','ATDM','BAWO','BBN','BRM','BSP','BTJ','CD" _
        , _
        "RK','CDS','CEB','CVI','DTW','FRE','GBN','IBCC','JCP','LGM','MCA'," & Chr(13) & "" & Chr(10) & "'MLA','MPI','OPGL','PNB','RDB','RDC','RIT','RPA','TQA','VBT','VGD','VPAN','CRRD','ALV','BGR','B003','ETRB','IBM','R002','R007','R010'" _
        , _
        ",'R011','R014','R009','R015','R017','R020','R022','R023','R024','R025','R026','R027'," & Chr(13) & "" & Chr(10) & "'R029','R031','R032','R036','R036','R037','R038','R039','R040','R041','R042','R044','R045','R046','R047','R048','" _
        , _
        "R050','R052'," & Chr(13) & "" & Chr(10) & "'R054','R055','R057','R058','R061','R062','R063','R064','R066','R067','R070','R071','R072','R073','R074','R075','R078','R079'," & Chr(13) & "" & Chr(10) & "'R080','R081','R083','R085','R086','R089','R100','R101','" _
        , _
        "R104','R105','R111','R112','R114','R117','R118','R119','R120','R121'," & Chr(13) & "" & Chr(10) & "'R122','R123','R124','R125','R126','R127','R128','R132','R140','R145','R146','R147','R148','R151','R152','R155','R156','R166'," & Chr(13) & "" & Chr(10) & "'" _
        , _
        "R167','R168','R169','R170','R171','R172','R174','R177','R178','R180','R181','R186','R187','R199','R208','R209','R238','R251'," & Chr(13) & "" & Chr(10) & "'R602','R675','R684','R700','R979','RB1','T013')" & Chr(13) & "" & Chr(10) & "AND area_tecnica IN ('T" _
        , "X','FO') " & Chr(13) & "" & Chr(10) & "AND dt_foto ='"& zData &'")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabela_1"
        .Refresh BackgroundQuery:=False
    End With

O problema é com a variaval da data no final do WHERE..

 
Postado : 29/08/2016 2:23 pm
 adGere
(@adgere)
Posts: 76
Trusted Member
 

Tente substituir 

"AND dt_foto ='"& zData &'")

por 

"AND dt_foto ='"& zData & "')"

caso não de certo ainda, tente criar uma variavel para montar a sql, depois vc utiliza a variavel

Dim strSQL As String

strSQL = "SELECT * FROM vw_bas_backlog "
strSQL = strSQL & "WHERE estacao IN "
strSQL = strSQL & "("
strSQL = strSQL & "'ARC','ATL8','ATL9','ATLA','ATT3','BBOA','BBR2','BBR3','BDR1','BICA',"
strSQL = strSQL & "'BOBG','BOBH','BOBI','BOBK','BOBL','BOBM','BOT','BRD2','BRD4','CAM',"
strSQL = strSQL & "'CEF2','CEM','CIN','CITI','COP','CRRD','CTA4','CTAX','CTLB','CX02',"
strSQL = strSQL & "'ECM','EQUA','FLA','FLO','GVT','IPA',"
strSQL = strSQL & "'IPBQ','JCCQ','LBCM','LBCN','LBCO','LBCP','LBCQ','LBCR','LBCS','LBCT',"
strSQL = strSQL & "'LBCU','LBCV','LBCY','LBCW','LBCZ','LEB','LEM','LNCC','MCT','MRED',"
strSQL = strSQL & "'NTEL','PGJ1','POL','PRA','SCD','SPR2','SRT','SUL','TIR','TGVM','TPA',"
strSQL = strSQL & "'TVO','UOL','ALG2','AUST','BERO','CAEL','CIBA','CODIC','COSE','DQX',"
strSQL = strSQL & "'ENPE','GNDU','IBIC','IGI','ILDM','ITSA','JPRI','MCO','MESQ','MRU',"
strSQL = strSQL & "'NIU','NLP','NOCP','PBI','PDAN','PIBT','PNCO','POBL','POSP','QUEA',"
strSQL = strSQL & "'SBTO','SMI','SUNA','UERL','VIMU','ACH','BCRE','BGR','BGU','BPN',"
strSQL = strSQL & "'BRB','BRF','CAC','CARD','CGD','COL','COS','CSR','CTA6','END','END2',"
strSQL = strSQL & "'ENN','GAL','HNP','IMPS','INT2','INTG','IRJ','JAB','JAC','JDA','JGU',"
strSQL = strSQL & "'JOHV','ASM','MAG','MAR','MDR','MGBS','MRCN','MSO','MSP','NAO','NHL',"
strSQL = strSQL & "'FLE','PAV','PGU','PIE','PQT','RAM','RMA','SCR','SCZ','SLC','SPB',"
strSQL = strSQL & "'TIJ','TIM','TIMA','TIRN','TMC2','VGL','VIB','VKY','VMI','VVA','ZIC',"
strSQL = strSQL & "'ZIP','ZIS','ABA','ABV','ALV','ATDM','BAWO','BBN','BRM','BSP','BTJ',"
strSQL = strSQL & "'CDRK','CDS','CEB','CVI','DTW','FRE','GBN','IBCC','JCP','LGM','MCA',"
strSQL = strSQL & "'MLA','MPI','OPGL','PNB','RDB','RDC','RIT','RPA','TQA','VBT','VGD',"
strSQL = strSQL & "'VPAN','CRRD','ALV','BGR','B003','ETRB','IBM','R002','R007','R010',"
strSQL = strSQL & "'R011','R014','R009','R015','R017','R020','R022','R023','R024',"
strSQL = strSQL & "'R025','R026','R027','R029','R031','R032','R036','R036','R037',"
strSQL = strSQL & "'R038','R039','R040','R041','R042','R044','R045','R046','R047','R048',"
strSQL = strSQL & "'R050','R052','R054','R055','R057','R058',"
strSQL = strSQL & "'R061','R062','R063','R064','R066','R067','R070','R071','R072','R073',"
strSQL = strSQL & "'R074','R075','R078','R079',R080','R081','R083','R085','R086','R089',"
strSQL = strSQL & "'R100','R101','R104','R105','R111','R112','R114','R117','R118','R119',"
strSQL = strSQL & "'R120','R121','R122','R123','R124','R125','R126','R127','R128','R132',"
strSQL = strSQL & "'R140','R145','R146','R147','R148','R151','R152','R155','R156','R166',"
strSQL = strSQL & "'R167','R168','R169','R170','R171','R172','R174','R177','R178','R180',"
strSQL = strSQL & "'R181','R186','R187','R199','R208','R209','R238','R251','R602','R675',"
strSQL = strSQL & "'R684','R700','R979','RB1','T013') "
strSQL = strSQL & "AND area_tecnica IN ('T', 'X','FO') "
strSQL = strSQL & "AND dt_foto ='" & zData & "'"
 
Postado : 29/08/2016 6:10 pm
Forum Jump:
  Tópico Anterior
Próximo Tópico