Vérifier la cohérence des données d'un fichier Excel

Bonjour,

C'est mon premier post sur le forum. Je ne me suis pas encore présenté.
Je suis stagiaire et je n'ai que 1 moi pour créér ce projet assez fou ! Mon entreprise me demande de vérifier les informations contenues dans des fichiers excel. En effet, l'entreprise vend ces fichiers excels à un autre entreprise, et il faut que les validité des données soient d'une qualité irréprochable.

L'entreprise qui m'embauche perd beaucoup de sous à causes des erreurs (oublis) humaines dans les fichiers excel.
Je suis chargé de créer un vérificateur de données automatique. Une fois que le fichier excel est créé par les techniciens, ces personnes n'ont plus cas utiliser le vérificateur pour voir s'il n'ont pas oublié de remplir des champs etc.
Par exemple il faut vérifier que le champ "code postal" contienne bien un code postal, et un code valide (à 5 chiffres). Il faut aussi tester le numéro de téléphone pour s'assurer que le technicien ne s'est pas trompé en tapant, il ne faut pas qu'il fasse 9 chiffres au lieu de 10. Ce sont des exemples, il y a des centaines de tests à faire en réalité.

L'onglet "validation des données" de Excel n'est pas assez complet.

Je pourrai programmer tout ça en VBA mais ça me parait vraiment très très long car j'ai beaucoup de structure de test à faire.

Voici une partie du fichier excel à vérifier:
https://www.dropbox.com/s/pcyiidp3se...%202.xlsx?dl=1
Voici un screenshot du fichier excel si vous ne souhaitez pas l'ouvrir:
https://www.dropbox.com/s/pwu4t2h8b8...%20pm.png?dl=1

Je vous remercie par avance infiniment pour votre aide
Je suis preneur de n'importe quelle idée et information.

Bonjour,

La validation de données sans autre artifice doit être capable de faire 90% du job de vérification de saisie au moins.
Pour les 10% restants, ce sera sûrement aussi possible mais en se cassant un peu plus la tête. Pour éviter de trop se casser la tête, tu peux la coupler à une fonction personnalisée basée sur les expressions régulières comme celle-ci:

Code:

---

1 2 3 4 5 6 7

Public Function RegExTest(textToTest As String, textPattern As String) As Boolean Dim reg   Set reg = CreateObject("VBScript.RegExp") reg.pattern = textPattern RegExTest = reg.test(textToTest) End Function

---

Ex pour un numéro de téléphone en case A1, le test serait:

Code:

---

=RegExTest(A1; "0\d(( \d{2}){4}|\d{8})")

---

(10 chiffres requis éventuellement séparés par un espace tous les deux chiffres; 1er chiffre égal à 0)

Ex pour un code postal en France métropolitaine (enfin je suis pas spécialiste, c'est peut-être pas exactement ça)

Code:

---

=RegExTest(A1; "(0[1-9]|[1-8]\d|9[0-5])\d{3}")

---

=> On accepte les codes dont les deux premiers chiffres sont entre 01 et 95. Bien sûr ce serait facile aussi à faire avec des formules traditionnelles; c'est pour l'exemple.

Pour vérifier si toutes les données requises ont bien été saisies, tu as plusieurs choix, notamment:
1- Mise en forme conditionnelle sur les cases requises mais encore vides.
2- Une formule dans une case quelque part testant si aucune des cases requises n'a été laissée vide.
3- A supposer que tu ais donné un nom à chaque case requise, une macro bouclant sur les noms et vérifiant que les cases pointées ne sont pas vides.

En bref, MFC + validation de données + éventuellement un peu de VBA (mais un tout petit peu alors) sont tout ce qu'il te faut.
Il ne faut pas t'en faire toute une montagne.

Bonjour
Si vous le permettez : ce qui me frappe :
1)

Citation:

---

L'entreprise qui m'embauche perd beaucoup de sous à causes des erreurs (oublis) humaines dans les fichiers excel.

---

2)

Citation:

---

Mon entreprise me demande de vérifier les informations contenues dans des fichiers excel. En effet, l'entreprise vend ces fichiers excels à un autre entreprise, et il faut que les validité des données soient d'une qualité irréprochable

---

Je n'a, de toute ma vie, jamais vu une entreprise sérieuse (vendeuse, en plus), ne pas inclure de telles vérifications au moment même de la saisie ou (au plus tard) à l'issue même de la saisie.
Voilà donc une "affaire" qui me laisse pour le moins perplexe !

Bonjour,

Une proposition se basant sur une feuille Modèle pour les contrôles avec une certaine syntaxe.
Une saisie est soit obligatoire (commence par obli:), soit optionnelle (commence par opt:)
obli: et opt: sont suivi du type de contrôle à appliquer :
- contrôle spécifique comme obli: email, opt: tel, ...
- contrôle appartient à une liste comme obli: UPR. Bien que ce contrôle ne devrait pas avoir lieu à posteriori je l'ai quand même mis
- contrôle avec Like() comme obli: IMB/#####/[A-Z]/##[A-Z]# (qui est peut être faux, je n'ai mis que des exemples)

Si le principe te parait applicable dans ton cas (il ne faut pas x cas particuliers sinon autant développer un truc dédié) tu pourrais compléter avec une syntaxe pour des regex plus complets que Like ou d'autres contrôles spécifiques.
J'ai prévu un contrôle spécifique pour les dates mais rien mis dedans, à toi de voir.
Limité à 10 anomalies par une constante, je les sors avec un msgbox. Tu pourrais faire un userform non modal qu'il puissent corriger en ayant la liste sous les yeux.

Je n'ai pas poussé les tests à fond, je te laisse contrôler.
eric

edit: ajout d'une version complétée avec contrôle par regex (Controle saisies.xlsm)

Citation:

---

Envoyé par Cyril_B

Je pourrai programmer tout ça en VBA mais ça me parait vraiment très très long car j'ai beaucoup de structure de test à faire.

---

Et donc... ton idée ce serait que quelqu'un d'autre le fasse à ta place ??? :lol:

Citation:

---

L'onglet "validation des données" de Excel n'est pas assez complet.

---

A ta place, j'aurais plutôt opté pour la mise en forme conditionnelle.

Ca permet de mettre autant de règles que tu veux pour chaque cellules indépendamment.
Tu peux mettre des couleurs différentes en fonction des erreurs détectée ou de la gravité de l'erreur.
Tu peux même mettre des conditions qui prennent en compte le résultat d'autres cellules.
Les opérateurs pourront donc être alertés instantanément des erreurs qu'ils introduisent dans les données.

Seul défaut : en VBA, on ne peut pas vérifier la couleur qui est générée par une MFC.
Le test final pourra être un test visuel.

Bonsoir à vous,

Excusez moi pour ma réponse tardive, je mettais en applications vos idées !
Tout d'abord merci infiniment à vous pour toutes ces bonnes pistes !

Effectivement je devrais m'en sortir dans l'ensemble.
Alors j'ai oublié de préciser quelque chose, je n'ai pas le droit de toucher une seule case du fichier à traiter. Je dois me débrouiller de travailler dans un autre excel, ou d'extraire les données. La mise en forme conditionnelle n'est pas adaptée car la vérification du fichier doit se faire après qu'il soit rempli. Le technicien constitue le fichier excel, ensuite seulement la vérification est faite.

Je pense que je vais m'en sortir pour tout ce qui est valeurs dans les cellules excel. En revanche je bloque quelque part: comment utiliser une structure de test sur un objet.
Le fichier Excel contient des bouton à cocher, des images, des cercles, des traits. Des formes en fait ! Et il faut que je teste ça.

Voici un exemple en image: en fonction du bouton coché, il faut que je vérifie que la cellule du dessous contient bien du texte:
https://www.dropbox.com/s/swmxw5yewp...%20pm.png?dl=1

Voici un autre exemple:
Il faut que je vérifie qu'il y a bien une image, ensuite il faut que je vérifie qu'il y a bien un sous-titre sous cette image, ensuite il faut que je vérifie qu'il y a bien un cercle, rouge, qui est positionné sur l'image. Autrement dit... je pense que c'est impossible malheureusement :
https://www.dropbox.com/s/68lz3zt2xd...01_pm.png?dl=1

Bonjour,

Donc, tu n'es absolument pas sûr de pouvoir te baser entièrement sur une "solution générique" ? Tu ne sais jamais si et quand tu vas devoir repartir à zéro à cause d'un technicien "trop créatif" ?

Si c'est cela, je pense que tes patrons et tes "copains" techniciens devraient Impérativement lire, relire et méditer la réponse de unparia.

Parce que, tu vas passer le reste de tes jours à faire cela...

La règle d'or en programmation (et même en "excellisation") est que c'est beaucoup moins coûteux de le faire de la bonne façon du premier coup. Le plus coûteux, c'est de corriger un produit déjà commercialisé. Toi, tu es presque avec le produit commercialisé et les coûts ne doivent pas en être bien loin.

Probablement, que quelques règles de conception et un peu de discipline de la part des techniciens te faciliteraient la vie et tu pourrais te concentrer sur les "cas extrêmes".

Le fichier est très long à remplir, et comme les techniciens sont des humains, ils font des erreurs. Toujours est-il que non je ne peux pas "prévenir" plutôt que "guérir". Le fichier d'origine ne doit pas bouger d'un poil ! Il faut que je fasse la vérification après coup.
Il y a déjà de la mise en forme conditionnelle dans le fichier, mais ça ne suffit pas, il y a tellement de cas. Il y a au minimum 250 champs à vérifier dans le fichier.

Ce n'est pas compliquer de regarder si une cellule est vide ou pleine, ce qui est dur c'est de travailler avec des objets aussi, comme des images, des cercles, des boutons etc...

Avez vous des idées à ce sujet ?

Merci pour votre aide et le temps que vous m'avez accordé ! Vous êtes au top ! :)

Bonjour, bonjour !

Citation:

---

Envoyé par Cyril_B

Je pourrai programmer tout ça en VBA mais ça me parait vraiment très très long car j'ai beaucoup de structure de test à faire.

---

Tu t'attends à quoi d'autre vu cette discussion créée dans le forum VBA ?‼

En dehors de l'incohérence de l'entreprise à éviter des contrôles en amont directement lors de la saisie (validation) …
Évitant justement ta future usine à gaz de contrôle en VBA !

Citation:

---

Envoyé par Cyril_B

Le fichier d'origine ne doit pas bouger d'un poil ! Il faut que je fasse la vérification après coup.

---

Là c'est à se demander pourquoi avoir créé cette discussion !


Quant aux objets, regarder leurs propriétés dans l'aide interne VBA …

______________________________________________________________________________________________________
Je suis Paris, Charlie, Bruxelles, …

Bonjour
Comme d'habitude on envoie toujours les stagiaires a la chasse au dahu ;)

pour faire toute ces vérifications il faudrait être sur que tout ces fichiers soit identiquement conçus sinon ca risque d'être compliqué

pour ma part j'ai ouvert un de tes fichiers et bien je l'ai refermé tout de suite

je réitère donc ce que l'on t'a dis plus haut parle en a la hiérarchie qui fasse en sorte que des fichier formaté soit instaurés et c'est seulement a ce moment la que l'on pourra faire une vérif. automatique de tes cellules

et file un coup de pied au c... a tes techniciens

d'autant plus que ce sont des fichiers facturés !!!!! si j'ai bien compris

donne moi le nom de la boite que je fasse pas appel a eux :mouarf:

Bonjour,

Je suis moi même stagiaire, et on m'a demandé aussi des contrôles sur des fichiers, un peu tout comme toi.

Ma première démarche a été de demandé à ce qu'on utilise un modèle un fichier type, sinon les contrôles seraient impossible ou au mieux archaïque.
Le demandeur l'a très bien pris et a compris la situation. Il en a résulté que pour la relecture ensuite aussi été facilité.

Je connais le principe des techniciens tête de mule. mais Si tu veux mené ton projet à bien et même pour le bien de ton entreprise instauré un modèle serait l'idéal. Sinon faudrait faire des vérifications en fonction du nom de la colonne, pour peut que le technicien l'ait bien écrit ... bref.

Le mieux serait de leur mettre un fichier avec déjà la mise en forme les colonnes dans l'ordre etc. Déjà les techniciens gagneront du temps et en plus cela rendra ton travail faisable.

Ensuite si tu dois procéder à des vérifs sans touché ton fichier, tu pourrais boucler sur le tableau entier avec un For each bien fabriqué et faire un contrôle en fonction du numéro de colonne. Ensuite si erreur il y a tu le notifies sur un autre classeur en précisant la ligne avec erreur, la colonne et au choix une bref description de l'erreur, sur ce fichier tu sautes une ligne et rebelote.
Avec seulement 250 champs remplit cela devrait être très rapide, après à voir le type de vérification.
Si tu dois regarde si une cellule est vide un contrôle range = empty suffit et pour comparer à un pattern je te conseille d'utilisé la fonction "like".

DeathZarakai.

Bonjour à vous !

Je vous remercie pour toutes ces réponses.
A la lecture de vos commentaires, je pense que je n'ai pas précisé quelque chose, qui pourtant est très important.
Les techniciens se contentent de remplir un fichier template. Ce qui veut dire que tous les fichiers que j'ai à vérifier ont la même structure.
Tous les fichiers que je dois traiter sont exactement les mêmes, à une chose près: la valeur des champs justement.

Je peux très bien tester la cellule C2 de la feuille numéro 2 pour verifier qu'elle ne soit pas vide, ce test va marcher sur tous les fichiers excels, rien ne varie dans la structure des fichiers à tester.

J'ai commencé à faire un chose toute bête: utiliser les structures de test et les fonctions dans un second fichier excel. ce nouveau fichier excel va lire les données en temps réel dans le fichier à vérifier.
Voici un exemple:

Voici un screenshot du champ que je dois vérifier dans le fichier:
https://www.dropbox.com/s/67pmvueby9...ifier.png?dl=1

Voici mon nouveau fichier excel qui fait le test:
https://www.dropbox.com/s/vxugcideku...imple.png?dl=1

Qu'en pensez-vous ? C'est trop simple non ? Je pense que je vais être limité dans les possiblités, notamment pour faire une vérification sur des objets.

Bonjour,

Un retour sur ce que je t'ai proposé au post 4 serait le bienvenu même si ça ne t'intéresse pas...
eric

Salut Eric !

Je me suis de nouveau penché sur ce que tu as fait, c'est assez grandiose effectivement.
Par contre cette solution nécessite de créer fichier modèle. Rajouter des feuilles modèles, et je n'ai pas le droit de modifier le fichier original.

Mais c'est clair que c'est assez génial ce que tu as fait, ça marche extrèmement bien. En tout cas merci infiniment pour le temps passé, je vais certainement m'en servir !
Ensuite j'ai aussi peur de ne pas pouvoir travailler sur des boutons ou des images etc.

Tu en penses quoi ?

Tout est dans le même fichier mais la macro tourne (en théorie correctement) sur la feuille active du fichier actif.

Citation:

---

Tu en penses quoi ?

---

J'en pense que chercher avec Paint vu ce que tu proposes c'est pas terrible...
eric

Bonjour à tous !

Je reviens vers vous pour vous dire où j'en suit et pour vous expliquer là où je bloque actuellement.
En résumé: j'arrive à faire un vérificateur en temps réel des des champs du fichier excel à vérifier.
En revanche je n'arrive pas à faire une vérification poussée.

J'ai encore besoin de vous !
Voici une vidéo qui explique tout ça:
https://youtu.be/6w10PxG7rXg

Merci beaucoup par avance pour votre aide et votre temps.
Bonne soirée :)

Je fais une vidéo car je pense qu'une image vaut plus que 1000 mots. Mais si vous préférez je vous fais les 1000 mots aussi:

Je suis chargé de développer un outil qui vérifie les fichiers excel crées par les techniciens de mon entreprise.
Je suis bloqué plus précisément sur une feuille du fichier excel car elle est plus complexe à analyser. Le technicien prend des photos et les intègre dans cette feuille excel. Ensuite il les commente avec une légende, des étiquettes et des formes (flèches, cercles etc.) (voir la fin de la vidéo pour voir concrètement).

Je dois faire des tests de vérification sur l'ensemble de cette feuille excel. Je dois par exemple vérifier que sur chaque image il y a bien entre 1 et 3 étiquettes (pas plus ni moins). Je dois aussi vérifier qu'il y a bien un cercle rouge dans cette feuille et que celui-ci est positionné sur une image.
Si les conditions ne sont pas validées, c'est donc que le technicien s'est trompé à un moment donnée, donc je dois l'avertir de son oubli ou de son erreur.

Je dois donc manipuler des objets pour faire ces tests: images, formes (et couleurs), etiquettes, contenu des étiquettes. Et je dois pouvoir trouver leurs positions dans la feuille excel (si une étiquette n'est pas positionnée sur une image, c'est qu'il y a une erreur)

Est-ce clair ?
Merci beaucoup pour votre aide,
Désolé pour mes faux pas, je ne veux vexer personne moi :(

Citation:

---

Envoyé par Cyril_B

Je dois par exemple vérifier que sur chaque image il y a bien entre 1 et 3 étiquettes (pas plus ni moins).

---

Qu'est-ce qu'une étiquette ?
Plus exactement, qu'est-ce qui qualifie qu'un objet est considéré comme une étiquette et qu'un autre n'en est pas une ?

Citation:

---

Je dois aussi vérifier qu'il y a bien un cercle rouge dans cette feuille et que celui-ci est positionné sur une image.

---

Il va falloir que tu scrutes tous les objets de ta feuille pour trouver les cercles rouges et déterminer sa position (ses positions haut et gauche font partie de ses propriétés et ses position bas et gauche peuvent être calculées avec sa largeur et sa hauteur).
Pour chaque cercle, il faudra lancer une seconde scrutation pour trouver les image et, là encore, trouver leurs limites et voir l'une d'elle a des limites correspondant au cercle.

Les images comme les cercles sont des Shape.
https://msdn.microsoft.com/fr-fr/lib.../ff821817.aspx
Tu peux scruter toutes les Shapes en lançant une boucle For Each sur la collection Shapes de la feuille.
https://msdn.microsoft.com/fr-fr/lib.../gg264596.aspx
Tu peux savoir lesquels sont des images ou des formes (comme un cercle) avec la propriété Type de la Shape : image a la valeur 13, une forme a la valeur 1.
https://msdn.microsoft.com/fr-fr/lib.../ff836170.aspx
Regarde les autres propriété de l'objet Shape pour trouver la position, hauteur, largeur, couleur de trait,
https://msdn.microsoft.com/fr-fr/lib.../ff193908.aspx

Super Menhir ! Merci beaucoup !

J'ai mis ça en place ce week-end, c'est parfait ça marche super bien j'arrive a extraire les données de toutes les shapes de ma feuille à vérifier. Il me reste à les exploiter :)

Merci encore Menhir ça m'a mis sur la bonne voie :)

Voici le code que j'utilise:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

Option Explicit Sub GetShapeProperties() Dim sShapes As Shape, lLoop As Long Dim wsStart As Worksheet, WsNew As Worksheet Dim tocheck As String '''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''LIST PROPERTIES OF SHAPES''''''''''''' ''''''''''Dave Hawley www.ozgrid.com'''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' tocheck = Worksheets("tables").Range("A2").Text 'MsgBox (tocheck) 'Set wsStart = Workbooks(tocheck).Worksheets("Adduction PMI") Set wsStart = ActiveSheet Set WsNew = Workbooks("verif.xlsm").Worksheets("DataShapes") 'Add headings for our lists. Expand as needed WsNew.Range("A1:G1") = _ Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top", "Text") 'Loop through all shapes on active sheet For Each sShapes In wsStart.Shapes 'Increment Variable lLoop for row numbers lLoop = lLoop + 1 With sShapes 'Add shape properties WsNew.Cells(lLoop + 1, 1) = .Name WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.Name WsNew.Cells(lLoop + 1, 3) = .Height WsNew.Cells(lLoop + 1, 4) = .Width WsNew.Cells(lLoop + 1, 5) = .Left WsNew.Cells(lLoop + 1, 6) = .Top WsNew.Cells(lLoop + 1, 7) = .TextFrame.Characters.Text 'Follow the same pattern for more End With Next sShapes 'AutoFit Columns. WsNew.Columns.AutoFit End Sub

---

Je souhaite récupérer le texte qui est contenu dans un AutoShape, c'est à dire le texte qui est contenu dans une infobulle. J'essaye de la faire avec la ligne suivante mais je n'y arrive pas:

Code:

---

WsNew.Cells(lLoop + 1, 7) = .TextFrame.Characters.Text

---

Vous avez une idée ?