Il existe plusieurs façons de faire en sorte quExcel actualise automatiquement les connexions de données et recalcule une feuille de calcul. Ces astuces sont plus importantes avec le type de données Stock dans Excel pour les clients Microsoft 365.
Normalement, Excel se met à jour lorsque vous changez une valeur de cellule. De nos jours, il existe des situations dans lesquelles les cellules changent de valeur, mais Excel ne met PAS à jour la feuille de calcul. En dautres termes, Excel moderne a changé dune manière que Microsoft ne sest pas encore totalement adaptée.
Idéalement, Excel aurait un paramètre global pour actualiser la feuille de calcul toutes les n secondes ou minutes. Dans létat actuel des choses, nous avons besoin de solutions de contournement pour y parvenir.
Voici quelques situations dans lesquelles forcer lactualisation ou le recalcul des données peut être nécessaire ou prudent.
Type de données boursières
Avec le type de données boursières, la possibilité de se mettre à jour automatiquement est plus importante. Les utilisateurs voudront que leurs feuilles de calcul saisissent la lat est automatiquement les prix, ce que les versions d’aperçu actuelles ne peuvent pas faire. Au lieu davoir un joli « ticker » automatique, nous sommes censés cliquer sur « Actualiser » pour obtenir les derniers prix.
Les types de données Stock et Geo sont des bêtes curieuses. Ce sont des connexions de données à des sources externes mais napparaissent PAS en tant que connexions de données Excel. Cela signifie que vous ne pouvez pas configurer une actualisation automatique des données, comme vous le feriez avec des connexions de données normales. En fait, il ny a pas de contrôles exposés pour les types de données Stock ou Geo.
NOW () et dautres fonctions volatiles
La fonction NOW () se met à jour à la dernière date et heure chaque fois quExcel recalcule la feuille de calcul. Mais sil ny a rien pour y arriver, Now () ne change pas de valeur. Un facteur externe est nécessaire pour mettre à jour Excel maintenant () et le reste de la feuille de calcul. En dautres termes, vous devriez pouvoir jeter un coup dœil sur une feuille de calcul et savoir quelle est à la seconde, mais ce nest pas possible avec Excel «prêt à lemploi».
Les fonctions personnalisées VBA peuvent également être marquées comme volatiles en utilisant cette ligne dans le code de la fonction:
Application.Volatile
Cette ligne permet à la fonction de sexécuter à tout moment quExcel met à jour / recalcule la feuille de calcul.
Attention supplémentaire
Peut-être voulez-vous que votre feuille de calcul se mette à jour automatiquement par précaution? Beaucoup danciennes mains Excel se souviennent de situations où Excel na pas été correctement mis à jour, elles aiment donc lapproche «ceinture et accolades» (au moins occasionnellement).
Code
La méthode standard de forçage automatique La mise à jour dExcel est un court extrait de code VBA. Voici ce que nous utilisons, il existe de nombreuses variantes sur le même thème. Le code complet se trouve au bas de larticle.
Il y a trois fonctions.
RefreshAllDataConn
effectue lactualisation réelle des connexions de données (Workbooks (ThisWorkbook.Name) .RefreshAll) et nous avons ajouté deux lignes facultatives pour afficher la dernière actualisation dans la barre détat inférieure.
Si vous voulez être très prudent, ajoutez une ligne pour forcer explicitement le recalcul. Soit ActiveSheet.Calculate, soit lextrême Application.CalculateFull (cela ralentirait une grande feuille de calcul, à utiliser avec parcimonie).
AutoRefresh
exécutez le sous RefreshAllDataConn toutes les minutes ou quelle que soit la valeur que vous définissez la ligne Application.OnTime Now + TimeValue (« 00:01:00 »), « AutoRefresh »
Workbook_Open
une fonction Excel intégrée qui sexécute automatiquement lorsque la feuille de calcul est ouverte . Dans ce cas, il démarre AutoRefresh.
Solution de contournement de connexion de données
Linconvénient de lapproche VBA est quune feuille de calcul .xlsm est nécessaire (feuille de calcul Excel activée par macro). Il peut y avoir des problèmes de partage de fichiers activés pour les macros en raison de problèmes de sécurité.
Larrivée de PowerQuery / Get et Transform signifie quil existe un autre moyen de forcer le recalcul dune feuille de calcul. Cest une solution de contournement et pas parfaite, mais cest possible et na pas besoin dune feuille de calcul prenant en charge les macros.
En bref, assurez-vous quil existe une configuration de requête de données avec actualisation automatique. Sil ny a pas de connexion de données, ajoutez-en une petite à la feuille de calcul.
Une fois que vous avez une requête dactualisation automatique, la feuille de calcul comprenant toutes les fonctions volatiles devrait également être actualisée.
Idéalement, les types de données Stock et Geo devraient également être actualisés.
Toute requête de données Excel est livrée avec des options dactualisation dans les propriétés de la requête. La plupart dentre eux sont désactivés par défaut.
Actualiser toutes les nnn minutes – par défaut désactivé avec 60 minutes suggérées.
Actualiser les données lors de louverture du fichier
Activer lactualisation en arrière-plan
Actualiser cette connexion sur Actualiser tout
La solution de contournement de lactualisation automatique consiste à créer un petit et pratiquement insignifiant Connexion de données. Configurez ensuite cette connexion de données pour quelle se mette à jour toutes les minutes ou à lheure que vous souhaitez. Cela devrait forcer la feuille de calcul à se mettre à jour, y compris les fonctions volatiles mentionnées ci-dessus.
Certaines versions de cette solution de contournement ajoutent un lien vers un petit fichier csv sur le même ordinateur. Nous obtenons le même résultat en utilisant une connexion de données à partir dun tableau de la feuille de calcul.
Créez un petit tableau avec une seule cellule. La cellule peut contenir nimporte quoi, mais nous créons une cellule avec NOW (), pour des raisons que nous expliquerons plus tard.
Sélectionnez le tableau puis choisissez Données | Obtenir des données | Dautres sources | à partir de la table / plage. Lélément de menu exact peut différer selon votre version dExcel.
Lorsque léditeur de requête souvre, fermez-le et chargez-le . Dans le volet des connexions de données, vous verrez une requête.
Cliquez avec le bouton droit sur la requête, choisissez les propriétés pour voir le paramètres dont nous avons besoin.
Définissez le taux de rafraîchissement qui vous convient.
Pour être bien rangé, nous déplacez la table source (à droite) sur la même feuille à la requête chargée (à gauche). Comme nous avons utilisé NOW () dans la cellule du tableau source, il sera facile de voir quand / si la feuille de calcul a été actualisée.
Exemple de code VBA
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
Créer automatiquement une liste de feuilles de calcul Excel ou une table des matières
Solution complète dExcel NetworkDays () avec des vacances & vacances