Tuto Excel - Connexions sur un site Web en temps réel

Octobre 2016


Connexions sur un site Web en temps réel


Une connexion comprend deux parties distinctes,
 La table « QueryTables» qui contient les paramètres de la connexion        
 La connexion proprement dite
.
Chacune peu avoir un nom, identique ou différent. Le nom de la table ne peut pas contenir de caractères autre que des lettres ou chiffres à l'inverse du nom de la connexion qui l'autorise. Une table est créée dans l'espace de la feuille la connexion est créée dans l'espace du classeur, autrement dit, les connexions d'une application doivent toutes avoir un nom différent alors que les noms des tables sont spécifiques à la feuille qui les contient.

Elles peuvent aussi êtres appelées par leur index, toutefois, ce système n'est pas fiable, si plusieurs connexions sont présentes sur le classeur, les index sont ajouter en interne au classeur et sont toujours ajouter à l'index 1 en reculant les autre, ce qui fait qu'il est difficile de le suivre.

Pour une connexion ponctuelle ont préférera une nouvelle connexion mais il ne faut pas oublier de la supprimer après l'emploi. Le fait de supprimer la table supprime également la connexion ce qui n'est pas vrai pour la connexion qui ne supprime pas la table.
    QueryTables(1).Delete        
Ou        
    QueryTables( SontNom).Delete

Quand une connexion a été crée par la méthode Add et qu'elle n'a pas été supprimée elle reste disponible à tout moment dans le classeur même s'il a été fermé il n'est donc plus nécessaire et même déconseillé de la recréer il suffit simplement de la rafraichir mais pour cela il faut connaître le nom de la connexion pour la réactualiser ou le nom de la table pour modifier l'un de ses paramètres, le rafraichissement peut aussi être fait à partir de la table. Nous verrons plus bas comment attribuer les noms et s'en servir.

Créer une nouvelle connexion, en général ont emploi la méthode « With » ce qui évite de rappeler la table pour chaque propriété, donc implicitement le point devant le nom de la propriété sera synonyme de la table
.        
    With Sheets("NomFeuille")        
        With .QueryTables.Add(Connection:= _        
          "URL;http://LeSiteWebAatteindre.com »,  Destination:=.Range(AdresseCellule))                                   
 ..... Liste des propriétés détaillées ci-dessous        
        End With        
    End With

Le nom attribué à la table : pour la démo j'ai pris le nom de l'extension de l'Urll de connexion dont j'ai enlevé les caractères invalide comme le caractère « / »
 LeNomDeLaTAble  = Replace("NZD/USD", "/", "")        
       .Name =  LeNomDeLaTAble

La propriété FieldNames : affiche les en-têtes de lignes/colonnes (titre des champs)
       .FieldNames = False / True  

La propriété FillAdjacentFormulas : True/ les formules à droite des tables rafraichies sont activées .
Voir la propriété WebSelectionType plus bas.
 .FillAdjacentFormulas = False / True   

La propriété RefreshOnFileOpen : True / Rafraichit les connections à l'ouverture du classeur. Pour activer cette propriété, il faut être certain que la connexion internet est disponible avant d'ouvrir le classeur. Un rafraichissement sans connexion génère une erreur. Ce qui est encore plus gênant c'est que cette erreur est répétée autant de fois qu'il y a de connections.
 .RefreshOnFileOpen = False / True

La propriété BackgroundQuery : True / Rafraichissement des connections en arrière-plan. Généralement employer quand des rafraichissements automatiques sont activés. Il est toujours possible d'actualiser une connexion en bloquant le mode en arrière-plan, voir la propriété Refresh.
       .BackgroundQuery = False / True 

La propriété AdjustColumnWidth : True / Ajuste la largeur des colonnes quand les données proviennent d'une table. Voir la propriété WebSelectionType plus bas.
 .AdjustColumnWidth = False / True

La propriété .RefreshPeriod : Actualisation automatique des connections exprimée en minute(s) le temps minimal est de 1 minute. La valeur zéro annule l'actualisation.
 .RefreshPeriod = 0  / 32767

La propriété WebSelectionType peu prendre trois valeurs différentes ;
        WebSelectionType .xlEntirePage : Retourne toute la page du site sélectionné        
        WebSelectionType :xlAllTables : Retourne tout les tableaux de la pages.        
        WebSelectionType .xlSpecifiedTables : Retourne la/les tableaux spécifiés par la propriété WebTables.
Cette propriété est exploitée dans la démo

WebSelectionType .WebTables = "2,3" : Retourne les tableaux deux et trois, les tableaux qui n'existent pas sont tout simplement ignorer. Ce qui permet de rapatrier les données dans un tableau structuré.
       .WebSelectionType .xlSpecifiedTables        
      .WebTables  = "2,3"

Exemple :


La propriété Refresh : Employée seule, elle actualise la connexion dans le mode définit par la propriété BackgroundQuery décrite ci-dessus.
 .Refresh

Si du code VBA est tributaire des résultats de l'actualisation il est possible de bloquer provisoirement l'actualisation en arrière-plan en ajoutant un paramètre supplémentaire.
       .Refresh BackgroundQuery:=False

La propriété Delete : Supprime définitivement la table et la connexion s'y afférent. Il est conseillé de supprimer une connexion qui a été établie pour une seule utilisation, cela évite de surcharger le classeur inutilement ce qui pourrait, à terme, perturber le fonctionnement du classeur.
      .Delete

A ce stade, le nom de la connexion a été défini par Excel, si c'est la première connexion ou que les précédentes ont été renommées, le nom par défaut est « Connexion », si ce nom existe déjà un nombre est ajouter.. Connexion1.. Connexion2.. etc. Il est donc évident qu'il vaut mieux renommer le nom des connexions au fur et à mesure de leur création pour que le nom par défaut ne change pas. A défaut de renommer les connexions il est pratiquement impossible de les retrouver ultérieurement, le nom attribuer à la nouvelle connexion est toujours « Connexion », c'est à la connexion précédente qu'un indice est ajouter.
Attribuer un nom à une connexion:
         
        ActiveWorkbook.Connections("Connexion").Name =   NomConnexion

Récapitulatif complet :


    With Sheets("NomFeuille")        
       With .QueryTables.Add(Connection:= _                                                         
                   "URL;http://LeSiteWebAatteindre.com",  Destination:=.Range(AdresseCellule))         
             .Name = Replace(Nom, "/", "")           'le nom de la QueryTable                                    .        
            .FieldNames = True                          'affiche les en-têtes de lignes/colonnes (titre des champs)         
            .RowNumbers = False        
            .FillAdjacentFormulas = True            'Active le recalcule des cellules à droite de la table.        
            .PreserveFormatting = False        
            .RefreshOnFileOpen = False              'Rafraichit la connexion quand ont ouvre le classeur        
            .BackgroundQuery = True                 'Rafraichissement en arrière-plan        
            .RefreshStyle = xlOverwriteCells        'pour ne pas effacer les lectures précédentes        
            .SavePassword = False        
            .SaveData = False        
            .AdjustColumnWidth = False        
            .RefreshPeriod = 0                      'Rafraichissement automatique minimum : 1 minute        
            .WebSelectionType = xlSpecifiedTables   'Pour sélectionner des données (pas toute la page)        
            .WebTables = "2,3"                      'Les tables (tableaux) à sélectionner        
            .WebFormatting = xlWebFormattingNone        
            .WebPreFormattedTextToColumns = True        
            .WebConsecutiveDelimitersAsOne = False        
            .WebSingleBlockTextImport = True        
            .WebDisableDateRecognition = False        
            .WebDisableRedirections = False        
            .Refresh BackgroundQuery:=False         'Rapatrie les données / en bloquant le programme        
            '.Delete                                'Pour un emploi unique de la connexion        
        End With        
End With        
        ActiveWorkbook.Connections("Connexion").Name =  NomConnexion

Pour modifier les paramètres de la connexion il faut intervenir au niveau de la QueryTables
Exemple pour modifier l 'actualisation automatique
        Sheets("NomFeuille").QueryTables(NomDeLaTable) .RefreshPeriod = 0              'Rafraichissement automatique annuler        
        Sheets("NomFeuille").QueryTables(NomDeLaTable) .RefreshPeriod = 5              'Rafraichissement automatique activer avec délais de 5 minutes.

Toutes les autres propriétés peuvent êtres modifiées de la même façon.
Exemple pour actualiser une connexion.
        ActiveWorkbook.Connexions("NomConnexion").Refresh

Cette méthode ne permet pas de modifier le paramètre BackgroundQuery, l'actualisation se fera donc dans le mode spécifié par la propriété de la QueryTables
Pour actualiser en modifiant provisoirement la propriété BackgroundQuery il faut employer la QueryTables
       Sheets("NomFeuille").QueryTables(NomDeLaTable) .Refresh BackgroundQuery:=False 



Téléchargement : Tuto Connexion Web.docx

Un classeur démo est également disponible.
Il traite plus particulièrement d'une connexion multiple sur le même site, en l'occurrence vingt connexions, mais pourrait tout aussi bien gérer ces connexions sur plusieurs sites. Elles peuvent être rafraîchies automatiquement, sans minuteur (Timer), uniquement en se servant des paramètres des connexions.
Il cible le rapatriement de données financières sur le site de Yahoo finance, connexion sélective sur le site français ou anglais suivant les paramètres d'Excel.



Téléchargement du classeur :Connexion Web V4.xlsm
Téléchargement de l'aide : Aide Connexion Web.docx

A voir également :

Ce document intitulé «  Tuto Excel - Connexions sur un site Web en temps réel  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.