Microsoft 365 comment "ralentir" une macro

[Eric 888]

Bonjour

J'ai développé un outil permettant de calculer le bilan carbone d'un événement à partir des codes postaux des participants
Pour ça, je requête le service en ligne de calcul d'itinéraires du Géoportail, qui me donne la distance routière entre deux codes postaux
Ce qui marchait très bien... jusqu'à ce que je me retrouve bloqué par une limite du Géoportail: pas plus de 5 requêtes par seconde

=>comment faire en sorte de limiter le nombre d'exécution d'une boucle à 5 itérations par secondes ?

la boucle que je souhaite contrôler est la suivante:

For i = 2 To lg 
'lg est la ligne de la dernière URL stockée dans ma feuille Excel
'les URL calculées à partir des codes postaux permettent de requêter le Géoportail
      
        Url = .Range("BP" & i).Value 'pour chaque URL
        With CreateObject("WINHTTP.WinHTTPRequest.5.1")
            .Open "GET", Url, False
            .send
            Txt = .responseText 
'réponse du Géoportail: suite de coordonnées GPS et de valeurs distance et temps annoncées par une suite de caractères normalisée
        End With
        .Range("BQ" & i).Value = Split(Split(Txt, """distance"":")(1), ",""duration""")(0) 
'découpage de la réponse du Géoportail pour récupérer la distance du trajet calculé par le Géoportail
'la valeur que je cherche est encadrée par les chaînes de caractères "distance": et ",duration"
    Next i

si le Géoportail détecte plus de 5 requêtes pendant 1 seconde à un moment ou un autre de l'exécution de la boucle, il renvoie un message d'erreur au lieu du code attendu, ce qui forcément plante la macro

d'où ma question: comment faire en sorte de limiter à 5 par seconde le nombre d'exécutions d'une boucle?

j'ai bien pensé à mettre en place un timer:

start = timer avant i-ème exécution
end = timer après i-ème exécution
si end - start < 1/5 seconde alors "attendre un peu" sinon exécution suivante

j'ai deux problèmes que je n'arrive pas à résoudre:
- "attendre un peu" => je ne trouve que l'instruction "wait" pour faire ça, mais pas d'intervalle en-dessous de la seconde => trop long, délai potentiel pour une 100aine de requêtes trop long pour l'utilisateur
- moins problématique mais quand même: pas optimal de limiter une exécution à 1/5e de seconde au lieu de 5 exécutions à 1 seconde (chaque requête peut prendre un temps variable, donc si une requête prend plus d'1/5e de seconde on se retrouve à ralentir inutilement les 4 requêtes suivantes

merci d'avance pour vos idées / solutions

Eric

 

[Gégé-45550]

Bonjour

J'ai développé un outil permettant de calculer le bilan carbone d'un événement à partir des codes postaux des participants
Pour ça, je requête le service en ligne de calcul d'itinéraires du Géoportail, qui me donne la distance routière entre deux codes postaux
Ce qui marchait très bien... jusqu'à ce que je me retrouve bloqué par une limite du Géoportail: pas plus de 5 requêtes par seconde

=>comment faire en sorte de limiter le nombre d'exécution d'une boucle à 5 itérations par secondes ?

la boucle que je souhaite contrôler est la suivante:

For i = 2 To lg
'lg est la ligne de la dernière URL stockée dans ma feuille Excel
'les URL calculées à partir des codes postaux permettent de requêter le Géoportail
    
        Url = .Range("BP" & i).Value 'pour chaque URL
        With CreateObject("WINHTTP.WinHTTPRequest.5.1")
            .Open "GET", Url, False
            .send
            Txt = .responseText
'réponse du Géoportail: suite de coordonnées GPS et de valeurs distance et temps annoncées par une suite de caractères normalisée
        End With
        .Range("BQ" & i).Value = Split(Split(Txt, """distance"":")(1), ",""duration""")(0)
'découpage de la réponse du Géoportail pour récupérer la distance du trajet calculé par le Géoportail
'la valeur que je cherche est encadrée par les chaînes de caractères "distance": et ",duration"
    Next i

si le Géoportail détecte plus de 5 requêtes pendant 1 seconde à un moment ou un autre de l'exécution de la boucle, il renvoie un message d'erreur au lieu du code attendu, ce qui forcément plante la macro

d'où ma question: comment faire en sorte de limiter à 5 par seconde le nombre d'exécutions d'une boucle?

j'ai bien pensé à mettre en place un timer:

start = timer avant i-ème exécution
end = timer après i-ème exécution
si end - start < 1/5 seconde alors "attendre un peu" sinon exécution suivante

j'ai deux problèmes que je n'arrive pas à résoudre:
- "attendre un peu" => je ne trouve que l'instruction "wait" pour faire ça, mais pas d'intervalle en-dessous de la seconde => trop long, délai potentiel pour une 100aine de requêtes trop long pour l'utilisateur
- moins problématique mais quand même: pas optimal de limiter une exécution à 1/5e de seconde au lieu de 5 exécutions à 1 seconde (chaque requête peut prendre un temps variable, donc si une requête prend plus d'1/5e de seconde on se retrouve à ralentir inutilement les 4 requêtes suivantes

merci d'avance pour vos idées / solutions

Eric

Bonjour,
Pourquoi ne pas compter le 'end' tous les 5 boucles puis, si 'end' - 'start' < 1 seconde, alors :

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1 - (end-start)
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Cordialement,

 

[dysorthographie]

Bonjour,

Dim i As Integer
For i = 2 To lg
    If i Mod 5 = 0 Then
        Application.Wait Now + TimeValue("00:00:01")
    End If
Next i

Mais Gégé a déjà fait cette proposition

 

[fanch55]

si le Géoportail détecte plus de 5 requêtes pendant 1 seconde à un moment ou un autre de l'exécution de la boucle, il renvoie un message d'erreur au lieu du code attendu, ce qui forcément plante la macro

Bonjour, le message d'erreur est-il explicite ?

 

[Eric 888]

Bonjour, le message d'erreur est-il explicite ?

hello, oui le msg est explicite
est-ce que l'idée derrière la question serait de détecter un dépassement d'allocation de requêtes via ce msg d'erreur et d'utiliser ce déclencheur pour mettre en pause et reprendre ensuite à partir de l'URL ayant généré l'erreur?
j'y ai pensé mais en pratique j'ai trouvé que cela compliquait les choses au lieu de les simplifier

 

[fanch55]

hello, oui le msg est explicite
est-ce que l'idée derrière la question serait de détecter un dépassement d'allocation de requêtes via ce msg d'erreur et d'utiliser ce déclencheur pour mettre en pause et reprendre ensuite à partir de l'URL ayant généré l'erreur?
j'y ai pensé mais en pratique j'ai trouvé que cela compliquait les choses au lieu de les simplifier

Effectivement, c'était l'idée.
Si vous pouviez donner un exemple d'URL, je testerai une salve de connexion et un processus consécutif.

 

[jurassic pork]

Hello,
sinon il y a d'autres possibilités pour récupérer les distances entre 2 adresses , par exemple l'API google DistanceMatrix. C'est ce que j'utilise mais il faut une ApiKey et pour que cela reste gratuit il ne faut pas dépasser un certain nombre de requêtes par mois (environ 30000). Pour éviter de payer quelque chose on peut utiliser le système de quota . par exemple moi j'ai mis :
max 200 requêtes par minute et 1000 par jour comme cela je ne dépasse pas le nb de requêtes par mois.
Je n'ai jamais rien eu à payer.
Le souci c'est que c'est compliqué pour s'inscrire et de comprendre le fonctionnement.
L'avantage, c'est que l'on peut mettre des adresses complètes car avec les codes postaux il peut y avoir des imprécisions ou des erreurs.

Mais pour toi le jeu n'en vaut certainement pas la chandelle si ton opération n'est que ponctuelle et que tu n'as pas besoin de précision dans les distances.
Ami calmant, J.P

 

[Rheeem]

- moins problématique mais quand même: pas optimal de limiter une exécution à 1/5e de seconde au lieu de 5 exécutions à 1 seconde (chaque requête peut prendre un temps variable, donc si une requête prend plus d'1/5e de seconde on se retrouve à ralentir inutilement les 4 requêtes suivantes

WinHTTPRequest peut fonctionner en asynchrone , le dernier paramètre de Open, donc elle n'attend pas la fin du téléchargement pour continuer l’exécution , alors il est possible de lancer 5 de ces objets a des intervalles bien précis et aspes vérifier l'avancement de téléchargements dans la meme boucle.

 

[Eric 888]

aucune des options proposées ne fonctionne car il ne s'agit pas de détecter des blocs de 5 consécutifs mais un bloc de 5 "glissant"
je me suis résolu à "ralentir" chaque requête, en l'obligeant à durer au moins un 5e de seconde et en utilisant la fonction Sleep

 

[Eric 888]

Hello,
sinon il y a d'autres possibilités pour récupérer les distances entre 2 adresses , par exemple l'API google DistanceMatrix. C'est ce que j'utilise mais il faut une ApiKey et pour que cela reste gratuit il ne faut pas dépasser un certain nombre de requêtes par mois (environ 30000). Pour éviter de payer quelque chose on peut utiliser le système de quota . par exemple moi j'ai mis :
max 200 requêtes par minute et 1000 par jour comme cela je ne dépasse pas le nb de requêtes par mois.
Je n'ai jamais rien eu à payer.
Le souci c'est que c'est compliqué pour s'inscrire et de comprendre le fonctionnement.
L'avantage, c'est que l'on peut mettre des adresses complètes car avec les codes postaux il peut y avoir des imprécisions ou des erreurs.
Regarde la pièce jointe 1213765
Mais pour toi le jeu n'en vaut certainement pas la chandelle si ton opération n'est que ponctuelle et que tu n'as pas besoin de précision dans les distances.
Ami calmant, J.P

hello, merci pour ta réponse
j'avais en effet étudié DistanceMatrix mais un peu usine à gaz et dans ma boîte on est pas trop orienté services Google donc pour choper une clé API "officielle"...
au regard de mon besoin, ponctuel, c'est en effet excessif
de plus, la précision à l'adresse ne m'est pas nécessaire, et même me poserait problème: les données sont issues de formulaires d'inscription, donc pas normalisées, pleine de typos, etc. - au moins avec les codes postaux je peux mettre au format et évacuer les valeurs incorrectes