Pour ne plus pleurer

  De Gaetan Mourmant | 29/10/2019 - 02:38 |
Réagir

Le problème de la corruption des fichiers

L’une des choses les plus hor­ri­bles qui puis­sent arriv­er à vos fichiers excel, c’est de tout per­dre suite à la cor­rup­tion du fichi­er. C’est une sit­u­a­tion assez rare — heureuse­ment — mais qui peut quand même arriv­er… 2 étu­di­ants sur 40 lors de mon dernier cours avec des fichiers assez com­plex­es.

Pour lim­iter les dégâts, voici un code VBA qui va — lors de chaque fer­me­ture du fichi­er — sauve­g­arder une ver­sion du fichi­er en inclu­ant la date et l’heure.

Comme cela, si le fichi­er se cor­rompt, vous pour­rez tou­jours repar­tir d’un dernier enreg­istrement non cor­rompu.

Procédure :

Enreg­istrez d’abord le fichi­er.

Dans VBE (ALT+F11), dou­ble-cliquez sur This­Work­book, puis coller ce code. 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim fso As Object
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    ThisWorkbook.Save
    fso.CopyFile ThisWorkbook.Path & "\" & ThisWorkbook.Name, ThisWorkbook.Path & "\" & Replace(Date, "/", "-") & "-" & "-" & Replace(Time, ":", "-") & "-" & ThisWorkbook.Name
End Sub

Beau­coup d’amélio­ra­tions sont pos­si­bles, j’y reviendrai dans d’autres posts.

  • Vous pour­riez garder unique­ment les n fichiers les plus récents.
  • Vous pour­riez chang­er le réper­toire de sauve­g­arde.
  • Finale­ment, vous pour­riez créer la copie à chaque sauve­g­arde grâce à l’événe­ment Workbook_AfterSave.

PS : sur cer­tains ordi­na­teurs, ce code peut pos­er des prob­lèmes en rai­son du Scripting.FileSystemObject. Si c’est le cas, il existe plusieurs raisons :

  • Vous êtes sur MAC. Il existe des alter­na­tives, mais c’est un peu com­pliqué.
  • Il est pos­si­ble qu’il y ait des pro­tec­tions si vous tra­vaillez sur un serveur.
  • Il manque une référence au sys­tème de script (Dans VBE, Out­ils-Références, Microsoft Script­ing Run­time). Le fichi­er se trou­ve en général ici : C:\WINDOWS\System32\Scrrun.dll. S’il n’est pas là, vous pou­vez l’a­jouter en choi­sis­sant Par­courir…
En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

SI.NON.DISP

  De Gaetan Mourmant | 29/10/2019 - 01:21 |
Réagir

Vous con­nais­sez cer­taine­ment la fonc­tion SIERREUR. Elle per­met de rem­plac­er la com­bi­nai­son = SI(ESTERREUR(…)).

Voici une autre fonc­tion qui per­met d’i­den­ti­fi­er l’er­reur #N/A, au lieu d’u­tilis­er =SI(ESTNA(…)).

Elle s’u­tilise de la même manière que SIERREUR :

=SI.NON.DISP(valeur, valeur_si_na)

Quand utilis­er l’une par rap­port à l’autre?

Evidem­ment, cela dépend de la sit­u­a­tion.

  • Si vous ne voulez cap­tur­er que l’er­reur NA, le cas le plus courant étant une RECHERCHEV ou un INDEX/EQUIV, alors, utilisez SI.NON.DISP
  • Si vous voulez cap­tur­er toutes les erreurs, alors on utilise SIERREUR.

Par exem­ple, sup­posons que vous vouliez retouner la valeur du CA pour la ville sélec­tion­née, en gérant l’er­reur.

Si la valeur cher­chée existe (Lille), mais qu’elle ren­voit une valeur d’er­reur (ici #DIV/0), la fonc­tion SI.NON.DISP va retourn­er la valeur d’er­reur, ce qui est une bonne chose, car l’in­for­ma­tion retournée est plus pré­cise. La fonc­tion SIERREUR retourne le mes­sage d’er­reur indiqué dans la for­mule.

Dans le cas de Par­ris (avec deux “r”), les deux fonc­tions vont retourn­er le mes­sage d’er­reur indiqué dans la for­mule.

Ques­tion sub­sidi­aire, mais pourquoi n’ont ils par util­isé la fonc­tion SINA !!!

PS : pour en savoir plus sur la com­bi­nai­son INDEX/EQUIV, regardez cette série de vidéo :

Y en a encore ?
En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

Complément : ajouter des formules à une requête de liaison externe

  De Gaetan Mourmant | 9/10/2019 - 21:49 |
3 Commentaires

Suite à ce post et aux com­men­taires, voici une vidéo de com­plé­ment.

Je n’ai pas dit mon dernier mot 🙂 con­cer­nant l’in­té­gra­tion de for­mules dans la zone de com­men­taires, mais je n’ai pas encore trou­vé !

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel