Bug sur code compilation fichiers

[JulienG__]

Bonjour à tous,

Après lecture de différents sujets, j'ai essayé de créer un code (grande première pour moi) afin de compiler différents fichiers xls mais qui comporte de nombreux bugs et imperfections.
Je me remets donc à vous pour pouvoir les résoudre.

J'ai un nombre non défini de fichiers xls dans un dossier "Fiches à compiler" dont les intitulés exacts ne sont pas connus.
Pour chacun des fichiers, je souhaite copier les cellules B3 à B34 de l'onglet "Fiche Annuaire AICM" pour les coller dans un fichier "Synthèse" (idéalement sous forme de lignes les unes en dessous des autres).

Vous trouverez en pièce jointe un fichier .rar qui comporte le fichier de synthèse comportant la macro ainsi que des fichiers test à compiler.

Merci d'avance à celui / celle qui pourra m'aider, j'avoue avoir passer pas mal de temps dessus ne pas m'en sortir !
N'hésitez pas à me demander plus de précisions,

Cordialement,
Julien



----
Code :
Sub Recup()

Dim Fichier As String
Dim Chemin As String
Dim I As Integer

'adapter le chemin où se trouvent les classeurs
Chemin = "C:\Users\jug65138\Desktop\Julien Guerle\Autre\Perso\AICM\Compilation automatique\Bug sur code compilation fichiers\Fiches à compiler\"

'récupère tous les classeurs Excel .xls (pas les autres, donc à adapter)
Fichier = Dir(Chemin & "*.xls")

'évite les entêtes
I = 1

'boucle sur tous les classeurs afin de récupérer les valeurs dans la feuille 1 ("Fiche Annuaire AICM")
'adapter le nom des feuille
Do While Fichier <> ""

I = I + 1
'récupère les valeurs par formules

Range("A" & I).Value = Fichier
Range("A" & I).Offset(0, 1).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B3"
Range("A" & I).Offset(0, 2).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B4"
Range("A" & I).Offset(0, 3).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B5"
Range("A" & I).Offset(0, 4).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B6"
Range("A" & I).Offset(0, 5).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B7"
Range("A" & I).Offset(0, 6).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B8"
Range("A" & I).Offset(0, 7).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B9"
Range("A" & I).Offset(0, 8).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B10"
Range("A" & I).Offset(0, 9).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B11"
Range("A" & I).Offset(0, 10).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B12"
Range("A" & I).Offset(0, 11).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B13"
Range("A" & I).Offset(0, 12).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B14"
Range("A" & I).Offset(0, 13).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B15"
Range("A" & I).Offset(0, 14).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B16"
Range("A" & I).Offset(0, 15).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B17"
Range("A" & I).Offset(0, 16).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B18"
Range("A" & I).Offset(0, 17).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B19"
Range("A" & I).Offset(0, 18).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B20"
Range("A" & I).Offset(0, 19).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B21"
Range("A" & I).Offset(0, 20).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B22"
Range("A" & I).Offset(0, 21).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B23"
Range("A" & I).Offset(0, 22).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B24"
Range("A" & I).Offset(0, 23).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B25"
Range("A" & I).Offset(0, 24).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B26"
Range("A" & I).Offset(0, 25).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B27"
Range("A" & I).Offset(0, 26).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B28"
Range("A" & I).Offset(0, 27).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B29"
Range("A" & I).Offset(0, 28).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B30"
Range("A" & I).Offset(0, 29).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B31"
Range("A" & I).Offset(0, 30).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B32"
Range("A" & I).Offset(0, 31).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B33"
Range("A" & I).Offset(0, 32).Formula = "='" & Chemin & "[" & Fichier & "]Fiche Annuaire AICM'!B34"


'et ne garde que les valeurs
Range("A" & I).Offset(0, 1).Value = Range("A" & I).Offset(0, 1).Value
Range("A" & I).Offset(0, 2).Value = Range("A" & I).Offset(0, 2).Value
Range("A" & I).Offset(0, 3).Value = Range("A" & I).Offset(0, 3).Value
Range("A" & I).Offset(0, 4).Value = Range("A" & I).Offset(0, 4).Value
Range("A" & I).Offset(0, 5).Value = Range("A" & I).Offset(0, 5).Value
Range("A" & I).Offset(0, 6).Value = Range("A" & I).Offset(0, 6).Value
Range("A" & I).Offset(0, 7).Value = Range("A" & I).Offset(0, 7).Value
Range("A" & I).Offset(0, 8).Value = Range("A" & I).Offset(0, 8).Value
Range("A" & I).Offset(0, 9).Value = Range("A" & I).Offset(0, 9).Value
Range("A" & I).Offset(0, 10).Value = Range("A" & I).Offset(0, 10).Value
Range("A" & I).Offset(0, 11).Value = Range("A" & I).Offset(0, 11).Value
Range("A" & I).Offset(0, 12).Value = Range("A" & I).Offset(0, 12).Value
Range("A" & I).Offset(0, 13).Value = Range("A" & I).Offset(0, 13).Value
Range("A" & I).Offset(0, 14).Value = Range("A" & I).Offset(0, 14).Value
Range("A" & I).Offset(0, 15).Value = Range("A" & I).Offset(0, 15).Value
Range("A" & I).Offset(0, 16).Value = Range("A" & I).Offset(0, 16).Value
Range("A" & I).Offset(0, 17).Value = Range("A" & I).Offset(0, 17).Value
Range("A" & I).Offset(0, 18).Value = Range("A" & I).Offset(0, 18).Value
Range("A" & I).Offset(0, 19).Value = Range("A" & I).Offset(0, 19).Value
Range("A" & I).Offset(0, 20).Value = Range("A" & I).Offset(0, 20).Value
Range("A" & I).Offset(0, 21).Value = Range("A" & I).Offset(0, 21).Value
Range("A" & I).Offset(0, 22).Value = Range("A" & I).Offset(0, 22).Value
Range("A" & I).Offset(0, 23).Value = Range("A" & I).Offset(0, 23).Value
Range("A" & I).Offset(0, 24).Value = Range("A" & I).Offset(0, 24).Value
Range("A" & I).Offset(0, 25).Value = Range("A" & I).Offset(0, 25).Value
Range("A" & I).Offset(0, 26).Value = Range("A" & I).Offset(0, 26).Value
Range("A" & I).Offset(0, 27).Value = Range("A" & I).Offset(0, 27).Value
Range("A" & I).Offset(0, 28).Value = Range("A" & I).Offset(0, 28).Value
Range("A" & I).Offset(0, 29).Value = Range("A" & I).Offset(0, 29).Value
Range("A" & I).Offset(0, 30).Value = Range("A" & I).Offset(0, 30).Value
Range("A" & I).Offset(0, 31).Value = Range("A" & I).Offset(0, 31).Value
Range("A" & I).Offset(0, 32).Value = Range("A" & I).Offset(0, 32).Value




'fichier suivant
Fichier = Dir

Loop

End Sub

 

[fredl]

Re : Bug sur code compilation fichiers

Bonjour julien,ouf, j'en arrive à bout.Cela m'a donné un peu du fil à retordre.Donc, lorsque tu cliques sur le bouton, cela lance la macro recup2Elle ajoute à ton fichier synthèse tous les fichiers xls (du repertoire spécifié dans la macro) en dessous de la derniere ligne renseignée de ton tableau synthèse.(pour info, dans cette macro, chacun des fichiers est ouvert puis fermé apres recup des infos.)Si tu reparts de ton fichier synthèse vide, laisse la premiere ligne avec les x.Tu la supprimera apres tes premiers ajouts.Veiller à enlever les fichiers du repertoire en question apres traitement au risque de les rajouterde nouveau à ton listing ou, prévoir une routine qui les stoquent automatiquementailleurs apres traitement.Dis moi ce que donne le test.Pour ma part, je l'ai lancé etcela s'est bien passé (voir resultat dans le fichier)A+Fred

 

[JulienG__]

Re : Bug sur code compilation fichiers

Bonjour Fred,

C'est top, merci beaucoup !

J'ai juste identifier 4 erreurs / sources de bug / plantage ci dessous :

1- Lors du chargement des premières fiches, sur les colonnes AF - Tél (prof) et et AG - mail (prof), les cellules ne se renseignent pas correctement. Sont présentées les adresses où se trouve l'information (i.e. C/.....) et non la donnée finale (i.e. n° téléphone / adresse mail). Ces colonnes se renseignent correctement une fois les lignes mises en formes (fond de couleur...) dépassées.

2- Excel plante lorsque certaines fiches sont présentes dans des groupes de fiches à chargées. J'ai cependant réussis à charger ces fiches de manière indépendante.

3- 4 fichiers ne peuvent pas être chargées et entraînent l'ouverture d'une Fenêtre d'information "Microsoft Visual Basic" dont le message est "Erreur d'execution '1004' - Erreur définie par l'application ou par l'objet". Quand je clique sur "Débogage", est surlignée en rouge la ligne :
_ Cas 1 : Range("A" & i).Offset(0, 1).Formula = "='[" & Fichier & "]Fiche Annuaire AICM'!B3"
_ Cas 2 : Workbooks.Open Chemin & "\" & Fichier

4- Le fichier présente des "0" dans les cases qui ne sont pas renseignées sur les fichiers sources. Est qu'il y a un moyen de les faire disparaître ?

En raison des points 2 et 3 je suis obligé de charger les fiches par groupe de 5 ce qui ralentit la manip'.
J'ai mis en pièce jointe tous les fichiers correspondants à ces points.

En dehors ça marche super, merci beaucoup !

 

[fredl]

Re : Bug sur code compilation fichiers

Julien
Point 1 : je ne le rencontre jamais....donc je ne peux y remédier (voir nb2 qui peut etre une solution)
Point 2 : je fais le meme constat sans pour autant comprendre ce qui se passe...
A creuser, mais j'ai pas le temps
point 3 : renommer les fichier afin qu'il n'y ait pas de " ' "
point 4 : j'ai mis une mise en forme conditionnelle dans la feuille (si 0 : format blanc= non visible)

nb : le resultat des 7 chargements est en bleu
nb2 : si c'était une macro à moi, je récupèrerai toutes les données des fichiers dans des variables, et j'intègrerai ensuite ces variables dans le bilan... a toi de voir (ce serait moins source d'erreur)
ex dans fiche agent : VN°fiche= range("B3").value
dans bilan : Range("A" & i).Offset(0, 1).Formula =VN°fiche


Dis moi!
A bientot!
j-4 avant les vacances.
Fred

 

[JulienG__]

Re : Bug sur code compilation fichiers

Bonjour Fred,

Merci à nouveau pour ton retour !

Super pour la mise en forme conditionnelle.
Concernant le nb2, ça me va très bien d'intégrer ces éléments à la macro mais je ne comprends pas très bien ce que je dois faire...
Concernant le point 3, ok pour le cas 1 où les intitulés des fichiers avaient des ', en revanche dans le cas 2, le seul point commun entre les intitulés est le "-" et cela ne pose pas de problème pour d'autres fichiers ayant des "-" dans l'intitulé...

 

[fredl]

Re : Bug sur code compilation fichiers

Je confirme que les "-" ne posent pas de problème.
Par contre, d'une maniere générale, pour info, éviter les espaces dans les noms de fichiers
Je verrai si j'ai un peu de temps à te consacrer pour tester avec variables et noms de fichiers corrects...

 

[fredl]

Re : Bug sur code compilation fichiers

Bon, je viens de faire un essai avec des noms de fichiers corrects et avec des variables :
Toujours des bugs à l'ouverture de fichiers si on les traite tous en meme temps...
Désolé : je ne vois pas.
A+
Frédéric