<\/p>\n
Essa dica \u00e9 pra quem precisa importar dados do postgres (ou qualquer outro SGBD) para o excel.<\/p>\n
O comando \u00e9 via macro e eu s\u00f3 usei no Excel 2003.<\/p>\n
‘Esta fun\u00e7\u00e3o conecta no banco de dados, executa o sql e devolve na celula indicada Dim cnt As ADODB.Connection ‘ valida planilha de saida ‘ valida celula de saida stADO = “Driver={PostgreSQL UNICODE};Server=” & rIP & “;Port=” & rPorta & “;Database=” & rBanco & “;Uid=” & rUsuario & “;Pwd=” & rSenha & “;”<\/p>\n Set wbBook = ActiveWorkbook With wsSheet stSQL = rSql<\/p>\n Set cnt = New ADODB.Connection<\/p>\n With cnt ‘Here we add the Recordset to the sheet from A1 ‘Cleaning up. End Sub<\/p>\n <\/p>\n D\u00e1 pra fazer gravando uma macro usando a importa\u00e7\u00e3o de dados externos do excel. Mas ai depende de ter uma odbc na maquina e o resultado nao sai da tabela (at\u00e9 sai, mas da muito trabalho), o que aumenta o tamanho dela.<\/p>\n <\/p>\n \u00c9 isso. Abra\u00e7os!<\/p>\n","protected":false},"excerpt":{"rendered":" Essa dica \u00e9 pra quem precisa importar dados do postgres (ou qualquer outro SGBD) para o excel. O comando \u00e9 via macro e eu s\u00f3 usei no Excel 2003. Sub Executa_SQL_PG(rSql As String, rPlaSaida As String, rCelSaida As String, rIP As String, rPorta As String, rBanco As String, rUsuario As String, rSenha As String, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,12,19,20],"tags":[],"class_list":["post-424","post","type-post","status-publish","format-standard","hentry","category-banco-de-dados","category-dicas","category-programacao","category-ti"],"_links":{"self":[{"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/posts\/424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/comments?post=424"}],"version-history":[{"count":0,"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/posts\/424\/revisions"}],"wp:attachment":[{"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/media?parent=424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/categories?post=424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rafaelamorim.com.br\/wp\/wp-json\/wp\/v2\/tags?post=424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nSub Executa_SQL_PG(rSql As String, rPlaSaida As String, rCelSaida As String, rIP As String, rPorta As String, rBanco As String, rUsuario As String, rSenha As String, rSchema As String)<\/strong><\/p>\n
\n‘ rSql\u00a0\u00a0\u00a0\u00a0\u00a0 => Comando a ser executado
\n‘ rPlaSaida => Nome da planilha onde os dados v\u00e3o retornar. Se em branco, retorna na atual
\n‘ rCelSaida => Endere\u00e7o de C\u00e9lula onde os dados v\u00e3o sair. Se em branco, retorna na A5
\n‘ rIP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 => IP do servidor
\n‘ rPorta\u00a0\u00a0\u00a0 => Porta onde conectar
\n‘ rBanco\u00a0\u00a0\u00a0 => Nome do Banco de Dados
\n‘ rUsuario\u00a0 => Nome do Usuario
\n‘ rSenha\u00a0\u00a0\u00a0 => Senha do Usu\u00e1rio
\n‘ rSchema\u00a0\u00a0 => Schema a considerar<\/p>\n
\nDim rst As ADODB.Recordset
\nDim stSQL As String
\nDim wbBook As Workbook
\nDim wsSheet As Worksheet
\nDim rnStart As Range
\nDim stADO As String<\/p>\n
\nIf rPlaSaida <> “” Then
\nSheets(rPlaSaida).Select
\nEnd If<\/p>\n
\nIf rCelSaida = “” Then
\nrCelSaida = “A5”
\nEnd If<\/p>\n
\nSet wsSheet = wbBook.Worksheets(rPlaSaida)<\/p>\n
\nSet rnStart = .Range(rCelSaida)
\nEnd With<\/p>\n
\n.CursorLocation = adUseClient
\n.Open stADO
\n.CommandTimeout = 5000000
\nSet rst = .Execute(stSQL)
\nEnd With<\/p>\n
\nrnStart.CopyFromRecordset rst<\/p>\n
\nrst.Close
\ncnt.Close
\nSet rst = Nothing
\nSet cnt = Nothing<\/p>\n