Pour récupérer des informations sur une page Web, il faut l'ouvrir, lire les informations qu'elle contient,
puis refermer la page.
On peut ouvrir directement la page sous Excel (ou l'ouvrir en créant ou actualisant une requête), ou bien
l'ouvrir sous Internet Explorer (voire sous un autre navigateur),
ou encore utiliser directement les dll qui gèrent les manipulations d'Internet.
Si la page est sur le disque local (et non sur un serveur Internet), on peut également ouvrir la page comme un fichier
texte et aller y pêcher des informations.
1) En ouvrant la page Web sous Excel
Rien de plus simple que d'ouvrir une page Web sous Excel avec VBA :
Workbooks.Open ("http://boursorama.fr")
et pour nommer l'objet fichier ainsi ouvert :
Set fich = Workbooks.Open ("http://boursorama.fr")
Pour aller chercher l'information qui nous intéresse dans la page, tous les moyens sont bons :
- Si les données sont contenues dans des tableaux (TABLES html), on peut utiliser le nom que leur donne Excel :
TABLE, TABLE_2, TABLE_3,...
Si, dans la page d'accueil de Boursorama, la valeur du CAC40 est dans la 2ème ligne de la de la 3ème
colonne de la table TABLE_13,
valeurCAC = Range("TABLE_13").Cells(2, 3)
Une macro destinée à récupérer le dernier cours du CAC40 pourrait ainsi s'écrire :
Sub CAC40()
Set fich = Workbooks.Open("http://boursorama.fr")
MsgBox fich.Sheets(1).Range("TABLE_13").Cells(2, 3)
fich.Close (False) '(referme la page Web)
End Sub
- Une autre méthode consiste à rechercher dans la page Web, l'information en utilisant la méthode
Find. Cette méthode présente l'avantage de limiter le risque d'erreurs liées
aux changements de disposition de la page Web.
Dans l'exemple précédent, il suffit de remarquer que la valeur du CAC se trouve située à
droite du libellé "CAC 40", puis de rechercher "CAC 40" par VBA.
Sub CAC40()
Set fich = Workbooks.Open("http://boursorama.fr")
MsgBox fich.Sheets(1).Cells.Find("CAC 40", , , xlWhole).Offset(0, 1)
fich.Close (False) '(referme la page Web)
End Sub
2) Création ou mise à jour de requêtes
Pour insérer par VBA une requête Internet dans un fichier Excel, on utilise QueryTables.Add.
Si C:\MES DOCUMENTS\requ.iqy est le fichier texte de la requête,
Sheets(2).QueryTables.Add("FINDER;C:\MES DOCUMENTS\requ.iqy", Sheets(2).Range("B1"))
va insérer la requête dans la cellule B1 de la deuxième feuille.
Refresh sert à actualiser les données :
With Sheets(2).QueryTables.Add("FINDER;C:\MES DOCUMENTS\requ.iqy", Sheets(2).Range("B1"))
.Refresh
End With
On peut bien sûr créer également le texte de la requête par VBA (puis le détruire après
utilisation).
La macro dont le texte est présenté ci-après va créer dans la cellule AI de la feuille 2
une requête Internet pointant sur Boursorama.
Sub req()
Open "c:\requ.iqy" For Output As #1 'crée fichier texte
'inscrit le texte de la requête
Print #1, "WEB" & Chr(10) & "1" & Chr(10) & "http://www.boursorama.com/"
Close #1 'ferme le fichier texte
'insére la requête dans la page 2
Sheets(2).QueryTables.Add("FINDER;C:\requ.iqy", Sheets(2).Range("A1")).Refresh
Kill "c:\requ.iqy" 'supprime le fichier texte
End Sub
Une fois cette requête mise en place, on peut en actualiser le résultat par :
Sheets(2).QueryTables(1).Refresh.
3) Ouverture d'une page Web par Internet Explorer
VBA peut ouvrir un lien hypertexte existant dans une feuille Excel, ou bien ouvrir
directement une page Web à partir de son URL.
- Pour créer un lien hypertexte par VBA, on utilise Hyperlinks.Add
Pour créer un lien hypertexte vers Boursorama dans la deuxième cellule de la feuille active :
ActiveSheet.Hyperlinks.Add Cells(2), "http://boursorama.fr/"
Pour en insérere un en A5 dans la feuille 2 :
Sheets("Feuil2").Hyperlinks.Add Sheets("Feuil2").Range("A5"), "http://boursorama.fr/"
- Pour ouvrir un lien hypertexte, il suffit d'utiliser la méthode
Follow :
Cells(1).Hyperlinks(1).Follow va ouvrir le lien hypertexte de la cellule A1.
Sheets("feuil2").Cells.Hyperlinks(2).Follow va ouvrir le deuxième lien de la page "Feuil2"
Pour ouvrir le lien dans une nouvelle fenetre, il suffit d'ajouter "True" :
Sheets("feuil2").Cells.Hyperlinks(2).Follow True
- Pour ouvrir une URL quelconque, on utilise FollowHyperlink :
ActiveWorkbook.FollowHyperlink "http://boursorama.fr".
et dans une nouvelle fenêtre :
ActiveWorkbook.FollowHyperlink "http://boursorama.fr", , True.
On peut aussi utiliser la méthode Shell appliquée
à l'application Internet Explorer avec comme paramètre l'URL de la page :
Shell ("C:\Program Files\Internet Explorer\iexplore.exe http://boursorama.fr")
- pour récupérer des données dans la feuille Web active, on peut utiliser Sendkeys,
mais la technique est capricieuse et délicate à maîtriser.
Par exemple, CTRL+A ou ALT+E puis S pour sélectionner tout, puis CTRL+C pour copier dans le presse-papiers,
et coller dans une feuille Excel :
SendKeys ("^a^c") ou SendKeys "%(es)^c", True pour tout sélectionner puis copier,
SendKeys "%(ES)^c{BS}", True pour tout sélectionner puis copier, puis revenir sur le fichier Excel
({BS} est la touche "Retour").
Il est prudent de prévoir une temporisation avant de lancer Sendkeys, pour laisser
le temps à la page Web de s'afficher.
Utile aussi ALT+D pour sélectionner la zone d'adresses.
Remarque : la méthode peut parfois être utilisée pour renseigner un formulaire sur une page Web
(par exemple ouverture avec mot de passe). Utiliser Sendkeys({TAB}) pour passer d'une zone de saisie à l'autre.
4) En utilisant les dll (via fonctions API) :
La fonction API InternetOpenA de la dll Wininet (que nous avons rebaptisée OuvreInternet ci-après) permet d'ouvrir Internet
et de nommer le handle correspondant (InternetCloseHandle est utilisé de la même façon pour fermer Internet) :
internet = OuvreInternet("toto", 1, vbNullString, vbNullString, 0)
La fonction InternetOpenUrlA (renommée Ouvrepage) va, comme son nom l'indique, ouvrir une page Web (dont nous avons nommé
le handle "URL" :
URL = Ouvrepage(internet, page_Web_à_lire, vbNullString, ByVal 0&, &H80000000, ByVal 0&)
Enfin, InternetReadFile (renommée "code_page") permet de lire le contenu de la page Web (code source) :
code_page URL, texte_code, 1000, nb_caractéres_lus va lire et renvoyer dans la variable texte_code
les 1000 premiers caractères de la page "URL".
La macro qui suit effectue donc les actions suivantes :
- saisie de l'URL de la page Web à ouvrir par une boite de dialogue
- ouverture d'une session Internet
- ouverture de la page Web choisie
- lecture des 5000 premiers caractères de la page
- fermeture de la page
- fermeture de la session Internet
- renvoi des données par un message puis recopie dans un fichier texte enregistré sur le dicque dur local.
Private Declare Function OuvreInternet Lib "wininet" _
Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, _
ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function fermeInternet Lib "wininet" _
Alias "InternetCloseHandle" (ByVal hInet As Long) As Integer
Private Declare Function code_page Lib "wininet" _
Alias "InternetReadFile" (ByVal hFile As Long, ByVal sBuffer As String, _
ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Private Declare Function Ouvrepage Lib "wininet" _
Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, _
ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long
Sub lit_code_page_Web()
Dim texte_code As String * 1024
page_Web_à_lire = InputBox("URL de la page à lire ?", _
"code de la page Internet", "http://boursorama.fr")
internet = OuvreInternet("toto", 0, vbNullString, vbNullString, 0) 'ouvre Internet
URL = Ouvrepage(internet, page_Web_à_lire, vbNullString, _
0, &H400000 Or &H4000000 Or &H80000000, 0) 'ouvre la page Web
'lecture du code de la page par paquet de 1024 caractères
txt = ""
nb_caractères_lus = 1
Do While nb_caractères_lus > 0
'lit 1024 caractères et les copie dans texte_code
code_page URL, texte_code, 1024, nb_caractères_lus
txt = txt & Left(texte_code, nb_caractères_lus)
Loop
fermeInternet URL 'ferme la page
fermeInternet internet 'ferme Internet
Msgbox txt
'recopie dans un fichier texte
Open "c:\rien.txt" For Output As #1
Print #1, txt
Close #1
'ouverture du fichier téléchargé
ThisWorkbook.FollowHyperlink "c:\rien.txt", , True
End Sub
Une fois les données récupérées, il est facile de les traiter par VBA,
par exemple en utilisant la méthode Find pour rechercher des tableaux de
chiffres (TABLE) ou bien pour rechercher les liens (HREF)...
La technique est particulièrement bien adaptée à la répération de cours de bourse sur Internet (cliquez ici).
Cette méthode de téléchargement de fichier n'est pas limitée
aux pages web, rien n'empeche de l'utiliser pour télécharger une image, un fichier son, un fichier zip...
5) En pilotant Internet Explorer en VBA :
Méthode très commode, mais limitée aux version d'Internet Explorer les plus récentes.
Pour voir le vocabulaire VB pour IE, cliquez ici.
ATTENTION !
Dans Vista et Windows 7, il est indispensable de désactiver l'UAC
- soit manuellement : panneau de configuration / comptes et protection des utilisateurs / comptes d'utilisateurs / modifier les paramètres de controle de compte d'utilisateur / mettre le niveau de protection au minimum
- soit en modifiant les bases de registre (éventuellement par macro) : passer HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\EnableLUA à 0.
Par exemple, pour récupérer le code d'une page Web :
Set ie = CreateObject("internetexplorer.application")
ie.Navigate ("http://boursorama.fr")
Do While ie.Busy
application.wait (now+0.5/3600/24)
Loop
application.wait (now+1/3600/24)
Set dct = ie.Document
msgbox dct.getElementsByTagName("HTML").item(0).outerhtml
ie.quit
Ou pour recopier dans une page Excel une table html, par exemple quand on ne peut pas l'ouvrir directement sous Excel :
nom | note | classement |
Zaza | 5 | 3 |
Nathalie | 15 | 1 |
Toto | 12 | 2 |
Lulu | 3 | 4 |
Sub copie_table()
Set ie = CreateObject("internetexplorer.application")
'ouvrir la page contenant le tableau
ie.Navigate ("http://jacxl.free.fr/cours_xl/cours_xl_jac.html#ouvrir_web")
Do While ie.Busy
Application.Wait Now + 0.1 / 3600 / 24
Loop
Application.Wait Now + 2 / 3600 / 24
ie.Visible = True
Set dct = ie.document.Parentwindow.frames.Item(1).frames.Item(1).document
'(le tableau est dans le deuxième frame du frame inférieur)
txt = dct.getElementsByTagName("table").Item(0).outerhtml
'txt est le code html du premier tableau de la page
ie.Quit
Set MyData = New DataObject
MyData.SetText txt
MyData.PutInClipboard
'le code du tableau est copié dans le presse-papiers
Set fich = Workbooks.Add
Set pag = fich.Sheets(1)
pag.Paste
End Sub
(Remarque : Paste donne parfois des résultats surprenants, notamment quand on colle des dates. Il est parfois utile d'utiliser SendKeys "^v" au lieu de Paste).
Voir aussi l'exemple "votre meilleur ami à la une de son journal favori (VBS)
" .