[RESOLVIDO] Join de ODBC com named range - possível?

Monstrinho

13-09-2012 20:06:47

E aí galera,
Bom saber que existe um site bom de Excel brasileiro. Não tenho problema em ler em inglês, mas saber que há uma comunidade por aqui é muito bom.

Versão "too long, didn't read":

Quero algo do tipo:

Código: Selecionar todos

SELECT odbc.*
FROM odbc
JOIN [named range na pasta de trabalho] (etc...)
É possível? Aceito outras soluções, como detalhado abaixo, mas essa pra mim é o ideal.

Versão detalhada:

Até recentemente, tenho feito os estudos pro meu trabalho do jeito "errado": rodo as queries no SAS (um programa de análise estatística, banco de dados, etc. Na prática, eu o utilizo como o Access, mas ele suporta tabelas e queries bem maiores, e em SQL), copio o resultado e colo em um template do Excel com as fórmulas prontas. Tenho já lido a um tempo que o jeito "certo" seria conectar o Excel diretamente à sua fonte de dados, por ODBC. A TI instalou o driver ODBC do SAS essa semana, e desde então estou embasbacado com o quão bem tudo funciona. Posso até passar parâmetros por células, é absolutamente lindo. Poréeem...

Minhas queries utilizam listas de parâmetros, e não valores isolados. São queries do tipo:

Código: Selecionar todos

SELECT b.*
FROM base_vendas_anuais AS b
JOIN lista_vendedores AS v ON v.HANDLE = b.VENDEDOR
JOIN lista_produtos AS p ON p.HANDLE = b.PRODUTO
onde lista_produtos e lista_vendedores são parâmetros diferentes para cada estudo. Até agora, consegui que funcionasse de dois jeitos, mas ambos são insuficientes por motivos diferentes:

Posso inserir os parâmetros no SAS, como tenho feito até agora, e só mandar o Excel rodar novamente a query, do jeito que está escrito acima. Não me interessa por dois motivos: preciso utilizar os dois programas diretamente, enquanto gostaria que o SAS fosse apenas a fonte de dados; além disso, ao rodar outro estudo, a tabela de parâmetros mudaria, e se eu abrir um estudo anterior há o risco de atualizar a query novamente por acidente, e perder os dados antigos.

Posso, também, criar no arquivo do estudo duas named ranges com os HANDLEs de produtos e vendedores, utilizar o VBA para concatenar cada uma das ranges, com valores separados por vírgulas, e escrever o resultado em duas células, e finalmente utilizar a função de parâmetros da fonte externa de dados para que minha query faça, em vez de um JOIN, uma cláusula WHERE IN (...). A princípio, esta solução seria a ideal, o que eu procuro em termos de interface é justamente salvar a entrada de dados (quais vendedores e produtos) na mesma pasta de trabalho que o relatório. O problema, agora, é que a lista de itens costuma ser muito grande, às vezes realmente grande, chegando a uns poucos milhares de itens; WHERE IN simplesmente não aguenta!

A solução que eu desejaria ver funcionando é algo do gênero:

Código: Selecionar todos

SELECT odbc.*
FROM odbc
JOIN [named range na pasta de trabalho] (etc...)
É possível?

Também me passa outra possibilidade na cabeça: utilizar o ODBC para criar uma tabela temporária dentro do SAS, exportar os parâmetros do Excel para ela, e realizar o JOIN normalmente dentro do SAS. Posso fazer isso diretamente no texto de comando da fonte de dados? Preciso de VBA? Posso inserir todos os parâmetros de uma só vez, ou preciso criar a tabela em branco e povoá-la uma linha de cada vez em um loop? Para que isso funcione, a tabela temporária deve ser criada sempre que eu atualizar os dados, assim não corro o risco de pegar os parâmetros de um estudo antigo que tenham sido salvos no SAS por acaso.

Se alguém conseguir me ajudar com este pepino, o agradecimento é garantido!

webmaster

13-09-2012 20:56:25

Colega,

Usei muito pouco, relembrando isso:

http://mc-computing.com/Databases/LinkToExcel.html

É uma sintaxe que você pode utilizar. O nome da 'tabela' deve ser o da planilha com um sifrão no final, ou um named range. Não sei como funciona nas internas, por isso, recomendo que você faça o teste. Dando certo, seria possível fazer algo como:

Código: Selecionar todos

SELECT *
FROM   'C:\Planilhas\plan.xls'.'RangeNomeado'
Espero que ajude.

Abraços

Monstrinho

14-09-2012 13:48:07

Massa, vou tentar hoje à tarde e aviso como foi!

Monstrinho

14-09-2012 20:58:11

Uai... escrevi a resposta, mas acho que esqueci de enviar.

Não funcionou, pelo menos não direto no texto da conexão. Acho que é porque eu estou tentando juntar dados de duas conexões diferentes, no exemplo que você enviou me parece que o nome de arquivo é para uma fonte de dados do mesmo tipo que a conexão criada.

Vou tentar um pouco com VBA, depois eu relato minhas aventuras (vai que é útil pra alguém). Por enquanto, estou tentando com isso:

Código: Selecionar todos

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
.CommandText = "SELECT ..."
End With
ActiveWorkbook.Connections("MYSERVER").Refresh
End Sub
Funciona com SELECT, mas não com CREATE TABLE. Além disso, ele não exatamente muda o comando da conexão, mas cria uma cópia com o comando novo. Eu, hein. Mas vamo lá, uma hora sai isso.

Monstrinho

14-09-2012 21:28:23

QUASE! Descobri que tem uma biblioteca da microsoft chamada ADO que substitui (melhora?) o acesso ODBC. Para ativá-la, dentro do Editor Visual Basic, vá em Ferramentas>Referências, e marque Microsoft Active X Database Object.

Daí, fiz o seguinte teste:

Código: Selecionar todos

Sub teste()
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stCon As String, stSQL As String
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    stCon = "DSN=****"
    cnt.ConnectionString = stCon
    stSQL = "CREATE TABLE a.TEMP_VENDEDORES (CDVENDEDOR VARCHAR (50))"
     
    cnt.Open
    rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText
     
    If CBool(rst.State And adStateOpen) = True Then rst.Close
    Set rst = Nothing
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing
     
End Sub
e tada! Tabela criada. Agora tô de saco cheio, mas segunda feira vou ver se implemento um loop que leia o named range, insira as linhas nesta tabela, faça a query com os joins necessários dentro do próprio DB, retorne os resultados e insira em uma tabela dinâmica direto pelo VBA... e, se deus quiser, voilá!

Novamente, segunda eu relato meus progressos. Quando tudo estiver pronto, eu posso postar aqui um exemplo completo de uma query externa com vários parâmetros, pra quem tiver o mesmo problema.

Monstrinho

21-09-2012 20:33:17

É galera, não tenho um código completo ainda por que tem prioridades na frente, mas a manha é essa mesma:
  • Planeje a sua query, liste quais os campos que você quer listar os parâmetros (por exemplo, lista de vendedores)
  • Crie uma taela no Excel para listar estes parâmetros
  • No VBA, monte uma query que crie uma tabela temporária no servidor onde estão seus dados, depois percorra a sua tabela do Excel inserindo os valores nesta tabela temporária
  • Ainda no VBA, monte a query principal, que puxe os dados que você quer fazendo JOIN com a tabela temporária do seu servidor
  • Traga os dados, delete a tabela temporária
  • ...?
  • Lucre!
Funciona perfeitamente, pode ser escalonada para complexidades maiores, você pode inserir uma tabela completa (e não só uma coluna), e fazer JOINs direto com ela... a RAM é o limite!