[TUTO] Connexions sur un site Web en temps réel

[TUTO] Connexions sur un site Web en temps réel

Auteur : Lermite

Date : 21 novembre 2012

Ce tuto se consacre plutôt aux connexions sur site web.

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 peu 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 à é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éconseiller 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 connexions.

    .RefreshOnFileOpen = False / True

La propriété BackgroundQuery : True / Rafraichissement des connexions 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 connexions 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’existes pas sont tout simplement ignorer. Ce qui permet de rapatrier les données dans un tableau structurer.

    .WebSelectionType 
    .WebTables  = "2,3"

Exemple :

1

La propriété Refresh : Employée seule 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 y afférente. Il est conseiller 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 connections au fur et à mesure de leur création pour que le nom par défaut ne change pas. A défaut de renommer les connections 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.Connections(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 

Un classeur démo est également disponible.

Il traite plus particulièrement 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 êtres rafraichies automatiquement, sans 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.

general

Téléchargement du classeur :Connexion Web V4.xlsm

Téléchargement de l'aide : Aide Connexion Web.docx

Rechercher des sujets similaires à "tuto connexions site web temps reel"