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:

1. Usar o Microsoft Query para recuperar dados externos (1);
2. Utilizar o Microsoft Query para obter dados externos (2);
3. Usar o Microsoft Query para recuperar dados de uma fonte de dados externa;
4. Configurando o Microsoft Query e as fontes de dados;

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. 
2. Usando o Microsoft Excel através da opção “do Microsoft Query” encontrada no menu Dados\De Outras Fontes conforme figura abaixo:
   1. 

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.

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.

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

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)

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”

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”.

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.

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

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

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:

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.

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

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

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

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…”

e, depois, em editar.

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

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

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

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

A seguinte mensagem será apresentada:

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”.

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.

Confirme e veja o resultado a seguir.

É 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.

Saia do Excel. Clique em Iniciar e, em seguida, clique em Executar . Na caixa Abrir , escreva regedit e, em seguida, clique em OK . Localize e, em seguida, clique para selecionar a seguinte chave de registo: HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options Com a chave anterior selecionada, no menu Editar , aponte para Novo e, em seguida, clique em AllowFailParam . Escreva AllowFailParam e, em seguida, prima ENTER. Clique com o botão direito do rato AllowFailParam e, em seguida, clique em Modificar . Na caixa dados do valor , escreva 1 e, em seguida, clique em OK . 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

 

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. 
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. 
   2. 
3. Depois selecione a planilha onde a consulta está definida, no exemplo “consulta_SE2_parametrizada”. Selecione o menu “Dados\Conexões”
   1. 
4. Propriedades:
   1. 
5. Propriedades\Definições:
   1. 
6. Propriedades\Definições\Parâmetros:
   1. 
7. E faça a vinculação do parâmetro à Célula correspondente
   1. 
   2. 

… continua no próximo post

[]s

иαldσ dj