Construction d'une macro qui aspire des données de plusieurs classeurs
Bonjour à tous,
Je tente en vain, de créer une macro VAB pour aller récupérer plusieurs données sur différents classeurs appartenant à un même dossier.
Etant débutant, depuis é semaine, je lis puis teste plusieurs bouts de codes trouvés sur le web sur ce sujet mais j'ai beau l'adapter, cela ne fonctionne jamais et j'ai toujours un bug.
Si quelqu'un peu me guider, ce serait sympathique car le but pour moi, est de comprendre et non pas d'obtenir une solution toute faite.
Voici en détail mon projet :
1/ Depuis un fichier nommé "fichier_synthese_SIG.xlsm", j'ai créé une macro qui me permet de lister le chemin puis le nom des fichiers sources, tous contenus sur un même dossier. Les résultats s'affichent ligne par ligne dans les colonnes A puis B de la feuille appelée "Liste des fichiers"
Je voudrai utiliser cette boucle qui dresse la liste des classeurs pour aller récueillir dans le même temps, des données qui se trouvent dans des cellules d'une feuille nommée "Synthese_des_donnees" aux mêmes adresses pour toutes (ex "K5", "L45", "Z3" etc.)
Toutes les données du premier classeur (94 données en tout) devront être collées sur la même ligne (en l'occurence, la ligne 3 pour le premier classeur) en commençant par la colonne "CX" puis "CY", "CZ", etc.
Toutes les données du deuxième classeur devront elles aussi, se trouver sur la même ligne (mais cette fois à la ligne 4) toujours en commençant par la colonne "CX", etc.
Toutes les données du troisième classeur devront elles aussi, se trouver sur la même ligne (mais cette fois à la ligne 5) toujours en commençant par la colonne "CX", etc.
et ainsi de suite jusqu'au dernier classeur...
2/ N'ayant pas réussi à les formater du premier coup, j'ai décidé de les stocker dans des colonnes à gauche inutilisées pour envisager de créer ensuite une autre macro qui me permettra cette fois, de formater le résultat obtenu (exemple : pour Z3, tranformer le chiffre en pourcentage, pour K5, ne garder que la 2ième ou la 3ième séquence entre des tirets, pour N41,afficher un décimal avec 1 chiffre après la virgule, ect.) puis de placer le résultat de ces transfromation dans les colonnes prévues à cet effet initialement de "B" à "CV" (exemple : à la ligne 1, je ne garderai que les 2 caractères situé en position 3 de la cellule CX3 et je collerai le résultat en B3.
3/ Un autre soucis que je rencontre, c'est que parfois j'obtiens la formule et non pas le résultat en chiffre.
Bref je galère et je ne trouve pas les solutions
Voici ce que j'ai fait
Sub Afficher_la_liste_des_fichiers_contenus_dans_le_dossier()
' Attribution des variables
' Chemin du dossier contenant les fichiers à exploiter
Static Chemin As String
' Chemin complet du fichier à exploiter
Static Dir_Fichier As String
' Noms des feuilles du classeur où seront importées les données
Static feuille_0 As String
Static feuille_1 As String
Static feuille_2 As String
' Nom du classeur qui contient les données
Static Filename As String
' Définition du Chemin d'accès au dossier et mis en placeholder par défaut
' ici Chemin vaut "C:\Users\... ...\Résultat"
Chemin = InputBox("Entrez le nom du chemin d'accès au dossier contenant les fichiers à exploiter SVP", _
"Etape 1 - Recherche et affichage des fichiers à exploiter pour cette affaire", "C:\Users\... ...\Résultat")
' Ajout de l'antislash sur le Chemin en cas d'oubli
If Right(Chemin, 1) <> "\" Then
Chemin = Chemin & "\"
End If
' Nommage du chemin d'accès complet
Dir_Fichier = Dir(Chemin)
' Attribution des valeurs à "i" et "j" pour définir le numéro de la première ligne où débutera l'affichage des résultats sur les feuilles (i pour feuille_2 et j pour feuille_3)
i = 3
j = 2
' Boucle pour crawler les fichiers un à un jusqu'au moment ou il n'y en a plus
While Dir_Fichier <> ""
Filename = Dir(Chemin & "*.xlsx")
MsgBox Filename
' Définition des noms des feuilles dans lesquelles les résultats seront inscrits
feuille_0 = "Instructions"
feuille_1 = "Liste des fichiers"
feuille_2 = "Tableau des résultats"
' Affectation des résultats dans les colonnes de la feuille "Liste des fichiers" (feuille_1)
Worksheets(feuille_1).Range("a" & j).Value = Chemin
Worksheets(feuille_1).Range("b" & j).Value = Dir_Fichier
' Définitions des zones d'affichage des résultats de la feuille "Liste des fichiers"
Worksheets(feuille_1).Range("a:b").Columns.AutoFit
' Définitions des zones d'affichage des résultats de la feuille "Tableau des résultats" (feuille_2)
Worksheets(feuille_2).Columns("a:a").ColumnWidth = 5
Worksheets(feuille_2).Columns("b:b").ColumnWidth = 14
Worksheets(feuille_2).Columns("c:c").ColumnWidth = 20
Worksheets(feuille_2).Columns("d:e").ColumnWidth = 17.5
Worksheets(feuille_2).Columns("f:f").ColumnWidth = 35
Worksheets(feuille_2).Columns("g:g").ColumnWidth = 40
Worksheets(feuille_2).Columns("h:j").ColumnWidth = 14
Worksheets(feuille_2).Columns("k:cv").ColumnWidth = 15
Worksheets(feuille_2).Range("a:cv").WrapText = True
Worksheets(feuille_2).Range("a:cv").HorizontalAlignment = xlCenter
Worksheets(feuille_2).Range("a:cv").VerticalAlignment = xlCenter
' Insertion automatique de l'id d'un fichier dans la feuille "Tableau des résultats"
Worksheets(feuille_2).Range("a" & i).Value = j - 1
' Do While FileName <> ""
' Workbooks.Open Chemin & FileName
' With Workbooks(Filename).Worksheets("Synthese_des_donnees")
' Numéro de compteur (col B), Commune (col C) et Axe (col F)
Workbooks("fichier_synthese_SIG.xlsm").Worksheets(feuille_2).Range("CX" & i).Value = Range("K5").Value
' Sens (col H)
Workbooks("fichier_synthese_SIG.xlsm").Worksheets(feuille_2).Range("CY" & i).Value = Range("L45").Value
' Année (col I)
Workbooks("fichier_synthese_SIG.xlsm").Worksheets(feuille_2).Range("CZ" & i).Value = Range("Z3").Value
' Vitesse (col J)
Workbooks("fichier_synthese_SIG.xlsm").Worksheets(feuille_2).Range("DA" & i).Value = Range("T36").Value
' Débit en S1 - Pourcentage - Tout véhicule TV (col K)
Workbooks("fichier_synthese_SIG.xlsm").Worksheets(feuille_2).Range("DB" & i).Value = Range("J41").Value
' End With
' Workbooks(FileName).Close SaveChanges:=False
' FileName = Dir
' Loop
' Scan du dossier pour afficher les résultats
Dir_Fichier = Dir()
'Itération de "i" puis de "j" pour continuer la boucle
i = i + 1
j = j + 1
Wend
End SubMerci par avance à ceux qui voudront bien m'aider
Bonjour,
On peut vous aider, mais tenant compte du fait que vous etes débutant en VBA, vous auriez tout intéret à regarder du coté de PowerQuery. C'est un outil intégré à Excel (accessible via l'onglet données), qui va vous permettre de réaliser vos extractions et synthèses de manière beaucoup plus simple, et avec une interface en plus, au lieu de lignes de code obscures.
Prenez le temps de lire/regarder 1 ou 2 tutoriels sur le sujet, il en existe foison. Je pense que travailler là-dessus serait plus profitable.
Je débute certes en VBA mais j'ai lu un paquet de cours dont tous ceux du site excel-pratique.com.
J'ai bien compris la mécanique des macros mais j'ai des soucis de compréhension avec certaines choses (nommage des différents classeurs sources et destinataires dans des boucles, variables qui ne se gardent pas d'une procédure à une autre malgré le Public, etc.)
Aujourd'hui par exemple :
Je cherche à copier la valeur d'un classeur "source" vers le classeur "synthèse" dont j'utilise le "Copy" et le "Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False" malheureusement 1 coup ça marche et je récupère ma value, 1 coup ça ne met rien et à la place, je me retrouve avec un cadre sélectionné dans ma feuille de x colonnes de large et de y lignes de long.
Je cherche donc des explication dans les notices du "Paste", "PasteSpecial" mais aussi de "Operation:=", " SkipBlanks:" etc. pour être certain que le bug ne vient pas de ces arguments mais il n'y a rien qui cloche !?
Au final je me dis que c'est sans doute parce que je copie une cellule fusionnée et que "Paste:=xlPasteValues" n'est pas adapté pour cela mais comment faire alors ?
Voici un bout de mon code (ps : je mets beaucoup de commentaires pour expliquer mais aussi pour garder en mémoire le code initialement écrit ;) )
' Copiage des données de la cellule K5
Range("K5").Copy
' Ne pas garder les valeurs en mémoire éternellement dans le Presse-papiers
Application.CutCopyMode = False
'Selection.Copy
Selection.Copy
' Retour sur classeur et activation du classeur et de la feuille de destination
Workbooks("fichier_synthese_SIG.xlsm").Activate
' Récupérer la dernière ligne vide de la feuille "Tableau des résultats" du classeur "fichier_synthese_SIG.xlsm"
'derligne1 = ActiveSheet.UsedRange.Rows.Count + 1
derligne1 = 3
' Position sur la cellule de la dernière ligne au niveau de la colonne "CX"
Range("CX" & derligne1).Select
' Collage des données
' ActiveSheet.Paste
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValuesRe,
Excusez-moi, je ne voulais pas remettre en cause votre connaissance de la programmation, mais vous rediriger simplement vers l'outil adapté à votre demande.
Pour ce qui est de "comprendre" les fonctions VBA. Dans l'éditeur de code (VBE) quand vous surlignez/sélectionnez une instruction puis appuyez sur "F1" cela vous ouvre la page d'aide correspondante. En l'occurence pour PasteSpécial Méthode Range.PasteSpecial (Excel) | Microsoft Learn. Cela répondra à 99% de vos questions.
Par ailleurs votre compréhension du copier/coller semble assez floue effectivement. La procédure à suivre est de définir le groupe de cellules à copier :
Range("A1:A2").CopyPuis la destination :
Range("B1:B2").PasteSpecial xlPasteAllVous noterez que Select/Selection est complètement superflu.
Enfin, je vous invite à utiliser l'enregistreur de macros pour avoir une trame, que vous adapterez ensuite.
Rebonjour,
J'avais bien lu vos instructions sur PowerQuery mais sur mon 365 et pour commencer, je n'ai pas d'onglet et c'est une fenêtre à part qui s'ouvre comme un nouveau soft. Bref si je dois en plus apprendre à connaître ce nouveau soft pendant encore 2 semaines (via des tutos que je devrais quoiqu'il en soit adapter et/ou tester) avant de réussir simplement à aspirer des données d'un classeur vers un autre via une macro, autant payer un développeur qui me fera une trame en moins de 10 minutes avec 3 exemples d'aspiration et je finirai le reste... car j'en ai 93 à faire par classeur.
Mais comme j'aime comprendre ce qui ne va pas dans un code et que je suis assez perséverant, je cherche...
S'agissant de vos conseils, j'ai utilisé l'enregistreur mais il ne m'aide pas puisque les fichiers appelés sont aléatoires et peuvent se trouver dans un dossier qui n'a pas le même nom (d'où un inpuBox pour demander le chemin au départ).
De plus la macro ainsi réalisée via l'enregistreur me donne des formules Excel et non pas VBA. Résultat, ça plante derrière avec des erreurs en pop up.
Exemple avec : "Windows" au lieu de "Workbooks", "texte.avant(texte.apres(..))'" au lieu de Mid, etc.
Bref, si cela m'a aidé à comprendre certaines choses, ça ne fait pas tout :(
Sinon, j'ai repris vos indications mais rien n'y change, la value collectée dans mon premier classeur ne se colle pas dans mon classeur synthèse et j'ai cette fois une erreur d'execution '1004': La méthode PasteSpecial de la classe Range a échoué
C'est assez épuisant ces bugs qui se suivent et que je ne comprends pas davantage...
Voici le nouveau code :
' Numéro de compteur (col B), Commune (col C) et Axe (col F)
' Copiage des données de la cellule K5
Range("K5").Copy
' Ne pas garder les valeurs en mémoire éternellement dans le Presse-papiers
Application.CutCopyMode = False
' Selection.Copy
' Selection.Copy
' Retour sur classeur et activation du classeur et de la feuille de destination
Workbooks("fichier_synthese_SIG.xlsm").Activate
' Récupérer la dernière ligne vide de la feuille "Tableau des résultats" du classeur "fichier_synthese_SIG.xlsm"
' derligne1 = ActiveSheet.UsedRange.Rows.Count + 1
derligne1 = 3
' Position sur la cellule de la dernière ligne vide au niveau de la colonne "CX"
' Range("CX" & derligne1).Select
' Collage des données
' ActiveSheet.Paste
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Selection.PasteSpecial Paste:=xlPasteValues
Range("CX" & derligne1).PasteSpecial Paste:=xlPasteValues
' Sens (col H) ** Si L45 vide alors SU
' Retour au classeur source
Workbooks(Nameclass).Activate
' Copiage des données de la cellule L45
Range("L45").Copy
' Ne pas garder les valeurs en mémoire éternellement dans le Presse-papiers
Application.CutCopyMode = FalseSi je mets :
Range("CX" & derligne1).Paste
à la place de :
Range("CX" & derligne1).PasteSpecial Paste:=xlPasteValues
J'ai cette fois une erreur d'execution 438 Propriété ou méthode non gérée par cet objet
Je ne vais jamais m'en sortir...
Bonjour TopChrono
Error 438: Object doesnt support this property or method
Je ne travaille jamais avec des .Activate
Lorsqu'il y a des propriétés d'objets qui s'enchainent comme dans la plupart des cas, le tien y compris, je me dis que tout n'est pas testé.
Donc quand je travaille avec des classeurs, des feuilles et des plages de données, j'ai des déclarations "As Workbook", "As Worksheet", "As Range".
A chaque étape je teste si l'objet est initialisé, : "Is Nothing Then" et dans le else je mets un Msgbox "erreur d'initialisation de ..."
Bien, bon courage. J'appuie la remarque de scraper.
Par ailleurs je trouve fort dommage que vous ne daignez jeter un oeil à PowerQuery. Vous faites partie de ces personnes qui ont peur de tout ce qui est nouveau et s'embourbent dans de vieux outils désuets… Si Excel évolue en intégrant de nouveaux outils, ce n'est pas par hasard… Je pense très sincèrement que si vous aviez passé le temps que vous avez perdu sur vos quelques lignes VBA à découvrir PQ vous auriez déjà résolu le problème. De nombreux utilisateurs ont découvert PQ sur le forum et ont été surpris de sa facilité d'utilisation de ce que j'ai pu observer.
Mais bon, l'attrait des macros sujettes à planter au moindre changement de nom de feuille ou déplacement d'une colonne à la vie dure…
Si vous voulez une solution VBA il va falloir envoyer des fichiers d'exemples (sources et résultats), vos explications sont floues. Pensez également à mettre quelques lignes d'exemple du résultat voulu.
Je ne sais pas si j'ai bien saisi ton information...
Concrètement, tu préfèrerais que je déclare des variables telles que :
Dim ClasseurSource As Worksbook
Dim ClasseurSourceFeuil As Worksheet
Dim ClasseurSourceFeuilcell_K5 As Range
Dim ClasseurSourceFeuilcell_L45 As Range
....
et ensuite que j'affecte une valeur à chacun
ClasseurSource = "fichier_synthese_SIG.xlsm"
ClasseurSourceFeuil = "Tableau des résultats"
ClasseurSourceFeuilcell_K5 = "K5"
ClasseurSourceFeuilcell_L45 = "L45"
...
pour enfin les appeler :
Workbooks(ClasseurSource).Activate au lieu de Workbooks("fichier_synthese_SIG.xlsm").Activate
Workbooks(ClasseurSource).Worksheet(ClasseurSourceFeuil) au lieu de Workbooks("fichier_synthese_SIG.xlsm"). Worksheet("Tableau des résultats")
etc.
C'est bien cela ?
Bonjour TopChrono
OK pour les définitions
Dim ...
Il faut réfléchir un peu
Set ClasseurSource = Workbooks.Open("fichier_synthese_SIG.xlsm")
Set ClasseurSourceFeuil = ClasseurSource.Sheets("Tableau des résultats")
Set ClasseurSourceFeuilcell_K5 = ClasseurSourceFeuil.Range("K5")
Set ClasseurSourceFeuilcell_L45 = ClasseurSourceFeuil.Range("L45")
...
Il faut apprendre à utiliser le débugueur VBE Visual Basic engine
Debogage / ajouter un espion
Debogage / Compiler ---> Erreurs à corriger
Affichage / fenetre espion ---> Variable sans affectations : Set var = ... Visualisé comme Nothing necessite alors d'ajouter une instruction If var... Is Nothing Then
Debogage / pas à pas (F8)
Debogage / basculer point d'arret (F9
Debogage / Executer (F5) --> jusqu'au point d'arrêt