Catégorie : Excel

Excel : retraiter des données

Dans le cadre de nos fonctions, il nous arrive fréquemment de devoir exporter et retraiter des données issues d’un logiciel professionnel (comptabilité, paie, CRM…).

Les données obtenues sont souvent incomplètes : des cellules vides émaillent mon document, rendant son exploitation difficile.

J’ai souvent perdu des heures à compléter les données manquantes. Jusqu’au jour où j’ai découvert l’astuce de mon ami Gaëtan MOURMANT (www.xlerateur.com). Des heures et des heures de travail économisées !

Pour combler les vides, 2 minutes suffisent :

  • Positionnez votre curseur n’importe où dans votre tableau.
  • Appuyez sur F5.
  • Dans la boîte de dialogue, sélectionnez Cellules vides.
  • Saisissez = puis cliquez sur la cellule jute au-dessus.
  • Validez à l’aide des touches Ctrl + Entrée.

Le tour est joué : votre tableau est complété.

 

Découvrez sa vidéo ici .

Retrouver aisément les derniers fichiers utilisés

Sans doute avez-vous l’habitude de créer des raccourcis vers les fichiers que vous utilisez fréquemment. Mais il existe une solution plus simple pour retrouver aisément les derniers fichiers utilisés : épingler les fichiers dans leur logiciel d’origine. En d’autres termes, les conserver en top liste des fichiers les plus récemment ouverts.

Pour cela :

  • ouvrez votre fichier avec votre application Office.
  • activez le bouton MS Office (version 2007) ou Fichier (version 2010 et plus).
  • cliquez sur l’option Récent.
  • le nom du fichier ouvert apparaît dans la liste des documents ouverts les plus récemment.
  • cliquez sur l’épingle blanche située face au nom de fichier.
  • l’icone devient bleue et le fichier est désormais « épinglé ». Vous y accéderez par le biais de l’option Fichier – Récent.
  • Si vous n’utilisez plus un fichier épinglé, cliquez simplement sur l’icone bleue en face de son nom.

Cette astuce fonctionne de la même façon avec l’explorateur Windows.

Concevoir un userform

Les boîtes de dialogue (ou Userform en anglais) permettent une véritable interaction avec l’utilisateur.  Elles comportent des zones de texte, des listes déroulantes, des cases à cocher… qui guident l’utilisateur dans ses choix. Elles sont donc très présentes en programmation.

Nous allons aujourd’hui découvrir comment concevoir ce type d’userform à travers un petit exercice Word mais le principe de la Userform vaut également pour tous les logiciels Office.

Pour cet exercice, nous repartirons sur la promesse d’embauche de la leçon précédente. Nous allons créer sous Word une boîte de dialogue pour permettre à un utilisateur de saisir tous les renseignements nécessaires pour finaliser le document. Pour cela, les zones à compléter ont été marquées à l’aide de signets.

Pour mémoire, pour visualiser les emplacements des signets dans un document Word, activez FichierOptions, Options avancées, et cochez la case Afficher les signets.

comment afficher un symbole signet dans Word

Les emplacements sont matérialisés par une [ grise. Pour plus de clarté, j’ai indiqué dans l’illustration ci-dessous le nom de chacun de ces signets en violet et à côté de la  [ : imprimez cette page car nous aurons besoin des ces indications pour insérer les différents éléments de notre boîte de dialogue à l’issue de notre macro.

signets

Créer une boîte de dialogue ou Userform

A présent, entrons dans le vif du sujet et passons à la création de la boîte de dialogue.

Tout d’abord, affichez l’éditeur Visual Basic (Alt F11).

Cliquez sur l’icône userform userform à gauche dans la barre d’outils.

Une grille intitulée Userform1 s’affiche ainsi qu’une barre d’outils Contrôles. user2

Les contrôles servent à positionner des des Zones de textes, cases à cocher, listes déroulantes… dans le Userform.
A l’aide de votre souris, survolez chacun de ces contrôles afin de bien les identifier.
Au cours de cette leçon, nous allons manipuler certains de ces contrôles (Intitulé, zone de texte, bouton de commande et, pour le plaisir, Image).

Agrandissez à présent un peu la Userform : positionnez votre curseur sur un angle de la Userform et tirez à l’aide de la souris. Vous pourrez, bien évidemment, à tout moment réajuster la taille de cette boîte.

Ajouter des contrôles Intitulés et Zones de texte

Avant d’ajouter nos contrôles sur notre boîte de dialogue, nous devons tout d’abord lister les informations que nous aurons besoin de récupérer :

L’adresse : elle comprend un champ societe, contact, rue1, éventuellement rue2, cp et ville. Nous créerons donc autant de champs textes que d’indications à fournir, en l’occurrence 6 zones.

La date, nos références, vos références, pièces jointes, objet, signataire, titre du signataire : pour ces champs, nous utiliserons les mêmes noms que ceux utilisés dans les signets, à l’exception de date que nous nommerons ‘datel’ (‘date’ étant un mot réservé en programmation).

Salutation (le contenu des salutations 1 et 2 étant identiques, un seul contrôle suffit). Par contre, il convenait de définir 2 signets, un par position dans le corps de la lettre.

A présent, plaçons les contrôles.

Les intitulés (étiquettes) – Propriété Caption

Cliquez sur l’icône intitule et déplacez votre curseur sur la Userform. Votre curseur  prend la forme +  A. Cliquez sur un emplacement de la grille pour ajouter une étiquette (Label en anglais).captionPour modifier l’intitulé de l’étiquette et afficher Société, dans la boîte de dialogue Propriétés située à gauche de votre écran sous la zone Projets. Si cette fenêtre n’apparaît pas, appuyez sur F4. Repérez le champ Caption (légende) et cliquez 2 fois derrière Label1 pour le sélectionner.

A présent, tapez tout simplement le nom de l’étiquette, à savoir « Société : » et appuyez sur entrée.

Les zones de textes – Propriétés Name

A présent, nous allons insérer notre première zone de texte. Contrairement à l’Intitulé qui fournit des indications destinées à l’utilisateur sur la zone qui suit, une zone de texte est une zone que l’utilisateur peut compléter. Cliquez sur l’icône  ab| et tracez une zone de texte juste à côté de l’étiquette que vous venez de dessiner. Tracez-la suffisamment longue pour qu’elle puisse accueillir un nom de société.

Nous allons à présent nommer cette zone de texte. Pourquoi ? Tout simplement parce que nous en aurons besoin pour récupérer son contenu. Dans la fenêtre de Propriétés, cliquez 3 fois sur le champ (Name), et saisissez le nom « societe ». Validez en appuyant sur entrée. Choisissez de préférence des noms courants, parlants et sans accent ni espace, pour des questions de sécurité.

Pour chaque élément à récupérer, à votre tour créez une étiquette et une zone de texte comme ci-dessous :

boite

Pour celles ou ceux qui le souhaitent, vous pouvez télécharger ici la Userform ci-dessus. Sauvegardez-la sur votre disque dur puis, dans le menu Fichier de l’éditeur Visual Basic, sélectionnez l’option Importer un fichier et pointez vers le fichier. Validez : Userform1 est insérée dans le répertoire Feuilles.

Les autres contrôles et leurs propriétés : boutons de commande, image et UserForm

Il nous reste à présent quelques éléments à dessiner sur notre boîte de dialogue : un bouton « Valider » pour valider le contenu, un bouton « Annuler » pour refermer la boîte de dialogue et enfin, une image (logo), pour la fine bouche.

Les boutons de commande – Propriétés Caption et Name

  • Pour placer un bouton de commande, cliquez sur l’icône  puis cliquez sur un emplacement libre de votre boîte de dialogue.
  • Il va à présent falloir ajouter un texte à ce bouton. C’est là le rôle de la propriété Caption. Dans la fenêtrePropriétés, cliquez 2 fois sur « Caption » et saisissez « Valider ». Appuyez sur entrée pour terminer.
  • Nous allons à présent donner un nom à ce bouton. Double cliquez sur la propriété « Name » et saisissez « Ok ». On le voit : le nom du bouton (« Ok ») peut être différent de son intitulé (« Valider »).
  • De la même manière, créer un bouton ayant les propriétés suivantes :
    • Caption : Annuler
    • Name : Cancel

Facultatif – Insérer une image

A présent, nous allons ajouter notre image. Pour ce faire, je vais utiliser le logo de ce site (vous pouvez faire de même en opérant un clic droit sur l’image en Hautet à gauche de cette page, option « Enregistrer l’image sous »).

  • Cliquez sur l’icône 
  • Dans la fenêtre de Propriétés, double-cliquez sur « Picture » et pointez vers votre image. Validez
  • Si votre image n’apparaît pas intégralement dans votre zone d’image, double-cliquez sur la propriété PictureSizeMode et testez les différentes options une à une.
  • Si vous ne souhaitez pas rajouter de bordure autour de votre image, double-cliquez sur la propriété BorderStyle et sélectionnez la première option « 0-fmBorderStyleNone ».

Tester la boîte de dialogue

  • Pour visualiser la boîte de dialogue, cliquez  puis sur le bouton Bouton  de la barre d’outils ou appuyez sur F5.
  • Pas mal non ? Reste cependant à finaliser un dernier point : notre boîte de dialogue s’intitule « Userform1 » et nous allons remédier à ce petit inconvénient.
  • Pour refermer la boîte de dialogue, cliquez sur la case « x » : les boutons « valider » et « Annuler » ne fonctionnent pas… encore du moins, car nous ne leur avons pas associé de code !
  • Une fois votre boîte refermée, sélectionnez votre Userform en cliquant sur la barre de titre bleue de la boîte de dialogue. Dans la fenêtre de propriétés, champ « Caption », saisissez « Renseignements utilisateur ».
  • Testez à nouveau votre boîte de dialogue… et sauvegardez !

Insérer du code : bouton « annuler »

Le code du bouton Annuler étant extrêmement limité, nous allons commencer par le traiter.

  • Dans votre UserForm, double-cliquez sur le bouton que vous avez intitulé « Annuler »
  • Le texte suivant apparaît :

Que doit-il se passer si l’utilisateur clique sur ce bouton « annuler » ? La boîte de dialogue doit simplement se refermer. L’instruction Visual Basic correspondante est unload nom_de_la_feuille.

  • Placez-vous entre les deux lignes de code et saisissez « Unload Userform1 »
  • Testez à nouveau votre UserForm en cliquant sur F5 et sur le bouton « Annuler ». Cette fois, tout devrait fonctionner…

Insérer du code : bouton « Valider » – Positionner le curseur dans le document Word

Nous entamons là la partie la plus complexe et la plus longue de notre code.

Voici le détail des opérations que nous allons devoir accomplir :

  • Récupérer le contenu de chacune de nos zones de texte.
  • Insérer ce contenu dans notre document grâce à nos signets.
  • Refermer la boîte de dialogue.
  • Positionner notre curseur au début de notre lettre pour que l’utilisateur puisse compléter le corps de la lettre.

Ouf !

Tout d’abord, pour insérer le code, vous l’aurez deviné, il vous faut double-cliquer sur le bouton « valider ».

Vous obtenez les deux lignes de code suivantes :

Fonction Placez votre point d’insertion entre ces deux lignes.

S’agissant de notre première boîte de dialogue, nous allons faire simple et récupérer les données telles que, sans les reformater, en partant du principe que l’utilisateur les ait saisies correctement. Nous veillerons au cours des leçons suivantes à améliorer et optimiser ce code.

Positionner le curseur dans le document Word

Nous avons défini, en page 2, différents signets dans notre document Word. Reprenez ou imprimez cette liste.

Nous allons déplacer notre curseur sur chacun de ses signets et y insérer le contenu de notre boîte de dialogue.

Vous avez sans doute noté que nous n’avons créé qu’un seul signet pour l’adresse alors qu’elle comporte plusieurs lignes : nous traiterons ce cas vers la fin de cette leçon. Let’s go, ce n’est pas très compliqué !

La syntaxe pour atteindre un signet Word est la suivante :

selection.goto([What], [Which], [Count], [Name] as range)

Encore une fois, faisons simple et ne nous embarrassons pas de détails inutiles (nous ne sommes pas des programmeurs chevronnés… pour l’instant !).

La syntaxe minimale dont nous aurons besoin est selection.goto,,, »nom_du_signet ».

Pour notre signet « date », cela donnera donc selection.goto,,, »date ». Insérez cette ligne de code et double-cliquez sur « Userform » dans la fenêtre Projets, puis appuyez sur F5 pour lancer la macro. Déplacez la boîte de dialogue : votre curseur est bien placé derrière « Paris, le « . Appuyez sur le bouton « Cancel » et revenez dans l’éditeur de code et double-cliquez à nouveau sur le bouton « valider ».

Voici le reste des instructions à saisir (faites un copier-coller dans Word si vous le souhaitez) :

Private Sub ok_Click()
Selection.GoTo , , , « date »
Selection.GoTo , , , « vosref »
Selection.GoTo , , , « nosref »
Selection.GoTo , , , « objet »
Selection.GoTo , , , « pj »
Selection.GoTo , , , « salutation1 »
Selection.GoTo , , , « salutation2 »
Selection.GoTo , , , « signataire »
Selection.GoTo , , , « titre »
Selection.GoTo , , , « debut »
End Sub

Pour l’instant, nous n’avons fait que déplacer notre curseur aux différents signets. Nous allons apprendre à présent, àinsérer le contenu de notre boîte de dialogue.

Insérer du code : bouton « valider » – insérer le contenu de la boîte de dialogue

Nous l’avons vu précédemment dans notre petit cahier des charges, après avoir atteint chaque signet, il nous faut insérer le contenu de la boîte de dialogue correspondant.

Par exemple, à l’emplacement du signet « date », nous devons insérer le contenu du contrôle de notre UserForm que nous avons appelé « datel ».

Idem pour le signet « vosref », nous allons insérer le contenu de la zone de texte de notre UserForm que nous avons nommée « vosref » et ainsi de suite.

Pour insérer du texte, la syntaxe est on ne peut plus simple :

selection.InsertAfter nom_du_contrôle

Ce qui nous donne, pour la date, selection.InsertAfter datel
Placez cette ligne de code sous la ligne Selection.GoTo , , , « date » en insérant une ligne supplémentaire.

Principe : on atteint le signet, on insère le texte. On atteint le signet, on insère le texte, etc. Il va donc falloir insérer une instruction selection.InsertAfter après chaque ligne selection.goto.

Notre code sera donc :

Private Sub ok_Click()
Selection.GoTo , , , « date »
Selection.InsertAfter datel
Selection.GoTo , , , « vosref »
Selection.InsertAfter vosref
Selection.GoTo , , , « nosref »
Selection.InsertAfter nosref
Selection.GoTo , , , « objet »
Selection.InsertAfter objet
Selection.GoTo , , , « pj »
Selection.InsertAfter pj
Selection.GoTo , , , « salutation1 »
Selection.InsertAfter salutation
Selection.GoTo , , , « salutation2 »
Selection.InsertAfter salutation
Selection.GoTo , , , « signataire »
Selection.InsertAfter signataire
Selection.GoTo , , , « titre »
Selection.InsertAfter titre
Selection.GoTo , , , « debut »
End Sub

Remarques :

Signets « salutation1 » et « salutation2 » : le contenu de ces deux signets est identique, à savoir le contenu de la zone de texte que nous avons appelée « salutation ».

Signet « debut » : on ne fait que positionner le curseur au début du corps de la lettre, avant de rendre la main à l’utilisateur.

A présent, occupons-nous du cas particulier de l’adresse.

Insérer du code : bouton « valider » – insérer un retour chariot dans Word

Une adresse s’écrit sur plusieurs lignes.
Nous aurions fort bien pu définir autant de signets que de lignes mais il est plus simple -nous verrons plus tard pourquoi- d’insérer des retours chariot à l’aide de code.

Dans le cas qui nous occupe, nous devons récupérer, dans l’ordre, les valeurs des contrôles suivants:

societe – contact – rue1 – rue2 – cp – ville

L’adresse est égale à tous ces contrôles, « additionnés » ou plutôt concaténés (mis bout à bout) et séparés par un retour chariot ou un espace (cp et ville).

Si le « + » est le symbole de l’addition, & s’emploie pour concaténer des chaînes de caractères.

Le changement de ligne (retour chariot) s’écrit quant à lui chr(10) et l’insertion d’un espace se fait à l’aide de 2 guillemets séparés par un espace  » « .

En langage clair, nous pourrions écrire notre adresse comme ceci (rc = retour chariot) :

adresse = societe + rc + contact + rc + rue1 + rc + rue2 + rc + cp + un espace + ville

Si nous remplaçons la ligne ci-dessous par les équivalents visual basic, nous obtenons donc :

adresse = societe & chr(10) & contact & chr(10) & rue1 & chr(10) & rue2 & chr(10) & cp &  »  » & ville

Il ne nous reste plus qu’à atteindre le signet « adresse » et à insérer dans notre code la syntaxe ci-dessus. Ce qui nous donne, au final :

Private Sub ok_Click()
Selection.GoTo , , , « date »
Selection.InsertAfter datel
Selection.Goto , , , « adresse »
Selection.InsertAfter societe & chr(10) & contact & chr(10) & rue1 & chr(10) & rue2 & chr(10) & cp &  »  » & ville
Selection.GoTo , , , « vosref »
Selection.InsertAfter vosref
Selection.GoTo , , , « nosref »
Selection.InsertAfter nosref
Selection.GoTo , , , « objet »
Selection.InsertAfter objet
Selection.GoTo , , , « pj »
Selection.InsertAfter pj
Selection.GoTo , , , « salutation1 »
Selection.InsertAfter salutation
Selection.GoTo , , , « salutation2 »
Selection.InsertAfter salutation
Selection.GoTo , , , « signataire »
Selection.InsertAfter signataire
Selection.GoTo , , , « titre »
Selection.InsertAfter titre
Selection.GoTo , , , « debut »
End Sub

Pour l’heure, il nous reste encore à résoudre 2 problèmes fondamentaux pour que cette macro soit totalement opérationnelle : refermer la boîte de dialogue (elle reste affichée, même après un clic sur le bouton valider) et son lancement (pour l’instant, nous la lançons à travers l’éditeur, ce qui n’est pas l’idéal…).

Rassurez-vous : le plus dur est fait et vous le constaterez, les deux dernières étapes ne vous prendront pas plus de 5 minutes…

Refermer la boîte de dialogue

Vous l’avez constaté : si nous renseignons notre boîte de dialogue et cliquons sur le bouton « Valider », les informations sont bien insérées dans notre document Word… mais la boîte de dialogue reste affichée.

Il faut donc insérer une ultime instruction dans notre code pour résoudre cet épineux problème. Faisons court et simple :unload me (ou unload Userform1) est notre instruction. Le code final est donc :

Private Sub ok_Click()
Selection.GoTo , , , « date »
Selection.InsertAfter datel
Selection.Goto , , , « adresse »
Selection.InsertAfter societe & chr(10) & contact & chr(10) & rue1 & chr(10) & rue2 & chr(10) & cp &  »  » & ville
Selection.GoTo , , , « vosref »
Selection.InsertAfter vosref
Selection.GoTo , , , « nosref »
Selection.InsertAfter nosref
Selection.GoTo , , , « objet »
Selection.InsertAfter objet
Selection.GoTo , , , « pj »
Selection.InsertAfter pj
Selection.GoTo , , , « salutation1 »
Selection.InsertAfter salutation
Selection.GoTo , , , « salutation2 »
Selection.InsertAfter salutation
Selection.GoTo , , , « signataire »
Selection.InsertAfter signataire
Selection.GoTo , , , « titre »
Selection.InsertAfter titre
Selection.GoTo , , , « debut »
Unload me
End Sub

Passer par l’éditeur vb pour lancer la macro n’est pas commode ni à la portée de tous. Il existe forcément une alternative que je vous invite à découvrir.

Lancer la macro à l’ouverture du modèle

Nous souhaitons qu’à chaque création d’un nouveau document basé sur le modèle, la macro se lance automatiquement.

Il nous faut donc rattacher l’apparition de la boîte de dialogue à l’ouverture du modèle :

  • Sélectionnez la fenêtre « Projet »
  • Double-cliquez sur « ThisDocument »
  • Dans la fenêtre de code, dans la liste déroulante « (Général) », sélectionnez « Document ». Par défaut, cela affiche la procédure « Private sub document_New ».
  • La procédure document_new se déclenche lorsque l’utilisateur crée un nouveau document basé sur un modèle. C’est donc bien de cette dernière dont nous avons besoin.
  • Il ne nous reste plus qu’à faire apparaître la boîte de dialogue à l’écran pour que l’utilisateur puisse la remplir. Cette action s’accomplit grâce à l’instruction nom_de_la_userform.show. Dans notre cas de figure, c’est donc la syntaxe suivante qu’il faut insérer entre les deux lignes de code : userform1.show.

Le code final est donc :

Private sub Document_New()
Userform1.show
End Sub

Sauvegardez votre macro, refermez l’éditeur et sauvegardez votre modèle.

Il ne vous reste plus qu’à lancer ce dernier à l’aide de la commande « Fichier – Nouveau… ».

Votre macro est à présent totalement opérationnelle.
Pour avoir une idée des améliorations qu’il serait possible de lui apporter ou une suggestion d’exercice, rendez-vous à la conclusion.

Conclusion

Certes, cette leçon a été particulièrement longue, mais à l’issue de cette dernière, vous devriez être en mesure de créer vos propres boîtes de dialogue personnalisées.

Pas convaincu(e) ? Essayez ! Je vous invite à créer de toutes pièces une petite boîte de dialogue pour le modèle de fax. N’oubliez pas de définir vos signets à l’intérieur du document Word… Voici une suggestion de présentation :

Au cours des prochaines leçons, nous apprendrons à utiliser d’autres contrôles, telles les listes déroulantes, les cases à cocher.

Nous verrons également comment pré-remplir certaines zones : par exemple, le contrôle datej avec la date du jour formatée ou bien encore la zone signataire avec votre nom…

Vous aurez aussi remarqué que nous avions défini un signet sur la mention « Lettre recommandée avec ar » et que nous n’avons pas encore fait référence à ce dernier : nous avions pour cela besoin des formules conditionnelles que nous ne maîtrisons pas… encore…

Notre macro pêche aussi par le fait que l’utilisateur peut saisir n’importe quel texte dans n’importe quelle zone, par exemple, une suite de nombre ou du texte dans la zone « datej »… ce qui n’est pas trop problématique pour l’instant mais que nous apprendrons à résoudre.

Macro Excel : remplir des PDF depuis un fichier Excel

Vous devez régulièrement remplir des formulaires PDF avec des données contenues dans un fichier Excel ? Vous souhaitez éviter les copier/coller entre les 2 applications ? Aucun souci grâce à cette macro géniale trouvée sur YouTube et à nos explications…

Le principe de la macro

La macro remplit en rafale, d’un clic de souris, 10 formules PDF et enregistre chacun d’eux sous un nom spécifique en ajoutant le nom du patient et la date de RDV de celui-ci.

Cette macro comprend 2 fichiers :

  • un fichier Excel comportant les coordonnées de 10 patients ;
  • un formulaire PDF vide « Patient intake form ».

Le projet VBA inclut 3 macros dont 2 qui ne sont pas abordées dans la vidéo car n’étant pas au coeur du sujet traité :

  • SavePDFFolder : cette macro permet de récupérer automatiquement le nom du répertoire de sauvegarde des formulaires PDF complétés. Elle n’est pas indispensable : vous pouvez, en effet, saisir à la main l’adresse dudit répertoire, à condition que le chemin d’accès soit indiqué en totalité.
  • PDFTemplate : comme la précédente, cette macro est facultative. Elle permet d’identifier le nom du formulaire PDF à remplir. Là aussi, vous pouvez saisir l’information manuellement.
  • CreatePDFForms, objet du tutoriel. 

Les principales instructions

Vous trouverez ci-dessous quelques explications sur le code destinées à vous aider à le décrypter et à l’adapter, le cas échéant, à vos propres besoins.

Dim

Dim est une instruction qui permet de définir des variables et de réserver la place nécessaire en mémoire en fonction de leur type.

La structure est : dim NomdelaVariable as type.

Il est intéressant, lorsque vous définissez un nom de variable, de mêler capitales et minuscules. De cette façon, vous pourrez ensuite saisir le nom de vos variables en minuscules dans vos lignes de code.

  • Si tout va bien, lorsque vous appuierez sur la touche Entrée pour changer de ligne, le nom de la variable sera converti tel que vous l’avez défini dans votre instruction Dim.
  • Si rien ne se passe, c’est que vous avez commis une erreur de saisie.

Une astuce bien pratique pour éviter toute mauvaise surprise.

Le choix du type, lui, dépend du type de résultat attendu. Les plus communs sont :

Nature de la variable

Texte

Date

Format monétaire

Nombre long

Type à utiliser

String

Date

Currency

Long

Cette liste est loin d’être exhaustive. Consultez l’aide en ligne de VBA pour plus d’information.

With... end with

With… end with s’utilise pour raccourcir le code et éviter de répéter une structure commune.

Dans notre exemple, with… end with est utilisé avec Sheet1. Elle nous évite ainsi de devoir saisir à chaque fois « Sheet1.Range ».

Par souci de concision, nous aurions pu utiliser cette même structure avec Application un peu plus loin dans le code pour éviter d’avoir à le saisir devant les instructions SendKeys ou Wait. Nous aurions donc écrit :
With Application
   .SendKeys …
   .SendKeys …
   .Wait …
End with

Remarquez que les instructions abrégées sont précédées d’un « . »

Atteindre la dernière cellule

L’instruction .Range(« E9999 »).End(xlUp).Row déplace le curseur depuis la cellule E9999 vers la dernière ligne du tableau (« E9999 ») vers le haut (xlUp) en simulant Ctrl ↑.

For... to... Next...

For… to… Next… constitue ce qui s’appelle une boucle. Tout ce qui est compris entre for et next est répété x fois.

La répétition démarre à la valeur indiquée après For. Ici, il s’agit de CustRow (ligne active) qui est définie à 5 (CustRow=5). Pourquoi cette valeur 5 ? Tout simplement parce que le tableau de données commence -et commencera toujours dans notre exemple- à la ligne 5.

La valeur située après to indique la valeur à atteindre. Ici, LastRow correspond à la dernière ligne, soit la ligne 14.

Le code situé entre For et Next sera donc répété de la ligne 5 à la ligne 14, soit 10 fois.

 

Application.SendKeys

Cette instruction permet de simuler la frappe de touches au clavier. Elle est utile pour piloter des applications externes à Microsoft. En effet, le VBA est un langage qui ne fonctionne qu’avec la Suite Office. Application.SendKeys contourne donc cette limite.

ATTENTION !! Il vous faudra probablement adapter ces SendKeys à la version des logiciels que vous utilisez. En effet, les instructions SendKeys de la macro pilotent des logiciels en langue anglaise tandis que vous utilisez sans doute une version française… Les intitulés de menus, les raccourcis clavier, les lettres soulignées ne sont peut-être pas les mêmes. Donc si votre macro plante, vérifiez en premier lieu ces paramètres.

Pour plus d’information sur SendKeys, cliquer ici.

 

Application.Wait

Cette instruction permet de mettre une macro en pause, soit jusqu’à une heure précise, soit durant un laps de temps défini.

Ici, c’est cette dernière option qui a été retenue. Now permet en effet de récupérer l’heure système. A l’heure système, on ajoute quelques millièmes de secondes (+0.0001).

Pourquoi ralentir le code ? Certaines données passées au formulaire PDF sont particulièrement longues (par exemple, les adresses). Il faut donc laisser à l’ordinateur le temps de saisir l’intégralité des informations dans le champ correspondant, tout en demandant à la macro d’attendre !

Si vous constatez que certains champs ne sont complétés qu’en partie, augmentez la valeur de Wait.

Les commentaires

Les commentaires sont utiles au décryptage des macros. Elles sont introduites par une aspostrophe ‘ et apparaissent en bleu dans l’éditeur.

C’est une bonne pratique à généraliser.

Pour terminer, je vous présente une autre vidéo qui permet, cette fois, de récupérer le contenu de plusieurs formulaires PDF dans un fichier Excel.

A noter : l’utilisation de cette macro nécessite l’installation du logiciel gratuit Foxit, téléchargeable ici.

Excel : Maîtrisez les segments

Les segments, une autre façon de filtrer vos informations

Les segments ont fait apparition sous Excel depuis déjà plusieurs années. Peut-être les avez-vous déjà utilisés pour filtrer vos tableaux dynamiques ou vos TCD de façon plus visuelle.

En fait, les segments prennent tout leur sens dans la conception de tableaux de bord car ils permettent de synchroniser plusieurs TCD sur un ou plusieurs segments.

Insérer un segment

Les segments sont disponibles dès lors que votre feuille de calcul comporte soit un TCD, soit un tableau dynamique (tableau mis en forme à l’aide du bouton « Mettre sous forme de tableau« ).

Pour insérer un segment, positionnez votre curseur dans une cellule du tableau dynamique ou du TDC et activez le menu contextuel correspondant à votre objet. Puis cliquez sur Insérer un segment.

Les entête de colonnes de votre base s’affichent : cochez ceux que vous souhaitez utiliser comme filtre(s). »

Utiliser un segment

Il n’y a strictement aucune différence entre le filtrage direct dans un tableau ou le filtrage par le biais d’un segment.

Lorsque l’on modifie un filtre, le segment correspondant est synchronisé. L’inverse est également vrai : une modification du segment modifie le filtre.

Synchronisation filtre/segment

L’avantage revient toutefois au segment qui permet une meilleure lisibilité des filtres, notamment lors de la sélection de plusieurs critères.

  • Pour activer un filtre depuis un segment, cliquez simplement dessus.
  • Pour activer un ensemble contigus de filtres, cliquez sur le premier filtre puis maintenez la touche Maj enfoncée et sélectionnez le dernier filtre à activer.
  • Pour activer un semble non contigus de filtres, cliquez sur chacun des filtres à appliquer en maintenant la touche Ctrl enfoncée.
  • Pour désactiver les filtres d’un segment, cliquez sur l’icône entonnoir située en haut et à droite de la boîte segment.
  • Pour supprimer un segment, sélectionnez-le et appuyez sur la touche Suppr de votre clavier. Attention : la suppression d’un segment ne désactive pas le filtre mais le segment.

Les options des segments

Comme tout objet, les segments disposent d’options. 

En tout premier lieu, le design du segment, accessible par le truchement de la zone Style de segments.

Excel, comme à l’accoutumée, vous offre même la possibilité de créer votre propre style de segment.

Les segments sont redimensionnées en hauteur et en largeur. Mais l’une des options les plus intéressantes est l’option Colonnes.

Cette option vous permet, lorsque vous avez des segments particulièrement longs avec barre de défilement, de disposer les étiquettes des segments sur plusieurs colonnes.

Les segments et les tableaux de bord

Les segments prennent tout leur sens dans l’élaboration de tableaux de bord car il devient alors possible de synchroniser plusieurs tableaux croisés dynamiques avec un ou plusieurs segments.

Vous devez d’abord positionner votre curseur sur un tableau croisé dynamique et afficher un ou plusieurs segments.

Votre segment est pour l’heure connecté à un seul et unique TCD.

Maintenez la sélection sur votre segment et activez le menu contextuel correspondant. Cliquez sur l’icône Signaler les connexions situé vers la gauche du ruban.

Cette action affiche une boîte de dialogue où sont listés tous les tableaux croisés dynamiques de votre classeur, feuille par feuille. Activez les cases des TCD que vous souhaitez relier à votre segments.

Tous vos TCD sont automatiquement filtrés sur le segment correspondant !

Vous pouvez ainsi créer très rapidement toutes sortes de tableaux de bord.

Insérer une chronologie

Outre les segments, Excel offre également la possibilité d’insérer des « chronologies ».

Pour cela, positionnez-vous sur un tableau dynamique et activez l’onglet contextuel Outils de tableau croisé dynamique – Analyse, bouton Insérer une chronologie.

Qui dit chronologie, dit temps et donc dates : lorsque vous activez cette option, une boîte de dialogue s’ouvre vous proposant d’afficher les champs dates contenu dans votre base de données.

La présentation diffère sur plusieurs points d’un segment :

  • Les informations sont présentées sous la ligne d’une frise chronologique ;
  • Vous ne pouvez pas sélectionner des périodes de temps discontinues. Vous pouvez par contre utiliser le curseur pour sélectionner un ou plusieurs périodes contiguës.
Par défaut, les informations sont présentées par mois.
Le bouton MOIS situé à droite vous permet de sélectionner une autre échelle temporelle : Années, trimestres, mois et jours, ce que ne permettent pas les segments.

Alors, segment ou chronologie ? Le choix de l’un ou l’autre de ces outils dépend de vos objectifs !

EXCEL : « additionner » des chaînes de texte

Pour Marine J. 

Il est des cas de figure où l’on a besoin « d’additionner » des chaînes de caractères contenues dans des cellules distinctes. Le cas le plus commun est « l’addition » d’une cellule contenant un prénom à une autre cellule contenant un nom de famille.

Le terme adéquat pour ce type de manipulation de chaîne est concaténation, l’addition étant un vocable désignant l’ajout d’un chiffre ou nombre à un autre chiffre ou nombre.

C’est l’un des rares cas  où Excel propose deux fonctions -voire 3 sous Excel 2016*– ayant strictement le même objectif !

Exposé du contexte

Soit le tableau ci-dessous :

 

 

L’objectif sera de concaténer le contenu de la colonne A avec celui de la colonne B pour obtenir la colonne C.

La fonction =Concatener()*

La syntaxe de la fonction Concatener est extrêmement simple : concatener(chaîne1;chaîne2;chaîne3…) où chaîneX représente soit :

  • une adresse de cellule contenant du texte ;
  • un texte saisi entre guillemets («  »).
  • un mixe d’adresses de cellule et de chaînes de caractères.

Dans l’exemple ci-dessus, en C2, saisissez :

=concatener(A2; » « ;B2) 

et validez.

La chaîne  »  » contient un espace. Faute d’ajouter cette chaîne, vous obtiendriez une chaîne avec le prénom et le nom accolés (AliGATOR).

La fonction &

Une alternative consiste à taper une formule similaire à une addition mais en remplaçant les + par le symbole &.

Dans l’exemple ci-dessus, la formule donne donc :

 

= A2 &  »  » & B2 

Le résultat sera strictement similaire à celui de la fonction Concaténer(). Le choix de l’une où l’autre formule est plus une question de préférence personnelle.

 

* Une fonction Concat() rigoureusement identique au fonctionnement de Concatener() est disponible sous Excel 2016.

Aller plus loin...

Que vous utilisiez Concatener(), concat() ou &, vous aurez peut-être besoin de supprimer les colonnes initiales (A et B dans notre exemple). Mais si vous le faites sans figer les valeurs, vous obtiendrez un message d’erreur car la colonne de résultat contient alors une formule faisant référence à des colonnes… inexistantes.

Avant de supprimer ces deux colonnes, sélectionnez la colonne de résultat (ici C). Faites un copier (Ctrl C), puis utilisez l’option Collage spécial (en cliquant sur le petit triangle pointant vers le bas sous Coller) puis choisissez l’une des 3 options Coller des valeurs (j’opte généralement pour l’icône de droite).

Validez.

Vous pouvez à présent supprimer les colonnes A et B en toute sécurité.

Les modèles Office

Vos fonctions vous amènent à réutiliser souvent un document déjà existant et vous êtes une adepte du Fichier – Enregistrer sous…. Malheureusement, il vous arrive parfois, dans la précipitation, d’écraser votre fichier original…
Simples à mettre en oeuvre, les modèles pallient cet inconvénient.
Le modèle est donc particulièrement adapté dans le cadre de documents à caractère répétitif : contrats, devis, lettres, réponses à des candidatures, factures, formulaire de demande de congés….

Qu'est-ce qu'un modèle ?

Un modèle n’est rien d’autre qu’un document Word, Excel ou PowerPoint sauvegardé avec une extension particulière (.dot, dotx ou dotm, .xlt, .xltx, .xltm, .pot, .potx, potm).

A ce titre, le modèle peut donc inclure des macros (format *.dotm, dot, xltm, xlt, potm, pot), des zones de formulaires, de la vidéo… bref être aussi complexe que vous le souhaitez !

Créer un modèle

Créez votre document, comme à l’accoutumée.

  • Sélectionnez FichierEnregistrer sous
  • Donnez un nom à votre document.
  • Dans Type de fichier, sélectionnez Modèle de document

Attention ! Veillez à choisir le bon type de modèle ! En effet, depuis la version 2003, Office distingue les modèles contenant des macros de ceux qui n’en contiennent pas. Si vous choisissez par mégarde le format .dotx, toutes vos macros seront effacées.

C’est pourquoi je vous recommande d’opter pour les formats 97-2003 qui ne présentent pas ce risque. Autre avantage : si vous avez pris la précaution de cocher, lors de l’enregistrement la case Enregistrer la miniature, votre document sera sauvegardé avec une vignette Aperçu. Cette option bogue encore actuellement sous les version Office > 2013.

Remarquez au passage que Word se positionne automatiquement dans Mes documents\Modèles Office personnalisés. Ce répertoire centralise l’intégralité de vos modèles qu’il s’agisse de feuilles Excel, diaporamas PowerPoint, bref tous les modèles Office. 

Cet emplacement est bien évidemment modifiable, ainsi que nous le verrons plus loin.

Utiliser un modèle

Tous les modèles sont disponibles depuis le menu Fichier, rubrique Nouveau….

Attention : tandis que l’option Fichier – Nouveau… affiche une nouvelle boîte de dialogue à onglets vous permettant de choisir parmi les modèles créés, le raccourci Ctrl N quant à lui ne vous laisse pas le choix et affiche le modèle par défaut de Word (normal.dot).

Dans la boîte de dialogue qui apparaît, repérez les catégories de modèles PROPOSES PERSONNEL*. Vos modèles sont rattachés à la catégorie PERSONNEL. Pour y accéder, cliquez sur PERSONNEL et choisissez le modèle qui vous intéresse.

Word charge une copie du modèle -et non le modèle lui-même-, à qui il attribue un nom générique du style Document1, Document2. Ainsi, lorsque vous cliquerez sur Enregistrer, le programme vous invitera à saisir un nom pour votre document, préservant ainsi l’original d’un possible écrasement.

* Ce nom peut varier en fonction des versions d’Office. 

Modifier les répertoires par défaut

Office vous permet de faire cohabiter 2 répertoires de modèles. Ce nombre peut paraître superflu mais en réalité, la plupart des entreprises disposent d’un répertoire Modèles commun à tous les collaborateurs pour, par exemple, partager des modèles de notes de frais ou de congés, les demandes d’achat…

Vous pouvez alors utiliser le second répertoire soit pour pour partager des modèles avec un département spécifique (RH, comptabilité…), soit pour des modèles spécifiques à l’utilisateur.

La gestion de ces répertoires n’est accessible, bizarrement, que depuis Word et exclusivement ce logiciel.

  • Activez le menu FichiersOptions de Word.
  • Dans le menu, choisissez Options avancées et faites défiler la partie droite de l’écran presque jusqu’en bas.
 
  • Cliquez sur Emplacement des fichiers
 
  • Pour chacun des 2 répertoires Modèles, cliquez sur Modifier (si besoin est) et parcourez l’arborescence de votre disque dur ou réseau jusqu’au(x) répertoire(s) devant accueillir vos modèles.
  • Validez.

Modifier un modèle

Vous l’avez vu précédemment : lorsque l’on double clique sur un modèle, en fait ce n’est pas l’original qui est chargé, mais une copie de ce dernier.

Modifier un modèle,  requiert donc une manipulation particulière.

Deux possibilités :

Depuis le menu Fichier

  • Ctrl O pour afficher la boîte de dialogue Ouvrir.
  • Remontez votre arborescence, localisez votre modèle et cliquez sur Ok.

Depuis le répertoire de Modèles Office personnalisés

  • Clic droit sur le modèle puis Ouvrir.

Il ne vous reste à présent plus qu’à modifier votre modèle et et à l’enregistrer. Validez.

Excel

3 situations dans lesquelles utiliser Mettre sous forme de tableau

Vous connaissez sans doute l’option Mettre sous forme de tableau pour la mise en forme rapide de vos tableaux. Mais savez-vous qu’elle est particulièrement pertinente dans le cas de tableaux croisés dynamiques ou pour la réalisation de listes déroulantes ? Retour en détail sur ces 3 cas de figures.

1. Mettre en forme rapidement un tableau

Un clic suffit pour mettre en forme un tableau : il suffit de :

  • sélectionner votre tableau si ce dernier comporte une ligne de total ;
  • se positionner dans une cellule quelconque du tableau dans le cas contraire

puis d’activer dans le ruban Accueil, le bouton Mettre sous forme de tableau et de choisir l’un des modèles proposés.

Si vous ne trouvez pas le modèle de couleur ad hoc, activez l’onglet Mise en page et sélectionnez une palette de couleur : il s’en trouve sûrement une qui correspondra à vos attentes !

Autre avantage : en cas d’ajout de lignes, ces dernières seront automatiquement formatées.

Vous pouvez mettre en avant la 1ère ou dernière colonne, désactiver le bouton de filtre ou ajouter/désactiver les bandes grâces aux options du menu contextuel.

Convertir en plagePour revenir à un tableau Excel classique (tout en conservant la mise en forme de votre tableau), cliquez sur le bouton Convertir en plage que vous trouverez à gauche, dans la barre d’outils contextuels.

Attention ! Si la mise en forme de votre tableau est bien conservée, vous devrez cependant formater manuellement vos nouvelles lignes éventuelles.

2. Actualiser facilement un TCD

Changer la source de donnéesLorsque vous ajoutez des lignes à une base de données liée à un tableau croisé dynamique, vous devez obligatoirement cliquer sur le bouton Changer la source de données et re-sélectionner votre base pour actualiser votre TDC. Cette manipulation est fastidieuse.

ActualiserL’option Mettre sous forme de tableau vous affranchit de cette contrainte. Appliquez d’abord l’option Mettre en forme de tableau à votre base de données. Vous pouvez ensuite ajouter très simplement des enregistrements à votre base.Pour mettre à jour votre TCD, cliquez simplement sur le bouton Actualiser.

3. Rendre une liste déroulante dynamique

Grâce à l’option Mettre sous forme de tableau, vous pouvez ajouter de nouveaux éléments à votre liste en toute simplicité !

Présentez simplement votre liste Sous forme de tableau. Puis sélectionnez les données de votre liste et affectez-leur un nom. Insérez votre liste déroulante dans une ou plusieurs cellules.

Toutes les modifications apportées aux éléments de votre liste seront automatiquement répercutées à vos cellules contenant la liste déroulante.

C’est un confort appréciable et un vrai gain de temps !

Excel

Graphique : comment sélectionner facilement un élément

Lors d’une récente formation que j’ai dispensée, j’ai eu à réaliser des graphiques superposés et me suis trouvée confrontée à une problématique de taille : comment diable sélectionner facilement un élément, en l’occurrence les données du graphique lorsque ces dernières sont  situées en dessous du premier graphique et sont donc inaccessibles ??

En effet, lorsque le chevauchement est complet, seul le graphique en premier plan peut être sélectionné… La réponse, peu évidente, réside dans les options du menu contextuel Outils de graphique – onglet Format.

Tout à gauche de cet onglet figure une zone Sélection active comportant une liste déroulante. Cette dernière vous permet de sélectionner les éléments présents sur votre graphique, dont les données.

 

Insérer une photo dans une forme

Lasse des traditionnels formats d’images carrés et rectangulaires ? Pour rompre la monotonie, intégrez vos photos dans des formes !

L’astuce présentée ci-dessous est valable sous l’ensemble des logiciels Office, qu’il s’agisse de Word, Excel ou PowerPoint. Et cerise sur le gâteau, elle ne nécessite que quelques secondes pour être mise en oeuvre…

Procédure

  • Insérez une image dans votre document.
  • Sélectionnez l’image et activez le menu contextuel Outils ImageFormat.
  • Cliquez ensuite sur la petite flèche pointant vers le bas de l’outil Rogner.
  • Activez l’option Rogner à la forme et sélectionnez une forme.
  • Pour un cercle parfait, cliquez sur Ellipse : la forme de votre image est modifiée instantanément. Cliquez à nouveau sur les options Rogner et sélectionnez cette fois Rapport Hauteur-largeur puis Carré 1:1. Votre ellipse se transforme en un cercle parfait.
  • Déplacez éventuellement votre image à la souris pour qu’elle épouse au mieux votre forme !