Excel - Conexões em um site da Web em tempo real

Novembro 2016


Conexões em um site da Web em tempo real
Autor : Lermite
Data : 21 de novembro de 2012

Esta dica trata das conexões em um site web.

Uma conexão compreende duas partes distintas:
A tabela "QueryTables" com as configurações de conexão e         
A conexão propriamente dita
.

Cada uma delas pode ter o mesmo nome, ou diferente. O nome da tabela só pode conter caracteres de letras ou números, ao contrário do nome da conexão que a autoriza. Uma tabela é criada no espaço da planilha, já a conexão é criada no espaço da pasta de trabalho, ou seja, todas as conexões de um aplicativo devem ter um nome diferente, enquanto que os nomes das tabelas são específicos às planilhas correspondentes.

Elas também podem ser nomeadas pelo seu índice; porém, este sistema não é confiável, se houver várias conexões na pasta de trabalho, os índices serão adicionados internamente à pasta de trabalho e, sempre, ao índice 1 recuando os outros, o que dificulta o controle.

Para uma conexão pontual melhor uma nova conexão, mas não se esqueça de suprimí-la depois. O fato de excluir a tabela também exclui a conexão, o que não é válido para a conexão que não remove a tabela.
    QueryTables(1).Delete         
Ou         
    QueryTables(SeuNome).Delete


Quando a conexão for criada pelo método "Add" e não for excluída, ela fica disponível na pasta de trabalho, mesmo se esta estiver fechada. Assim sendo, não é mais necessário e, até desaconselhável, recriá-la, basta atualizá-la. Mas, para isso, é preciso saber o nome da conexão para atualizá-la ou o nome da tabela, para alterar qualquer uma das suas configurações. A atualização também pode ser feita a partir da tabela. Vamos ver abaixo como atribuir nomes e usá-los.

Criar uma nova conexão, em geral usamos o método "With" o que evita lembrar a tabela para cada propriedade assim, implicitamente, o ponto antes do nome da propriedade será sinônimo de tabela.

 With Sheets("NomeDaPlanilha")         
     With .QueryTables.Add(Connection:= _         
     "URL;http://OSiteWebAalcançar.com »,  Destino:=.Range(EndereçoCélula))                                    
 .....Lista das propriedades detalhadas abaixo         
        End With         
    End With


O nome atribuído à tabela: para o exemplo utilizaremoss o nome da extensão do URL de conexão onde os caracteres inválidos, como a barra "/" foram retirados

 ONomeDaTabela  = Replace("NZD/USD", "/", "")         
       .Name =  ONomeDaTabela 

A propriedade FieldNames : exibe os cabeçalhos de linhas/colunas (título dos campos)
       .FieldNames = False / True  

A propriedade FillAdjacentFormulas : True/ as fórmulas à direita das tabelas atualizadas são ativadas.
Veja a propriedade WebSelectionType mais abaixo.
      .FillAdjacentFormulas = False / True   


A propriedade RefreshOnFileOpen: True / Atualiza as conexões para a abertura da pasta de trabalho. Para ativar essa propriedade, você deve ter certeza de que a conexão à Internet está disponível antes de abrir a pasta de trabalho. Uma atualização sem conexão gera um erro. O que é ainda mais irritante é que este erro é repetido tantas vezes quantas houver conexões.
.RefreshOnFileOpen = False / True


A propriedade BackgroundQuery: True / Atualização de conexões em segundo plano. Geralmente usado quando as atualizações são ativadas automaticamente. É sempre possível atualizar uma conexão bloqueando o modo segundo plano, ou a propriedade Refresh.
.BackgroundQuery = False / True 


A propriedade AdjustColumnWidth : True / Ajusta a largura das colunas quando os dados proveem de uma tabela. Veja a propriedade WebSelectionType abaixo.
.AdjustColumnWidth = False / True


A propriedade.RefreshPeriod: Atualização automática das conexões exprimida em minuto(s), o tempo mínimo é de 1 minuto. O valor zero cancela a atualização.
.RefreshPeriod = 0  / 32767


A propriedade WebSelectionType pode ter três valores diferentes;
        WebSelectionType .xlEntirePage :Retorna toda a página do site selecionado         
        WebSelectionType :xlAllTables : Retorna todas as tabelas das páginas.         
        WebSelectionType .xlSpecifiedTables :  Retorna a/as tabelas específicas pela propriedade  WebTables.

Esta propriedade é vista no exemplo

Retorna as tabelas dois e três, as tabelas que não existem são simplesmente ignoradas. O que ajuda a recuperar dados de uma tabela estruturada.
.WebSelectionType .xlSpecifiedTables         
.WebTables  = "2,3"

Exemplo:

A propriedade Refresh: usada sozinha, ela atualiza a conexão na forma definida pela propriedade BackgroundQuery descrita acima.
.Refresh


Se o código VBA depende dos resultados da atualização é possível bloquear temporariamente a actualização em plano de fundo, adicionando uma configuração adicional.
.Refresh BackgroundQuery:=False


A propriedade Delete : exclui definitivamente a tabela e a conexão correspondente. É aconselhável excluir uma conexão que foi estabelecida para uma única utilização, isso evita a sobrecarga desnecessária da pasta de trabalho, o que poderia prejudicar o funcionamento da mesma.
.Delete


Nesta fase, o nome da conexão foi definido pelo Excel, se for a primeira conexão ou se as anteriores foram renomeadas, o nome padrão é "Conexão", se esse nome já existir, um número é adicionado .. Conexão1 .. Conexão2 .. etc. Portanto, é óbvio que é melhor mudar o nome das conexões conforme forem criadas para que o nome padrão não mude.

Se as conexões não forem renomeadas, é quase impossível encontrá-las mais tarde. O nome atribuído à nova conexão é sempre "Conexão", é na conexão anterior que um índice será adicionado.

Atribuir um nome a uma conexão:
          
ActiveWorkbook.Connections("Conexão").Name = NomeConexão

    With Sheets("NomeDaPlanilha")         
       With .QueryTables.Add(Connection:= _                                                          
                   "URL;http://OSiteWebAalcançar.com",  Destino:=.Range(EndereçoCélula))          
             .Name = Replace(Nome, "/", "")        'o nome da QueryTable                                    .         
            .FieldNames = True                          'exibe os cabeçalhos das linhas/colunas (título dos campos)          
            .RowNumbers = False         
            .FillAdjacentFormulas = True            'Ativa e recalcula células à direita da tabela.         
            .PreserveFormatting = False         
            .RefreshOnFileOpen = False             'Atualiza a conexão quando abrimos a pasta         
            .BackgroundQuery = True                 'Atualização em plano de fundo         
            .RefreshStyle = xlOverwriteCells        'para não deletar as leituras anteriores         
            .SavePassword = False         
            .SaveData = False         
            .AdjustColumnWidth = False         
            .RefreshPeriod = 0                      'Atualização automática mínima: 1 minuto         
            .WebSelectionType = xlSpecifiedTables   'Para selecionar dados (não a página toda)         
            .WebTables = "2,3"                      'As tabelas a serem selecionadas         
            .WebFormatting = xlWebFormattingNone         
            .WebPreFormattedTextToColumns = True         
            .WebConsecutiveDelimitersAsOne = False         
            .WebSingleBlockTextImport = True         
            .WebDisableDateRecognition = False         
            .WebDisableRedirections = False         
            .Refresh BackgroundQuery:=False         'Recupera os dados / bloqueando o programa         
            '.Delete                                'Para uso único da conexão         
        End With         
End With         
        ActiveWorkbook.Connections("Conexão").Name =  NomeDaConexão


Para editar as configurações da conexão é preciso intervir nas QueryTables
Exemplo para editar a atualização automática
        Sheets("NomeDaPlanilha").QueryTables(NomeDaTabela) .RefreshPeriod = 0                      
'Atualização automática cancelada        

        Sheets("NomeDaPlanilha").QueryTables(NomeDaTabela) .RefreshPeriod = 5                     
'Atualização automática ativada comtempo de 5 minutes.


Todas as outras propriedades odem ser alteradas da mesma forma.

Exemplo para atualizar uma conexão.
        ActiveWorkbook.Connections("NomeDaConexão").Refresh


Este método não permite alterar a configuração BackgroundQuery, a atualização será feita no modo especificado pela propriedade dos QueryTables
Para atualizar mudando temporariamente a propriedade BackgroundQuery, é preciso usar o QueryTables
Sheets("NomeDaPlanilha").QueryTables(NomeDaTabela) .Refresh BackgroundQuery:=False 


Download : Tutorial Conexão Web.doc

Uma pasta de exemplo está disponível abaixo.

Trata-se, mais particularmente, de uma conexão múltipla no mesmo site, ou seja, 20 conexões, mas poderia muito bem gerenciar essas conexões em vários sites. Elas podem ser atualizadas automaticamente, sem cronômetro (Timer), apenas usando as configurações das conexões.

Incide sobre a repatriação de dados financeiros sobre o site do Yahoo Finanças, conexão seletiva no site francês ou Inglês, dependendo das configurações do Excel.


Download da pasta:Conexão Web V4.xlsm

Download de Assitência: Ajuda de conexão Web.doc

Tradução feita por Lucia Maurity y Nouira

Veja também :
Este documento, intitulado « Excel - Conexões em um site da Web em tempo real  »a partir de CCM (br.ccm.net) está disponibilizado sob a licença Creative Commons. Você pode copiar, modificar cópias desta página, nas condições estipuladas pela licença, como esta nota aparece claramente.