Méthodes diverses d'utilisation des requêtes Web dans Microsoft Office Excel 2003

  • Article

Frank Rice
Microsoft Corporation

S'applique à :
    Microsoft® Office Excel 2003

Résumé : Apprenez les différentes manières d'utiliser des requêtes Web pour récupérer les données d'une page Web. Pour ce faire, vous devrez utiliser des requêtes statiques et dynamiques, ainsi que les méthodes Add et Find. L'expérimentation de ces méthodes vous permettra d'utiliser les données des pages Web dans vos propres applications.

Sommaire

Introduction
À propos des requêtes Web
Récupération de données à partir d'URL
Récupération de données à l'aide des connexions FINDER
Informations complémentaires sur les méthodes de récupération des données
Récupération de données à partir d'une table unique
Conclusion

Introduction

Les pages Web contiennent souvent des informations parfaites pour l'analyse dans Microsoft® Office Excel. Vous pouvez, par exemple, analyser des cotations boursières dans Excel en utilisant les informations issues directement d'une page Web. En fonction de vos besoins, vous pouvez récupérer des données actualisables (c'est-à-dire que vous pouvez mettre à jour dans Excel avec les données les plus récentes de la page Web), mais vous pouvez également récupérer les données d'une page Web sous forme de données statiques et les utiliser dans une feuille de calcul. Cet article illustre différentes méthodes de récupération des données à partir de pages Web afin de vous aider à mieux comprendre comment tout cela fonctionne.

À propos des requêtes Web

Vous pouvez créer ou lancer une requête Web pour récupérer le texte ou les données d'une page Web. Les requêtes Web sont particulièrement pratiques pour récupérer des données se trouvant dans des tableaux ou des zones préformatées. (Les tableaux sont définis avec la balise HTML [TABLE] ; les zones préformatées quant à elles sont souvent définies avec la balise HTML [PRE].) Les données récupérées n'incluent ni les images ni le contenu des scripts.

Important Si vous récupérez des données à partir d'un site Web, il est de votre devoir de vous assurer que vous les utilisez conformément aux conditions générales applicables qui régissent l'utilisation des données définies par le propriétaire et/ou l'opérateur d'un tel site Web.

Une requête Web est une fonction d'Excel vous permettant de récupérer des données stockées sur un intranet ou sur Internet. Une requête Web peut utiliser des paramètres statiques, des paramètres dynamiques ou une combinaison des deux. Les requêtes utilisant des paramètres statiques envoient une requête sans aucune entrée de l'utilisateur ; les requêtes utilisant des paramètres dynamiques vous invitent à spécifier une entrée. Quel que soit le type des paramètres de la requête, les informations requises sont extraites d'un site Internet ou intranet et les résultats sont intégrés dans une feuille de calcul.

Récupération de données à partir d'URL

Pour exécuter une requête Web à l'aide d'un programme, vous pouvez utiliser la méthode Add de la collection QueryTables. Si vous utilisez la méthode Add, elle renvoie un objet QueryTable représentant la nouvelle table de requêtes. La syntaxe de la méthode Add est la suivante :

<expression>.Add(Connection, Destination, Sql)

où < expression > est obligatoire et renvoie un objet QueryTable. L'argument Connection est également obligatoire et constitue la source de données de la table de requêtes. Dans le cadre des requêtes Web, vous pouvez utiliser un des deux types de chaînes Connection  : adresse Web (URL) ou FINDER. Lorsque vous spécifiez le type d'URL, vous utilisez l'URL du serveur sur lequel vous exécutez la requête. Vous pouvez copier la troisième ligne de n'importe quel fichier de requête Web [.iqy] et l'utiliser comme URL. Lorsque vous spécifiez le type FINDER, vous utilisez le chemin complet vers un fichier de requête Web existant (.iqy).

Remarque    Destination est un argument obligatoire et est la cellule située en haut à gauche de l'objet QueryTable. L'argument Sql est facultatif et n'est pas utilisé dans les requêtes Web.

Utilisation d'une requête Web statique

L'exemple suivant utilise des paramètres statiques ; dans ce cas, Excel ne vous invite pas à spécifier les valeurs à envoyer au serveur. Les paramètres sont en effet contenus dans l'URL. Pour créer cette macro, exécutez les étapes suivantes :

  1. Créez un classeur.

  2. Lancez Visual Basic Editor (appuyez sur ALT+F11).

  3. Dans le menu Insertion, cliquez sur Module.

  4. Dans le module, saisissez le code suivant :

    Sub URL_Static_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://quote.money.cnn.com/quote/quote?symbols=msft", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

  5. Passez à Feuil1 dans le classeur.

  6. Pour lancer la macro URL_Static_Query, pointez sur Macro dans le menu Outils, puis cliquez sur Macros.

  7. Dans la boîte de dialogue Macro, cliquez sur la macro URL_Static_Query, puis sur Exécuter.

    La cotation boursière de MSFT est intégrée dans Feuil1.

    odc_xlwebquery01.gif

    Figure 1. Cotation boursière pour l'action MSFT

Utilisation d'une requête Web dynamique

L'exemple suivant utilise des paramètres dynamiques ; Excel vous invite à spécifier les valeurs à envoyer au serveur. Cet exemple utilise la méthode GET, car les paramètres sont contenus dans la requête. Pour créer la macro, exécutez les étapes suivantes :

  1. Basculez dans Visual Basic Editor et entrez la procédure suivante dans la feuille du module :

    Remarque   L'exemple suivant inclut un saut de ligne supplémentaire afin de faciliter l'affichage en ligne. Pour utiliser cet exemple, supprimez le saut de ligne supplémentaire à la fin de la troisième ligne.

    Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://quote.money.cnn.com/quote/quote?symbols=[""QUOTE"",
""Enter stock symbols separated by commas.""]", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

  2. Passez à Feuil2 dans le classeur et lancez la macro URL_Get_Query. Étant donné que cet exemple utilise des paramètres dynamiques, vous êtes invité à spécifier les valeurs de ces derniers.

  3. Dans la boîte de dialogue Entrer une valeur de paramètre, saisissez un nom d'action valide. Par exemple, tapez msft.

  4. Cliquez sur OK.

    La cotation boursière de l'action que vous avez saisie est intégrée dans Feuil2.

Récupération de données à l'aide de connexions FINDER

Utilisation d'une requête dynamique

Avec le type de connexion FINDER, vous spécifiez le chemin complet vers un fichier de requête Web existant (.iqy). Cet exemple utilise la méthode GET parce que le fichier de la requête Web, MSN MoneyCentral Investor Currency Rates.iqy, utilise cette méthode. Ceci signifie que les paramètres et l'URL du serveur se trouvent sur la troisième ligne du fichier.

Remarque   Les fichiers .iqy utilisés dans les procédures de cet article se trouvent dans C:\Program Files\Microsoft Office\OFFICE11\QUERIES en cas d'installation complète de Microsoft Office 2003.

Pour créer la macro, exécutez les étapes suivantes :

  1. Basculez dans Visual Basic Editor et saisissez la macro suivante dans la feuille du module :

    Sub Finder_Get_Query()
'Edit path to .iqy file, if necessary.
IQYFile = "C:\Program Files\Microsoft Office\OFFICE11\" &amp; _
"QUERIES\MSN MoneyCentral Investor Currency Rates.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" &amp; IQYFile, Destination:=Range("A1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

  2. Passez à Feuil3 dans le classeur et lancez la macro Finder_Get_Query.

    Une liste des taux des devises est intégrée dans Feuil3.

Utilisation de la requête dynamique

Avec le type de connexion FINDER, spécifiez le chemin complet vers un fichier de requête Web existant (.iqy). Cet exemple utilise la méthode GET parce que le fichier de la requête Web, MSN MoneyCentral Investor Stock Quotes.iqy, utilise cette méthode. Ceci signifie que les paramètres se trouvent sur la quatrième ligne et l'URL du serveur sur la troisième ligne du fichier. Pour créer la macro, exécutez les étapes suivantes :

  1. Basculez dans Visual Basic Editor et entrez la procédure suivante dans la feuille du module :

    Sub Finder_Get_Query()
' Edit path to .iqy file, if necessary. 
IQYFile = "C:\Program Files\Microsoft Office\OFFICE11\" &amp; _
"QUERIES\MSN MoneyCentral Investor Stock Quotes.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" &amp; IQYFile, Destination:=Range("A1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

  2. Passez à Feuil4 et lancez la macro Finder_Get_Query. Étant donné que cet exemple utilise des paramètres dynamiques, vous êtes invité à spécifier les valeurs de ces derniers.

  3. Dans la boîte de dialogue Entrer une valeur de paramètre, saisissez un nom d'action valide. Par exemple, tapez msft.

  4. Cliquez sur OK.

    La cotation boursière de l'action que vous avez saisie est renvoyée dans un tableau de Feuil4.

Informations complémentaires sur les méthodes de récupération des données

Les sections suivantes fournissent de plus amples informations sur les différentes méthodes de récupération des données à partir de pages Web. Vous pouvez, par exemple, récupérer les taux de change à partir du site x-rates.com (en anglais).

odc_xlwebquery02.gif

Figure 2. Tableau des taux de change des devises

Récupération de données à l'aide de la méthode Find

La procédure suivante ouvre une page Web dans son intégralité. La procédure analyse ensuite la page et recherche les informations souhaitées, ici le taux de change dollar canadien/dollar américain.

  1. Tapez la macro suivante dans la feuille du module :

    Sub OpenUSDRatesPage()
Dim objBK As Workbook
Dim objRng As Range

'Open the page as a workbook.
Set objBK = Workbooks.Open("http://www.x-rates.com/tables/USD.HTML")

'Find the Canadian Dollar cell.
Set objRng = objBK.Worksheets(1).Cells.Find("Canadian Dollar")

'Retrieve the exchange rate.
MsgBox "The CAD/USD exchange rate is " &amp; objRng.Offset(-6, -1).Value
End Sub

  2. Passez à Feuil5 et lancez la macro OpenUSDRatesPage. La boîte de dialogue suivante s'affiche :

    odc_xlwebquery03.gif

    Figure 3. Boîte de dialogue indiquant le taux de change dollar canadien/dollar américain

    Remarque Le gros inconvénient de cette méthode de récupération des données est qu'elle charge l'intégralité de la page Web en mémoire, y compris les graphiques qu'elle peut contenir. Ces informations superflues peuvent ainsi considérablement ralentir la récupération des données.

Récupération de données à partir d'une table unique

Contrairement à la méthode précédente, la requête Web suivante vous permet de récupérer des données à partir d'une table unique. En outre, cette requête Web offre une option permettant d'actualiser les données chaque fois qu'un utilisateur ouvre le classeur ou l'actualise à intervalles réguliers.

La procédure suivante récupère un tableau des taux de change grâce à l'exécution une requête Web :

Sub RatesWebQuery()
Dim objBK As Workbook
Dim objQT As QueryTable

Dim strDecimal As String
Dim strThousand As String
Dim boolUseSystem As Boolean

'Create a new workbook.
Set objBK = Workbooks.Add

'Create query table to hold the rates.
With objBK.Worksheets(1)
Set objQT = .QueryTables.Add( _
Connection:="URL;http://www.x-rates.com/tables/USD.HTML", _
Destination:=.Range("A1"))
End With

'Set QueryTable properties.
With objQT
.Name = "USD"

'Don't recognize dates.
.WebDisableDateRecognition = True

'Don't refresh query when file opened.
.RefreshOnFileOpen = False

'Ignore page formatting.
.WebFormatting = xlWebFormattingNone

'Wait for query to finish before continuing.
.BackgroundQuery = True

'Select a specific table.
.WebSelectionType = xlSpecifiedTables

'Import the table containing the exchange rates.
.WebTables = "15"

'Save the query with workbook.
.SaveData = True

'Adjust columns to fit the data.
.AdjustColumnWidth = True
End With

With Application
'Store number format settings.
strDecimal = .DecimalSeparator
strThousand = .ThousandsSeparator
boolUseSystem = .UseSystemSeparators

'Set XL separators to match the Web site.
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = True

'Ingore any errors.
On Error Resume Next

'Execute query and wait for it to finish.
objQT.Refresh BackgroundQuery:=False

'Reset number format settings.
.DecimalSeparator = strDecimal
.ThousandsSeparator = strThousand
.UseSystemSeparators = boolUserSystem
End With

End Sub

Excel récupère le tableau suivant.

odc_xlwebquery04.gif

Figure 4. Tableau des taux de change

Notez l'utilisation des propriétés Application.DecimalSeparator, Application.ThousandsSeparator et Application.UseSystemSeparators dans la procédure. Dans les versions antérieures à Excel 2002, la méthode d'identification des nombres d'une page par Excel posait des problèmes. En effet, dans un grand nombre de pays européens, le point est utilisé comme séparateur des milliers, ce qui donnait des taux de change bien trop élevés lorsque Excel interprétait les données. Excel utilisait les Paramètres régionaux de Microsoft Windows® lorsqu'il essayait de reconnaître des nombres sur une page.

Dans Excel 2002 et les versions ultérieures, trois propriétés ont été ajoutées à l'objet Application afin de contourner temporairement les paramètres utilisés pour la reconnaissance des nombres :

  • Application.DecimalSeparator : caractère utilisé pour le séparateur décimal.
  • Application.ThousandsSeparator : caractère utilisé pour le séparateur des milliers.
  • Application.UseSystemSeparators : caractère spécifiant l'utilisation du séparateur de Windows ou d'Excel.

Conclusion

Cet article explique les différentes méthodes de récupération des données à partir d'une page ou d'un fichier Web. L'utilisation de la méthode Find permet de récupérer une page complète de données. La méthode Add, quant à elle, permet d'utiliser une requête Web pour extraire des données spécifiques à partir d'une page Web. Cet article traite également des diverses propriétés permettant de traiter des données numériques provenant d'une requête Web. En utilisant la méthode ou la propriété correspondant à vos besoins, vos applications gagneront en efficacité.

 

 

Dernière mise à jour le lundi 24 mai 2004