Jump to content
Fórum Script Brasil
  • 0

[DUVIDA] Como armazenar resultado de um select para outro select


Question

Boa tarde,

Estou criando um script VBA para Excel onde eu realizo a consulta a uma db já existente do Postgress.

Já realizei a conexão, já montei o select e já aparece o resultado na planilha.

O problema é: Após realizar essa primeira consulta, eu queria armazenar o id do cliente em uma varivael para montar um outro select para consultar uma outra tabela.

 

Exemplo do código que estou utilizando:

Sub consultar_tudo()
'
' consultar_tudo Macro
'

'
    Dim os As String
    Dim consulta As String
    Dim db As String
    Dim server As String
    Dim login As String
    Dim senha As String
    Dim id As String
    Dim cons_receita As String
    
    
    id = Range("A11").Value
    cons_receita = "SELECT * FROM public.receitas receitas WHERE id_cliente = '" & id & "' ORDER BY id_receita DESC"
    os = Range("B2").Value
    server = Range("F2").Value
    db = Range("G2").Value
    login = Range("J2").Value
    senha = Range("K2").Value
    port = Range("L2").Value
    consulta = "SELECT * FROM public.clientes clientes WHERE cod_cliente = '" & os & "' ORDER BY id_cliente ASC"
    
    With ActiveWorkbook.Connections("Consulta de Postgres").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array(consulta)
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DRIVER={PostgreSQL ANSI(x64)};DATABASE=" & db & ";SERVER=" & server & ";PORT=" & port & ";UID=" & login & ";PWD=" & senha & ";SSLmode=disable;ReadOnly=0;Protocol=" _
        ), Array( _
        "7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar" _
        ), Array( _
        "charSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongV" _
        ), Array( _
        "archar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature" _
        ), Array( _
        "=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1" _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    
    With ActiveWorkbook.Connections("Consulta de Postgres")
        .Name = "Consulta de Postgres"
        .Description = ""
    End With
        ActiveWorkbook.Connections("Consulta de Postgres").Refresh
        
        
    With ActiveWorkbook.Connections("Consulta de Postgres1").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array(cons_receita)
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DRIVER={PostgreSQL ANSI(x64)};DATABASE=" & db & ";SERVER=" & server & ";PORT=" & port & ";UID=" & login & ";PWD=" & senha & ";SSLmode=disable;ReadOnly=0;Protocol=" _
        ), Array( _
        "7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar" _
        ), Array( _
        "charSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongV" _
        ), Array( _
        "archar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature" _
        ), Array( _
        "=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1" _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    
    With ActiveWorkbook.Connections("Consulta de Postgres1")
        .Name = "Consulta de Postgres1"
        .Description = ""
    End With
    
    ActiveWorkbook.Connections("Consulta de Postgres1").Refresh
    ActiveWorkbook.RefreshAll
End Sub

 

Link to post
Share on other sites

1 answer to this question

Recommended Posts

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Forum Statistics

    • Total Topics
      149402
    • Total Posts
      645890
×
×
  • Create New...