[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 :
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.
Téléchargement du classeur :Connexion Web V4.xlsm
Téléchargement de l'aide : Aide Connexion Web.docx