Comment Automatiser des données Refresh Schedules dans Excel - Guide Pratique

Apprenez à automatiser les planifications d'actualisation des données dans Excel en utilisant Power Query, les macros VBA et les connexions cloud. Ce didacticiel avancé couvre la planification des mises à jour automatiques à partir de sources de données externes.

Pourquoi c'est important

L'automatisation de l'actualisation des données économise des heures de travail manuel et garantit l'accès aux informations actuelles. C'est essentiel pour la prise de décision basée sur les données.

Prérequis

•Compétences intermédiaires en Excel (formules, filtrage, tableaux croisés dynamiques)
•Compréhension des sources de données (SQL, API, fichiers CSV, bases de données)
•Connaissances de base en VBA ou volonté d'utiliser l'interface graphique Power Query
•Excel 365, Excel 2019 ou Excel 2016 avec complément Power Query

Instructions étape par étape

Connecter à une Source de Données Externe

Accédez à Données > Obtenir les données > À partir d'une base de données/web/fichier, sélectionnez votre source de données et établissez les paramètres de connexion. Vérifiez l'aperçu des données avant le chargement.

Configurer la Transformation Power Query

Dans l'éditeur Power Query (Données > Modifier les requêtes), appliquez les transformations nécessaires comme le filtrage et le tri. Cliquez sur 'Fermer et charger' pour charger les données dans votre feuille de calcul.

Définir l'Actualisation Automatique dans Excel

Cliquez avec le bouton droit sur la table chargée > Options de requête > Actualiser > cochez 'Actualiser les données à l'ouverture du fichier' et définissez l'intervalle d'actualisation.

Planifier l'Actualisation à l'Aide d'une Macro VBA

Appuyez sur Alt+F11 pour ouvrir l'éditeur VBA, créez un nouveau module et écrivez une macro avec Application.OnTime pour planifier l'actualisation à des intervalles spécifiques.

Activer l'Actualisation en Arrière-Plan et la Gestion des Erreurs

Configurez Données > Requêtes et connexions > cliquez avec le bouton droit sur requête > Propriétés > cochez 'Activer l'actualisation en arrière-plan' et ajoutez des notifications d'erreur. Enregistrez le fichier en tant que .xlsm.

Méthodes alternatives

Utiliser Microsoft Power Automate (Cloud)

Créez des flux cloud via Power Automate pour actualiser les classeurs Excel sur OneDrive/SharePoint selon un calendrier prédéfini sans garder le fichier ouvert.

Utiliser les Compléments Excel (Ablebits, Exceljet)

Les compléments tiers offrent des interfaces de planification intuitives et une automatisation avancée sans codage. Installez via Office > Obtenir les compléments.

Planificateur de Tâches Windows avec Script VBA

Créez un script VBA autonome (.vbs) déclenché par le Planificateur de tâches Windows pour ouvrir, actualiser et enregistrer les fichiers Excel automatiquement à des heures prédéterminées.

Astuces et conseils

✓Testez votre calendrier d'actualisation avec un petit ensemble de données avant de déployer sur des fichiers volumineux.
✓Utilisez le format .xlsm pour préserver tout code VBA; les fichiers .xlsx suppriment les macros lors de l'enregistrement.
✓Définissez des intervalles d'actualisation raisonnables (15-60 minutes) pour équilibrer la fraîcheur des données et les performances.
✓Surveillez l'état de connexion d'Excel via Données > Connexions pour diagnostiquer rapidement les actualisations échouées.

Astuces avancées

★Combinez REFRESH.ALL() avec des fonctions de journalisation d'erreurs pour capturer les actualisations échouées et notifier les utilisateurs via des déclencheurs de courrier électronique en VBA.
★Utilisez la fonctionnalité 'Chargement incrémental' de Power Query pour n'actualiser que les enregistrements nouveaux/modifiés, réduisant considérablement le temps de traitement.
★Planifiez les actualisations pendant les heures creuses (par exemple, 2-4 AM) pour minimiser l'impact réseau et assurer la stabilité du système.
★Implémentez le contrôle de version en ajoutant des horodatages aux fichiers de sauvegarde après chaque actualisation à l'aide des fonctions DateTime de VBA.

Résolution de problèmes

L'actualisation prend trop de temps et ralentit l'ensemble du système.

Activez 'Actualisation en arrière-plan' dans les propriétés de requête pour éviter le gel de l'interface utilisateur. Envisagez de scinder les grandes requêtes en tables plus petites et actualisez-les séquentiellement à l'aide d'Application.OnTime.

L'actualisation planifiée ne se déclenche pas automatiquement lorsque le fichier est fermé.

Le Planificateur de tâches Windows est requis pour actualiser les classeurs fermés. Créez un script .vbs qui lance Excel, ouvre le fichier et exécute l'actualisation via les macros VBA.

L'authentification de la source de données continue d'échouer après le démarrage du calendrier d'actualisation.

Stockez les identifiants de manière sécurisée à l'aide de la fonctionnalité 'Chiffrer' d'Excel ou du Gestionnaire des identifiants Windows. Évitez de coder en dur les mots de passe en VBA.

L'actualisation de Power Query renvoie des données en cache au lieu de données nouvelles.

Désactivez la mise en cache des requêtes via Données > Requêtes et connexions > cliquez avec le bouton droit sur requête > Propriétés > décochez 'Charger dans le modèle de données' et invoquez Ctrl+Shift+F9.

Formules Excel associées

REFRESH.ALL()Application.OnTime NOW()TIME()INDIRECT()INDIRECT.EXT()

Questions fréquentes

Puis-je planifier l'actualisation d'Excel sans utiliser des macros VBA?

Oui, utilisez la planification d'actualisation intégrée à Power Query (Données > Actualiser à l'ouverture du fichier) ou Power Automate pour l'automatisation cloud. Cependant, VBA offre un contrôle plus granulaire.

Quelle est la fréquence d'actualisation maximale que je peux définir dans Excel?

Power Query permet des intervalles d'actualisation aussi bas que 1 minute, mais les limites pratiques dépendent de la capacité de la source de données. La plupart des entreprises utilisent des intervalles de 15 à 60 minutes.

Comment actualiser les fichiers Excel selon un calendrier si je ne garde pas le fichier ouvert?

Utilisez le Planificateur de tâches Windows combiné à un fichier .xlsm compatible VBA et un script wrapper .vbs. Le script lance Excel en mode arrière-plan, ouvre le classeur et déclenche l'actualisation.

Les calendriers d'actualisation fonctionneront-ils si Excel est fermé ou l'ordinateur est éteint?

L'actualisation Excel standard ne fonctionne que lorsque le fichier est ouvert. Pour automatiser les actualisations de fichiers fermés, utilisez le Planificateur de tâches Windows, Power Automate ou Power 365.

Comment gérer les erreurs qui se produisent lors de l'actualisation planifiée?

Implémentez la gestion des erreurs en utilisant 'On Error Resume Next' et 'Err.Description' de VBA pour journaliser les défaillances. Envoyez des notifications via Outlook ou des webhooks et implémentez la logique de nouvelle tentative.