BlackTDN Search

domingo, 12 de fevereiro de 2012

BlackTDN :: Excel :: Usando Microsoft Query para Consultas parametrizadas

Às vezes precisamos efetuar consultas parametrizadas e importá-las para o Microsoft Excel para melhor análise e/ou manipulação.

Poderemos usar o Microsoft Query para essa finalidade. Os artigos abaixo descrevem as formas de recuperação de dados de fontes externas:

A configuração dessa recuperação poderá ser feita de duas maneiras.

  1. Usando o aplicativo MSQRY32.EXE encontrado em sua instalação do Microsoft Office;
    1. image
  2. Usando o Microsoft Excel através da opção “do Microsoft Query” encontrada no menu Dados\De Outras Fontes conforme figura abaixo:
    1. image

Para usar o Microsoft Query diretamente, verifique, em sua instalação do Microsoft Office, a existência do arquivo MSQRY32.EXE e abra-o. No meu caso, e considerando a versão do Microsoft Office instalada, esse arquivo encontra-se em c:\Arquivos de programas\Microsoft Office\Office12\MSQRY32.EXE.

image

Para elaborar a primeira consulta, selecione “Arquivo\Novo” e selecione a “Fonte de Dados”. No exemplo a seguir, utilizarei a “Fonte de Dados”, configurada via ODBC, que aponta para o Banco de Dados em SQL Server. Selecione a sua “Fonte de Dados” e depois clique em OK.

image

Feito isso, será disponibilizado o “Assistente de Consulta”

image

Selecione a Tabela, e escolha os campos para a consulta. No meu caso selecionarei a Tabela SE2010 e todos os campos (apenas para habilitar o botão avançar)

image

Poderemos adicionar Filtros à consulta através do “Assistente de Consulta – filtrar dados” mas, para o exemplo, e considerando que a “Consulta” será elaborada “manualmente” apenas clique em “Avançar”

image

Poderemos, também, classificar os dados de acordo com determinada(s) coluna(s). No nosso caso faremos a classificação “manual” então, bastará clicarmos em “Concluir”.

image

Feito isso, os os dados serão apresentados de acordo com uma consulta “pré-elaborada” pelo próprio “Assistente” e de acordo com a Tabela e Campos Selecionados.

image

“Clique” no botão SQL para visualizar a “query” elaborada.

image

E, agora, vamos alterá-la conforme as nossas necessidades. Para o exemplo utilizarei a seguinte expressão SQL

image

SELECT
     SE2.E2_FILIAL,
     SE2.E2_PREFIXO,
     SE2.E2_NUM,
     SE2.E2_PARCELA,
     SE2.E2_TIPO,
     SE2.E2_FORNECE,
     SE2.E2_LOJA,
     SE2.E2_NOMFOR,
     CONVERT(CHAR(10),CONVERT(datetime,SE2.E2_EMISSAO,103),103) AS 'E2_EMISSAO',
     CONVERT(CHAR(10),CONVERT(datetime,SE2.E2_VENCTO,103),103)  AS 'E2_VENCTO',
     CONVERT(CHAR(10),CONVERT(datetime,SE2.E2_VENCREA,103),103) AS 'E2_VENCREA',
     CONVERT(CHAR(10),CONVERT(datetime,SE2.E2_BAIXA,103),103)   AS 'E2_BAIXA',
     SE2.E2_BCOPAG,
     SE2.E2_VALOR,
     SE2.E2_NATUREZ
FROM
     SE2010 SE2
WHERE
     (SE2.D_E_L_E_T_='*')
AND
     (
               SE2.E2_EMISSAO
          BETWEEN
               CONVERT(CHAR(8),CONVERT(datetime,?,103),112)
          AND
               CONVERT(CHAR(8),CONVERT(datetime,?,103),112)
     )
ORDER
     BY SE2.E2_FILIAL,
     SE2.E2_PREFIXO,
     SE2.E2_NUM,
     SE2.E2_PARCELA,
     SE2.E2_TIPO,
     SE2.E2_FORNECE,
     SE2.E2_LOJA

Observe que usei o caractere “?” na condição da “Query”. Será a partir dele que os parâmetros serão definidos. Teremos tantos parâmetros quanto o número de “?” encontrados.

Agora, alterando a “Query” original pela customizada teremos:

image

Clique em “OK” para continuar o processo de elaboração. Observe que, ao clicar em “OK” o Microsoft Query irá abrir, automaticamente, a tela para a digitação dos parâmetros.

image

Informe o valor inicial do Intervalo, no meu caso 01/01/2011

image

e, considerando que temos duas “?” uma nova janela de parâmetro será aberta, onde informarei 31/01/2011

image

A consulta será executada de acordo com a parametrização.

image

Agora vamos “Nomear” os parâmetros “Formalmente” (não é necessário mais uma boa prática uma vez que irá auxiliar ao usuário a identificar o que deverá ser informado nos parâmetros). Para isso, clique em “Exibir\Parâmetros…”

image

e, depois, em editar.

image

Irei denominar o primeiro parâmetro de “data_da_emissao_inicial” e o segundo de “data_da_emissao_final”. “OK” para confirmar.

image

Observe que o Microsoft Query, atualiza, automaticamente, as variáveis

image

Pronto. A nossa consulta “Parametrizada” já foi elaborada. Agora vamos salvá-la e abrí-la pelo Microsoft Excel.

image

Fechamos o aplicativo Microsoft Query e, agora, vamos utilizar o “Abrir Com” para abrir a nova consulta personalizada através do Microsoft Excel

image

A seguinte mensagem será apresentada:

image

Selecione “Habilitar” e os parâmetros para a consulta ser-lhe-ão apresentados. Informe o valor do Primeiro parâmetro, no meu caso 01/02/2011 e clique em “OK”.

image

Poderá usar uma “Célula” como referência e configurar a atualização automática sempre que o valor da célula de referência for alterado. Após a confirmação do Primeiro Parâmetro, ser-lhe-a apresentado o Segundo Parâmetro; no qual informarei 28/02/2011.

image

Confirme e veja o resultado a seguir.

image

É bem provável que, na primeira vez em que o arquivo for aberto pelo Microsoft Excel, o seguinte erro seja apresentado:

Não é possível derivar informações de parâmetros quando o marcador de parâmetro é um argumento de função

Para corrigi-lo, siga o Link: XL2000: Não é possível derivar informações de parâmetros quando o marcador de parâmetro é um argumento de função onde achará a seguinte solução para um problema já conhecido.

  1. Saia do Excel.
  2. Clique em Iniciar e, em seguida, clique em Executar .
  3. Na caixa Abrir , escreva regedit e, em seguida, clique em OK .
  4. Localize e, em seguida, clique para selecionar a seguinte chave de registo:

    HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options

  5. Com a chave anterior selecionada, no menu Editar , aponte para Novo e, em seguida, clique em AllowFailParam .
  6. Escreva AllowFailParam e, em seguida, prima ENTER.
  7. Clique com o botão direito do rato AllowFailParam e, em seguida, clique em Modificar .
  8. Na caixa dados do valor , escreva 1 e, em seguida, clique em OK .
  9. No menu ficheiro , clique em Sair para sair do Editor de registo.

Poderá usar o conteúdo abaixo para atualizar automaticamente o “REGISTRO” do Windows com a correção. Para isso, copie o conteúdo. Salve em um arquivo .reg (no meu caso ms_office_12_AllowFailParam.reg ) e execute-o. Atente para a versão do Microsoft Office instalada. A solução apresentada referenciava o Office\9.0. Observe que no meu caso a versão é a Office\12.0 Ajuste o Registro de acordo com a versão do Office instalada.

ms_office_12_AllowFailParam.reg

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options]
"AllowFailParam"=dword:00000001

image

image 

Feita a devida alteração no registro basta abrir a consulta personalizada.

No caso de optar por vincular os parâmetros da consulta a “Células” do Microsoft Excel proceda da seguinte forma:

  1. Crie uma nova pasta de trabalho denominada “parâmetros”
    1. image
  2. Utilize a Primeira coluna para definir a Descrição dos Parâmetros e a segunda para os seus valores. Formate a segunda coluna como “Texto”
    1. image
    2. image
  3. Depois selecione a planilha onde a consulta está definida, no exemplo “consulta_SE2_parametrizada”. Selecione o menu “Dados\Conexões”
    1. image
  4. Propriedades:
    1. image
  5. Propriedades\Definições:
    1. image
  6. Propriedades\Definições\Parâmetros:
    1. image
  7. E faça a vinculação do parâmetro à Célula correspondente
    1. image
    2. image

… continua no próximo post

[]s

иαldσ dj

16 comentários:

  1. Oi tudo bem, gostei muito do exemplo, me ajudou muito, mas eu tenho um problema eu estou fazendo uma query usando union all e o microsoft query não aceita os parâmetros por cauda do union, teria um jeito de eu colocar os parâmetros mesmo usando unions, ou caso não tem como eu fazer outro tipo de operação sem eu usar o union.

    ResponderExcluir
  2. COMO USAR ESSE MESMO CASO, CASO EU TIVER UTILIZADO UNION NO MICROSOFT QUERY, ELE DA UM ERRO, NÃO SE PODE CRIAR PARAMETROS COM UNION, COMO RESOLVO ISSO?

    ResponderExcluir
    Respostas
    1. amigo, quando aparecer a tela para inserir o sql, rode uma consulta qualquer, só para devolver os dados na planilha, depois altere a consulta nas propriedades da consulta na aba Dados

      Excluir
  3. Muito legal este post. É possível utilizar uma célula do excel que possui um valor concatenado? Exemplo: na célula a1 eu tenho o valor "1, 2, 3, 4, 5" e minha query seja: "select * from tabela where cd_item in (?)" E eu aponte o parâmetro para a célula a1. Funciona dessa forma?
    Obrigado!

    ResponderExcluir
    Respostas
    1. Olá Amigo, Estou com o mesmo problema. Você conseguiu resolver como?

      Excluir
  4. Estou usando Join em uma query e o excel esta informando "nenhum valor foi fornecido a um ou mais parametros" o que pode ser?

    ResponderExcluir
    Respostas
    1. Junior,

      Crie uma View no SGBD e referencie-a dentro do Microsoft Query (Excel)

      Excluir
    2. devo colocar o parametro "?" nesse view?

      Excluir
  5. Me ajudou muito!!! No entanto, não consigo parametrizar vários valores, por exemplo, select * from tabela1 where campo1 in (?), os parametros podem ser 01 apenas ou 01,02,03,...,. Para um paramentro apenas funciona! Mas para vários paramentros na mesma "?" não funciona. Alguém tem alguma sugestão??? Obs.: o campo1 é do tipo number.

    ResponderExcluir
    Respostas
    1. Você pode usar a funçao CONCATENAR do excel para pegar o intervalo numa celula à parte, e usar essa selula já com os campos agrupados com vírgula!

      Excluir
  6. Muito bom o post! Está me ajudando muito!

    ResponderExcluir
  7. É bem util esse tipo de consulta, porem ainda estou tendo muita dor de cabeça quando vou utilizar consultas que já criei o no sqlserver, entre 3 tabelas por exemplo, que utilizo join sempre me retorna o erro "consulta não pode ser exibida graficamente". Porem a consulta está correta, eu a executo normalmente no sqlserver, ou chamando no Excel através de outro método.
    Essa é a única forma que consigo trabalhar com parâmetros dentro do EXCEL ?

    ResponderExcluir
  8. Ótimo post, eu precisava usar consultas com ligação em várias tabelas e/ou consultas em funções com parâmetros, porém o ms query informa "Parâmetros não são permitidos em consultas que não podem ser exibidas graficamente", como eu faço pra trabalhar dessa forma?.

    ResponderExcluir
    Respostas
    1. Facilite a vida do Microsoft Query: Resolva isso no SGBD (Procedure e/ou View) .

      Excluir
    2. Sim, consegui usando procedure mesmo, com a função "CALL" usei "?" como parâmetro, ficou massa! obrigado :)

      Excluir