Incrémentation de données vers autre fichier
Bonjour à tous,
J'ai besoin de votre aide pour une macro que je n'arrive pas à réaliser ...
Je vous explique : j'ai 15 fichiers, tous conçu de la même manière, avec un tableau qui commence en C15 et qui se termine en AWXX (tous les tableaux ne font pas le même nombre de ligne, ça oscille entre 150 et 850).
J'ai un fichier global qui doit regrouper une partie des informations de chaque fichier, je souhaiterais donc intégrer une macro aux 15 fichiers (avec un bouton lié ça peut être bien), pour intégrer les données que je souhaite.
J'ai créé dans un premier temps cette macro :
Sub copie_vers_ESC()
Dim ESC As Workbook
Set ESC = Workbooks("TEST INCREMENTATION ESC.xlsm")
ESC.Worksheets("ESC").Range("D15") = ThisWorkbook.Worksheets("Tableau").Range("AG15").Value
ESC.Worksheets("ESC").Range("E15") = ThisWorkbook.Worksheets("Tableau").Range("AH15").Value
ESC.Worksheets("ESC").Range("F15") = ThisWorkbook.Worksheets("Tableau").Range("AI15").Value
ESC.Worksheets("ESC").Range("G15") = ThisWorkbook.Worksheets("Tableau").Range("AJ15").Value
ESC.Worksheets("ESC").Range("H15") = ThisWorkbook.Worksheets("Tableau").Range("AK15").Value
ESC.Worksheets("ESC").Range("I15") = ThisWorkbook.Worksheets("Tableau").Range("AL15").Value
ESC.Worksheets("ESC").Range("J15") = ThisWorkbook.Worksheets("Tableau").Range("K15").Value
ESC.Worksheets("ESC").Range("M15") = ThisWorkbook.Worksheets("Tableau").Range("C15").Value
ESC.Worksheets("ESC").Range("o15") = ThisWorkbook.Worksheets("Tableau").Range("AQ15").Value
ESC.Worksheets("ESC").Range("p15") = ThisWorkbook.Worksheets("Tableau").Range("AR15").Value
ESC.Worksheets("ESC").Range("q15") = ThisWorkbook.Worksheets("Tableau").Range("AS15").Value
ESC.Worksheets("ESC").Range("t15") = ThisWorkbook.Worksheets("Tableau").Range("F15").Value
ESC.Worksheets("ESC").Range("u15") = ThisWorkbook.Worksheets("Tableau").Range("G15").Value
ESC.Worksheets("ESC").Range("v15") = ThisWorkbook.Worksheets("Tableau").Range("H15").Value
ESC.Worksheets("ESC").Range("w15") = ThisWorkbook.Worksheets("Tableau").Range("E15").Value
End Sub
Elle fonctionne bien, les valeurs des cellules du tableau dans lequel j'ai ma macro s'intègre bien, dans les bonne cellules, dans mon fichier global.
Cependant, et vous l'aurez deviner, cette macro n'est vraiment que le début de ce que je souhaite faire ...
Il faudrait que ma macro aille chercher toutes les données des colonnes AG, AH, AI, etc ... pour les réintégrer dans mon fichier de base.
Autre difficulté : je souhaite ne copier les cellules que si il y a la valeur ESC dans la colonne AE de mon fichier exportateur de données.
Par exemple : je ne veux que copier les cellules AG30, AH30, AI30 etc ..., si il n'y a marqué ESC uniquement en AE30
De plus, 15 fichiers = 15 macros. Donc, si je lance la macro sur le fichier 2, il ne faut pas que les données du fichier 1 qui ont déjà été intégrées soient écrasées pour autant. Il faudrait que les données du fichier 2 apparaissent après celles du fichier 1, que celles du fichiers 3 apparaissent après celles du fichier 2, etc ...
A l'idéal, il faudrait que cela se fasse également si il y a des filtres, des tris etc sur le fichier global.
Dernière chose à savoir : le tableau global n'est pas une machine à gaz. Il devrait au total n'y avoir que 200 lignes maximum environ (Car au maximum 200 fois marqué "ESC" dans les colonnes AE de tous les fichiers)
J'espère que cela est faisable, merci de m'éclairer ...
A bientôt !
Bonjour,
Une petite réflexion minimale indique généralement que : une macro dans le fichier cible, capable de traiter les 15 fichiers source, est un schéma plus productif que 15 macros, chacune dans son fichier source !
Conseils (au vu des quelques lignes de code mentionnées) :
- Apprendre à utiliser les blocs With... End With
- Apprendre à faire des boucles.
Cordialement.
Bonjour MFerrand,
Merci pour votre réponse.
Je pensais qu'il était plus simple de faire comme ceci...
Si je met une unique macro dans le fichier global, j'imagine qu'il va falloir qu'elle ouvre un à un les 15 fichiers source ? Le problème est qu'une macro se lance à chaque ouverture des fichiers source (avec des msgbox et tout), donc si l'utilisateur doit renseigner les msgbox des 15 fichiers pour importer les données, ça va être un peu compliqué ...
Cependant vous êtes bien plus expérimenté que moi, il existe peut-être un moyen de ne pas activer toutes ces msgbox pour aller chercher les infos ... ?
J'ai déjà lu quelques cours sur les boucles etc, mais j'ai beaucoup de mal. Je vais essayer de m'y remettre.
Cordialement,
On peut désactiver l'activation des macros à l'ouverture par progammation.
Je ne l'ai jamais utilisée, mais c'est Microsoft qui le dit !
Effectivement, avec Application.EnableEvents = False
J'essaye donc d'avancer sur la macro. J'écoute donc votre conseil et je vais réaliser une seule macro sur mon fichier source.
Je pense cependant que je vais vite retourner vers vous ...
Merci.
Cordialement,
Pas à ça que je pensais ! Mais à Application.AutomationSecurity
Mais Suspendre les évènements peut suffire à empêcher Workbook_Open de s'exécuter.
Le fichier source s'ouvre, prend les infos, puis se referme.
Donc il y a effectivement juste workbook_open qui ne doit pas s'exécuter.
ApplicationEnableEvents = false me permet d'effectuer cela à priori ...
Voici mon code pour le moment :
Sub Incrémentation_ESC()
Dim AR As Workbook
Application.EnableEvents = False
Set AR = Application.Workbooks.Open("P:\Dossiers Communs\TEST INCREMENTATION AR.xlsm")
Application.EnableEvents = True
For i = 15 To 900
If AR.Worksheets("Tableau").Range("AE&i").Value = "ESC" Then
ThisWorkbook.Worksheets("ESC").Range("D" & i) = AR.Worksheets("Tableau").Range("AG" & i).Value
ThisWorkbook.Worksheets("ESC").Range("E" & i) = AR.Worksheets("Tableau").Range("AH" & i).Value
ThisWorkbook.Worksheets("ESC").Range("F" & i) = AR.Worksheets("Tableau").Range("AI" & i).Value
ThisWorkbook.Worksheets("ESC").Range("G" & i) = AR.Worksheets("Tableau").Range("AJ" & i).Value
ThisWorkbook.Worksheets("ESC").Range("H" & i) = AR.Worksheets("Tableau").Range("AK" & i).Value
ThisWorkbook.Worksheets("ESC").Range("I" & i) = AR.Worksheets("Tableau").Range("AL" & i).Value
ThisWorkbook.Worksheets("ESC").Range("J" & i) = AR.Worksheets("Tableau").Range("K" & i).Value
ThisWorkbook.Worksheets("ESC").Range("m" & i) = AR.Worksheets("Tableau").Range("C" & i).Value
ThisWorkbook.Worksheets("ESC").Range("o" & i) = AR.Worksheets("Tableau").Range("AQ" & i).Value
ThisWorkbook.Worksheets("ESC").Range("p" & i) = AR.Worksheets("Tableau").Range("AR" & i).Value
ThisWorkbook.Worksheets("ESC").Range("q" & i) = AR.Worksheets("Tableau").Range("AS" & i).Value
ThisWorkbook.Worksheets("ESC").Range("t" & i) = AR.Worksheets("Tableau").Range("F" & i).Value
ThisWorkbook.Worksheets("ESC").Range("u" & i) = AR.Worksheets("Tableau").Range("g" & i).Value
ThisWorkbook.Worksheets("ESC").Range("v" & i) = AR.Worksheets("Tableau").Range("h" & i).Value
ThisWorkbook.Worksheets("ESC").Range("w" & i) = AR.Worksheets("Tableau").Range("e" & i).Value
End If
Next
AR.Close False
End Sub
Malheureusement il ne fonctionne pas ... De plus, même s'il fonctionnait, il ne me conviendrait pas vraiment. Il s'agit là d'un début.
L'erreur d'exécution 1004 : Erreur définie par l'application ou par l'objet.
Clairement, l'aide ne m'a pas vraiment aidé ...
A savoir : le code marchait bien avant que j'inscrive la boucle for i ...
Si il fonctionnait, il n'afficherait pas les lignes sur mon fichier cible à la suite, mais avec de grands trous, étant donné qu'il n'y a que 3 ou 4 fois "ESC" sur mes 900 lignes ...
Comment faire face à ça ? Il faudrait que je fasse une autre boucle for à l'intérieur de la première ?
En parlant des "900", j'ai mis ce nombre car aucun de mes fichiers ne fait plus de 900 lignes. Mais j'imagine qu'il y a une solution plus adéquate ... qui éviterait que la macro cherche sur les 900 lignes dans les fichiers qui n'en possèdent que 150 ...
Merci beaucoup pour votre aide.
Cordialement,
Ça je ne peux pas te dire ! Je ne l'ai pas lue !
Son aspect est toujours le même : une longue énumération imbuvable et toujours pas indentée...
Si tu lui fait faire autre chose que ce que tu doit faire, il est évident que ce sera inadapté !
Longue énumération oui, mais je ne sais pas comment faire autrement.
Et si mon code est indenté
Sub Incrémentation_ESC()
Dim AR As Workbook
Application.EnableEvents = False
Set AR = Application.Workbooks.Open("P:\Dossiers Communs\TEST INCREMENTATION AR.xlsm")
Application.EnableEvents = True
For i = 15 To 900
If AR.Worksheets("Tableau").Range("AE"& i).Value = "ESC" Then
With ThisWorkbook.Worksheets("ESC")
.Range("D" & i) = AR.Worksheets("Tableau").Range("AG" & i).Value
.Range("E" & i) = AR.Worksheets("Tableau").Range("AH" & i).Value
.Range("F" & i) = AR.Worksheets("Tableau").Range("AI" & i).Value
.Range("G" & i) = AR.Worksheets("Tableau").Range("AJ" & i).Value
.Range("H" & i) = AR.Worksheets("Tableau").Range("AK" & i).Value
.Range("I" & i) = AR.Worksheets("Tableau").Range("AL" & i).Value
.Range("J" & i) = AR.Worksheets("Tableau").Range("K" & i).Value
.Range("m" & i) = AR.Worksheets("Tableau").Range("C" & i).Value
.Range("o" & i) = AR.Worksheets("Tableau").Range("AQ" & i).Value
.Range("p" & i) = AR.Worksheets("Tableau").Range("AR" & i).Value
.Range("q" & i) = AR.Worksheets("Tableau").Range("AS" & i).Value
.Range("t" & i) = AR.Worksheets("Tableau").Range("F" & i).Value
.Range("u" & i) = AR.Worksheets("Tableau").Range("g" & i).Value
.Range("v" & i) = AR.Worksheets("Tableau").Range("h" & i).Value
.Range("w" & i) = AR.Worksheets("Tableau").Range("e" & i).Value
End With
End If
Next
AR.Close False
End SubC'est sans doute plus claire maintenant, désolé
Oui je ne m'attends pas à ce qu'il fasse tout tout de suite.
Mais étant donné que je suis débutant, j'y vais step by step ...
Ce code fonctionne, c'était une erreur bête. Mais comme expliqué, il ne répond pas vraiment à ma demande et je me retrouve un peu coincé ...
C'est un peu mieux indenté ainsi, il manque juste un retrait de tout le code exécutable par rapport à la marge : ça sert à voir immédiatement la présence d'une étiquette de branchement (qui elle sera à la marge, sur l'alignement Sub/End Sub).
Et sans ligne sautée ce serait encore mieux car on pourrait mieux suivre les alignements...
Mais l'essentiel reste d'analyser le problème pour mettre en place les éléments nécessaires à la réalisation projetée.
Tu as un classeur cible dans lequel tu veux intégrer des données de 15 classeurs source. Il faut donc envisager une première boucle pour ouvrir tour à tour chaque classeur, opérer puis le refermer. Ce qui implique probablement, outre la variable compteur de boucle, une variable pour le chemin des classeurs à ouvrir, une liste des noms de classeurs à mettre en variable tableau.
On a donc l'armature extérieure, sur chaque classeur ensuite, il faudra vraisemblablement dimensionner le nombre de lignes à parcourir (une variable pour cela et une variable pour la boucle). Sur chaque ligne on teste vraisemblablement si un critère indique qu'il faut prélever des valeurs : test. Si la condition est satisfaite on prélève : il faut savoir ce qui est à prélever pour remplir une ligne de la cible, si c'est une zone continue ou non. Si la zone n'est pas continue, une variable tableau permettant de la parcourir en boucle s'impose. Puis on ne va pas opérer façon tortue ligne par ligne. Si plusieurs sont à prélever, on en constitue un tableau qui pourra être affecté d'un seul coup à la cible.
Comme tu n'as pas fourni toutes les indications utiles, on est un peu dans le vague... Une fois définies toutes les variables nécessaires (sans inflation inutile : la variable utilisée pour le dimensionnement sert pour tous les classeurs source et également pour trouver le positionnement dans le classeur cible par exemple, une variable pour élément que l'on peut mettre sous bloc With... End With est inutile...)
A ce stade on passe à l'écriture...
Merci MFerrand pour votre réponse complète.
Qu'est-ce qu'une étiquette de branchement ... ?
Tu as un classeur cible dans lequel tu veux intégrer des données de 15 classeurs source. Il faut donc envisager une première boucle pour ouvrir tour à tour chaque classeur, opérer puis le refermer. Ce qui implique probablement, outre la variable compteur de boucle, une variable pour le chemin des classeurs à ouvrir, une liste des noms de classeurs à mettre en variable tableau.
J'avais effectivement penser à la boucle qui permettrait d'ouvrir tout à tour chaque classeur, je ne vais pas recopier le code 15 fois ...
La difficulté réside, vous l'avez dit, dans les différents chemins/noms des fichiers à ouvrir. D'autant plus que chacun des fichiers sont dans des répertoires différents. Je reprends votre message : "la variable compteur de boucle" > il s'agit d'une boucle for X = 1 to 15 pour les 15 fichiers ? "Une liste des noms de classeurs à mettre en variable tableau" > C'est à dire ... ? Il faut que je liste tous les chemins et tous les fichiers des 15 fichiers source, j'en ai bien conscience. Mais je ne sais pas comment le mettre en forme ...
On a donc l'armature extérieure, sur chaque classeur ensuite, il faudra vraisemblablement dimensionner le nombre de lignes à parcourir (une variable pour cela et une variable pour la boucle). Sur chaque ligne on teste vraisemblablement si un critère indique qu'il faut prélever des valeurs : test. Si la condition est satisfaite on prélève : il faut savoir ce qui est à prélever pour remplir une ligne de la cible, si c'est une zone continue ou non. Si la zone n'est pas continue, une variable tableau permettant de la parcourir en boucle s'impose. Puis on ne va pas opérer façon tortue ligne par ligne. Si plusieurs sont à prélever, on en constitue un tableau qui pourra être affecté d'un seul coup à la cible.
La même chose ? Je devrais, dans ma liste de fichier et de chemin, renseigné le nombre de ligne à parcourir (de 15 à XXX) ? Le problème est que le nombre de ligne maximum est susceptible de changer.
Concernant le test : celui de mon code n'est pas satisfaisant ? Comme tu peux le voir sur mon code, la zone n'est pas vraiment continu, c'est aussi l'une des difficultés. Plutôt que de faire comme j'ai fait, tu me conseilles donc une nouvelle fois de passer par une variable tableau ?
C'est sûr que si cette méthode est plus rapide que ma méthode tortue, je suis preneur. J'imagine que la macro ne va pas s'exécuter en 5 secondes, d'autant plus que les fichiers source sont assez imposant.
Comme tu n'as pas fourni toutes les indications utiles, on est un peu dans le vague... Une fois définies toutes les variables nécessaires (sans inflation inutile : la variable utilisée pour le dimensionnement sert pour tous les classeurs source et également pour trouver le positionnement dans le classeur cible par exemple, une variable pour élément que l'on peut mettre sous bloc With... End With est inutile...)
Quelles informations utiles n'ai-je pas fourni ?
Quant à la parenthèse ... Je ne la comprends pas ...
En tous les cas, merci pour votre réponse qui m'aide à réfléchir façon codage, ce qui n'est pas naturel pour moi, même si je suis encore complètement dans le flou en ce qui concerne cette macro qui est très importante pour moi.
Cordialement,
1) Exemple d'étiquette dans le cadre d'une gestion d'erreur :
Sub Macro()
'instructions
On Error GoTo erreur
'instruction susceptible de générer une erreur qu'on peut récupérer
On Error GoTo 0
'instructions
Exit Sub
erreur:
'instructions correctives (éliminant l'erreur)
Resume Next
End SubPar exemple, on doit transférer des données dans un classeur et le créer s'il n'existe pas encore. On va utiliser une gestion d'erreur pour traiter la question, si on met une instruction destinée à ouvrir le classeur et qu'il n'existe pas, on aura une erreur. On fait précéder cette commande par une instruction On Error GoTo... Si l'erreur se produit le programme ira se brancher sur l'étiquette stipulée après ...GoTo, soit erreur.
Cette étiquette figure dans la procédure sous la forme : erreur:
Quel que soit l'endroit où tu la tapes, VBA la place automatiquement à la marge.
Le programme se branche donc sur l'étiquette, les instructions qui suivent créent le classeur manquant, ce qui élimine l'erreur, et renvoient la poursuite du programme à la ligne suivant celle ayant déclenché l'erreur (Resume Next), qui va tomber ici sur l'élimination du gestionnaire d'erreurs (...GoTo 0) [si une erreur survient par la suite, cela arrêtera l'exécution]
Ce qui était visé dans l'illustration, c'est qu'au premier coup d'oeil sur le code tu vois qu'il y a une étiquette si (entre Sub et End Sub)
si seul le erreur: se trouve au même niveau. Ce qui fait gagner énormément de temps, car tu n'as plus qu'à repérer les GoTo pour savoir ce qui déclencher les branchements sur cette étiquette. Alors que si tu ne l'as pas repérée au départ, tu auras à la chercher en tombant sur un GoTo... J'applique l'indentation préconisée lors de la mise en place de VBA dans les années 90, je n'ai jamais eu à m'en plaindre, et depuis que je me suis livré à quelques chronométrages qui m'ont montré que je mettais 4 fois moins de temps en moyenne à lire en l'interprétant du code indenté de cette façon que du code non ou mal indenté, cela me fait rager de perdre du temps inutilement !
Il faut que je liste tous les chemins et tous les fichiers des 15 fichiers source, j'en ai bien conscience. Mais je ne sais pas comment le mettre en forme ...
Dans un tel cas, tu crées une liste sur ton classeur cible, des chemins et noms de fichiers qui devront être ouverts, liste qu'on ira récupérer sous forme de tableau.
Le problème est que le nombre de ligne maximum est susceptible de changer
On repère facilement la dernière ligne utilisée dans chaque fichier...
la zone n'est pas vraiment continu
En constituant par exemple un tableau des colonnes à prélever sur la ligne indiquée, on parcourt ce tableau de façon à renvoyer successivement l'élément du tableau indiquant la colonne...
Quand on ne peut faire de boucle de façon directe, un tableau permet de le faire indirectement... On peut toujours faire des boucles !
Quelles informations utiles n'ai-je pas fourni ?
Quant à la parenthèse ... Je ne la comprends pas ...
Tes informations sont un peu désordonnées, pour écrire la macro il faut des références précises, et exhaustives.
La parenthèse concerne l'économie de variables à utiliser...
Il me semblait clair que devant chercher la dernière ligne dans chaque classeur source, puis dans le classeur cible pour positionner l'insertion, ces recherches étant succesives dans le déroulement, quand on doit en faire une on n'a aucun besoin de conserver la précédente, une seule variable suffit donc pour ces recherches successives !
Et l'autre exemple que j'ai donné : quand tu peux à l'ouverture d'un classeur source mettre :
With Workbooks.Open(chemin&nomclasseur)tu n'as aucun besoin d'une variable classeur, l'instruction With le met en mémoire dans les mêmes conditions qu'une variable et l'accés sera aussi rapide, sinon plus dans certains cas.
Cordialement.
D'accord, merci pour ces explications.
Je vais donc essayer de déclarer les variables nécessaires, créer ma table avec les chemins et le nom des fichiers, mettre en ordre tout ça dans ma tête. Tout ça demain matin.
Je reviendrai ensuite vers vous.
Merci encore et bonne soirée !
Si je résume, j'ai besoin de X variable :
Der_ligne : qui sert à chercher la dernière ligne de chacun de mes fichiers sources. Le test va donc s'effectuer jusqu'à Der_ligne dans tous les fichiers. Cette variable va également permettre de déterminer jusqu'où est rempli le fichier source. Et là, les données vont se copier à Der_ligne + 1. C'est bien ça ? C'est .End(xlup).Row qui permet ça, mais je sais vraiment pas comment faire ...
Il faut récupérer sous forme de tableau les cellules T2 à T16 de l'onglet "Tables", il contient "chemin&nom" de tous les fichiers source, et créer une boucle pour que l'ensemble de la macro s'effectue sur le fichier inscrit en T2, puis en T3, T4, etc.
Créer ensuite une autre boucle pour que le test s'effectue sur toute les lignes des classeurs cibles.
Il faut également récupérer sous forme de tableau à 2 colonnes j'imagine ... D'un coté les colonnes source, de l'autre coté les colonnes cibles, c'est ça ? Histoire de ne pas avoir à recopier tout ce que je veux dans la macro ?
Dois-je nommer ces tableaux ?
tu n'as aucun besoin d'une variable classeur, l'instruction With le met en mémoire dans les mêmes conditions qu'une variable et l'accés sera aussi rapide, sinon plus dans certains cas.
Ok donc j'ai bien compris, mon Dim AR As Workbook sert à rien.
J'ai essayer de le retirer, et d'adapter mon code avec With Workbooks.Open(chemin&nomclasseur), comme tu me l'as conseillé, mais je n'arrive pas à le faire fonctionner ...
Edit :
Sub Incrémentation_ESC()
Dim AR As Workbook
Dim der_ligne As Integer
Application.EnableEvents = False
Set AR = Application.Workbooks.Open("P:\Dossiers Communs\TEST INCREMENTATION AR.xlsm")
Application.EnableEvents = True
der_ligne = Range("AE" & Rows.Count).End(xlUp).Row
MsgBox der_ligne
For i = 15 To der_ligne
If AR.Worksheets("Tableau").Range("AE" & i).Value = "ESC" Then
With ThisWorkbook.Worksheets("Suppléance Centrale")
.Range("D" & i) = AR.Worksheets("Tableau").Range("AG" & i).Value
.Range("E" & i) = AR.Worksheets("Tableau").Range("AH" & i).Value
.Range("F" & i) = AR.Worksheets("Tableau").Range("AI" & i).Value
.Range("G" & i) = AR.Worksheets("Tableau").Range("AJ" & i).Value
.Range("H" & i) = AR.Worksheets("Tableau").Range("AK" & i).Value
.Range("I" & i) = AR.Worksheets("Tableau").Range("AL" & i).Value
.Range("J" & i) = AR.Worksheets("Tableau").Range("K" & i).Value
.Range("m" & i) = AR.Worksheets("Tableau").Range("C" & i).Value
.Range("o" & i) = AR.Worksheets("Tableau").Range("AQ" & i).Value
.Range("p" & i) = AR.Worksheets("Tableau").Range("AR" & i).Value
.Range("q" & i) = AR.Worksheets("Tableau").Range("AS" & i).Value
.Range("t" & i) = AR.Worksheets("Tableau").Range("F" & i).Value
.Range("u" & i) = AR.Worksheets("Tableau").Range("g" & i).Value
.Range("v" & i) = AR.Worksheets("Tableau").Range("h" & i).Value
.Range("w" & i) = AR.Worksheets("Tableau").Range("e" & i).Value
End With
End If
Next
AR.Close False
End SubCe code, même s'il n'a pas pris en compte ta remarque sur le fait que la variable qui définit mes fichiers sources est inutile, la macro se réalise bien jusqu'à la dernière ligne de mon tableau, c'est niquel.
Cependant, je ne vois pas comment cette seule variable peut être également utilisé pour mon fichier cible. Car la colonne AE ne correspond à rien dans celui-ci. Je ne vois pas comment faire autrement que créer une seconde variable, identique sauf qu'il ne prend pas la colonne AE pour référence, mais une autre colonne, qui elle, a du sens dans mon fichier cible ...
Bonjour,
Exemple indicatif, tiré des éléments de ton code initial et de tes indications... J'ai pris l'option que tu transférais toutes les lignes, n'ayant pas vu de critère sélectif de lignes à transférer jusqu'ici...
Sub VersESC()
Dim TT(), rgC, rgS, wbS, ar%, n%, i%, j%
rgC = Array(4, 5, 6, 7, 8, 9, 10, 13, 15, 16, 17, 20, 21, 22, 23)
rgS = Array(33, 34, 35, 36, 37, 38, 11, 3, 43, 44, 45, 6, 7, 8, 5)
wbS = ThisWorkbook.Worksheets("Tables").Range("T2:T16").Value
Application.ScreenUpdating = False
For ar = 1 To UBound(wbS)
With Workbooks.Open(wbS(ar, 1))
With .Worksheets("Tableau")
n = .Cells(.Rows.Count, 3).End(xlUp).Row
ReDim TT(15 To n, 14)
For i = 15 To n
For j = 0 To 14
TT(i, j) = .Cells(i, rgS(j))
Next j
Next i
End With
.Close False
End With
With ThisWorkbook.Worksheets("ESC")
n = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
For i = 15 To UBound(TT, 1)
For j = 0 To 14
.Cells(n + i - 15, rgC(j)) = TT(i, j)
Next j
Next i
End With
Next ar
End SubTT est un tableau dynamique destiné à recueillir les résultats à transférer.
rgC, variable de type Variant : on lui affecte un tableau des numéros de colonnes à servir sur la feuille Cible.
rgS, variable de type Variant : on lui affecte un tableau des numéros de colonnes à prélever sur la feuille Source.
Ces tableaux de 15 éléments sont naturellement indicés de 0 à 14.
wbS, variable de type Variant : on lui affecte les valeurs de la plage contenant les chemins et noms de fichiers (ne pas omettre l'extension dans les noms de fichiers).
wbS contient donc un tableau qui, résultant de l'affectation des valeurs d'une plage de cellule, sera à deux dimensions : première dimension correspondant aux lignes, indicée de 1 à 15, deuxième dimension, correspondant aux colonnes, indicée de 1 à 1.
On initialise une boucle d'ouverture des classeurs sources : variable ar de 1 à 15 (en mettant UBound(wbS) au lieu de 15, on se réserve la possibilité de faire varier le nombre de classeur sans avoir à retoucher le code ici, mais on aura à modifier les réf. de plage, à moins de la définir par un nom dynamique... fignolages à voir ultérieurement selon contexte...)
A chaque tour de boucle on ouvre un classeur : wbS(ar, 1) [chemin et nom]. On l'ouvre d'emblée sous bloc With. Avant le End With on le fermera sans enregistrer.
On initialise un second bloc With pour la feuille source. On cherche la ligne de fin (en col. C), affectée à n.
On dimensionne le tableau TT: TT(15 To n, 14). La première dimension correspond aux lignes, on opère à partir de 15 jusqu'à la dernière, on dimensionne donc 15 To n : on aura ainsi une correspondance directe de l'indice et du compteur de boucle ligne. La seconde correspond aux colonnes à prélever : on dimensionne à 14, soit 0 à 14, indice correspondant à ceux du tableau des numéros de colonnes déjà établi.
On n'a plus qu'à opérer le prélèvement au moyen de deux boucles imbriquées.
La première (variable i) parcourt les lignes de 15 à n : cela permet de servir les éléments (i, ?) du tableau avec les valeurs de .Cells(i, ?)
La seconde (variable j) parcourt le tableau de n° de colonnes de 0 à 14 : cela permet de servir les éléments (?, j) du tableau avec les valeurs de .Cells(?, rgS(j)).
Les valeurs à récupérer étant dans le tableau TT, on ferme le classeur source et on passe à la cible, sous bloc With également (pourquoi s'en priver ? Cela fait partie de moyens d'accélerer VBA... !)
On cherche la dernière ligne de la feuille cible en colonne D et on affecte le numéro de la ligne suivante à n.
Si la plage cible avait été d'un seul tenant, on aurait pu faire une affectation directe du type : plageCible.Value = TT.
La plage étant discontinue, on affecte au moyen de deux boucles imbriquées, à l'instar de l'opération de prélèvement.
Première boucle (i), par ligne : on doit affecter de la ligne n à ? les valeurs du tableau d'indice 15 à finTableau. On fait donc varier i de 15 à UBound(TT, 1) qui permettra d'affecter TT(i, ?) aux cellules .Cells(n + i - 15, ?) [calcul arithmétique simple : i - 15 ira de 0 au nb de lignes à servir -1, il suffit donc d'y ajouter n pour avoir le numéro de ligne à servir]
Seconde boucle (j), par colonne : on est dans les mêmes conditions que précédemment puisqu'on utilise un tableau des numéros de colonnes à servir similaire à celui des numéros de colonnes à prélever, donc parcours du tableau colonnes de 0 à 14, qui permet d'affecter à .Cells(?, rgC(j)) les éléments TT(?, j).
Quand aura défilé les 15 fichiers, ce sera fini.
Cordialement.
Merci beaucoup !
Je vais analyser ça, relire plusieurs fois afin de comprendre, essayer, et te tenir au courant.
Cependant, j'ai assez peur de lancer la macro car j'imagine qu'elle va prendre beaucoup de temps, étant donné que la condition n'est pour le moment pas prise en compte ... Elle va donc recopier toutes les lignes, ce qui va, je pense, prendre énormément de temps.
La condition pour que les lignes s'incrémentent est la suivante :
je ne recopie que les lignes pour lesquelles il est inscrit en cellule AE "ESC" (qui est le résultat d'une formule, je ne sais pas si ça change quelque chose).
Si en AE134 il est inscrit ESC, alors je recopie AG154 du fichier source en DX de mon fichier cible, AH154 en EX, etc.
If AR.Worksheets("Tableau").Range("AE" & i).Value = "ESC" ThenC'est cette ligne là qui effectuait cela dans mon code (et ça fonctionnait)
Dans ton code :
wbS = ThisWorkbook.Worksheets("Tables").Range("T2:T16").ValueJe peux remplacer Range("T2:T16") par un nom ? Nom que j'aurai déterminé avec la fonction décaler, au cas ou des fichiers se rajoutent ?
Merci
Cordialement,
Version avec condition :
Sub VersESC()
Dim TT(), rgC, rgS, wbS, ar%, n%, i%, j%, x%
rgC = Array(4, 5, 6, 7, 8, 9, 10, 13, 15, 16, 17, 20, 21, 22, 23)
rgS = Array(33, 34, 35, 36, 37, 38, 11, 3, 43, 44, 45, 6, 7, 8, 5)
wbS = ThisWorkbook.Worksheets("Tables").Range("T2:T16").Value
Application.ScreenUpdating = False
For ar = 1 To UBound(wbS)
With Workbooks.Open(wbS(ar, 1))
With .Worksheets("Tableau")
n = .Cells(.Rows.Count, 3).End(xlUp).Row
For i = 15 To n
If .Cells(i, 31) = "ESC" Then
ReDim Preserve TT(14, x)
For j = 0 To 14
TT(j, x) = .Cells(i, rgS(j))
Next j
x = x + 1
End If
Next i
End With
.Close False
End With
With ThisWorkbook.Worksheets("ESC")
n = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
For i = 0 To UBound(TT, 2)
For j = 0 To 14
.Cells(n + i, rgC(j)) = TT(j, i)
Next j
Next i
End With
Erase TT: x = 0
Next ar
End SubLa condition est introduite à la boucle i de prélèvement.
On ne prédimensionne pas le tableau sur le nombre de lignes mais on va le redimensionner en préservant sont contenu à chaque ligne à prélever. On n'aura plus d'adéquation ligne/indice donc on peut partir de 0. Variable x (à 0 initialement), incrémentée après chaque prélèvement.
Le nombre de colonne étant fixe et le nombre de lignes variables, on inverse les dimensions : TT(colonnes, lignes) [Seule la dernière dimension d'un tableau (dynamique) peut être redimensionnée en cours d'exécution.]
Pour les boucles d'affectation, i ira maintenant de 0 à UBound(TT, 2) [2e dimension qui correspond aux lignes]
Et on affecte TT(j, i) à .Cells(n + i, rgC(j))
Avant de passer au fichier suivant, on efface le tableau (par sécurité) et on réinitialise x à 0.
Cordialement.
Bien sûr, tu peux nommer la plage en dynamique et l'affecter ainsi :
wbS = [NomPlage].ValueMerci beaucoup ! Je regarde tout ça et vous tient au courant.
Edit :
Pourquoi ne pas repasser "Applicaion.ScreenUpdating" en True à la fin du code ?
De plus, il faut que je place Application.EnableEvents = False au début de mon code, que je repasse en True à la fin.
Voici donc le code utilisé :
Sub VersESC()
Dim TT(), rgC, rgS, wbS, AR%, n%, i%, j%, x%
rgC = Array(4, 5, 6, 7, 8, 9, 10, 13, 15, 16, 17, 20, 21, 22, 23)
rgS = Array(33, 34, 35, 36, 37, 38, 11, 3, 43, 44, 45, 6, 7, 8, 5)
wbS = ThisWorkbook.Worksheets("Tables").Range("T2:T16").Value
Application.ScreenUpdating = False
Application.EnableEvents = False
For AR = 1 To UBound(wbS)
With Workbooks.Open(wbS(AR, 1))
With .Worksheets("Tableau")
n = .Cells(.Rows.Count, 3).End(xlUp).Row
For i = 15 To n
If .Cells(i, 31) = "ESC" Then
ReDim Preserve TT(14, x)
For j = 0 To 14
TT(j, x) = .Cells(i, rgS(j))
Next j
x = x + 1
End If
Next i
End With
.Close False
End With
With ThisWorkbook.Worksheets("Suppléance Centrale")
n = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
For i = 0 To UBound(TT, 2)
For j = 0 To 14
.Cells(n + i, rgC(j)) = TT(j, i)
Next j
Next i
End With
Erase TT: x = 0
Next AR
Application.EnableEvents = True
End SubLorsque je la fais tourner, tout fonctionne parfaitement pour le premier fichier : les 3 lignes s'intègrent correctement.
Par contre, j'ai ensuite une erreur : Erreur d'exécution '9' : L'indice n'appartient pas à la sélection.
For i = 0 To UBound(TT, 2)
Il s'agit de cette ligne qui pose problème. Cela signifie que la macro parvient bien à copier ce qu'il faut dans le deuxième fichier, mais qu'elle ne parvient pas à les coller dans le fichier cible, c'est bien ça ?
L'erreur est sans doute dû à la condition rajoutée ...
Edit 2 : Je remplace également cette partir du code :
n = .Cells(.Rows.Count, 4).End(xlUp).Row + 1par
n = .Cells(.Rows.Count, 5).End(xlUp).Row + 1Effectivement, la colonne D peut parfois rester vide (car colonne AG des fichiers sources pas toujours alimentée). La colonne E, par contre, l'est toujours.
Ma modification est-elle bonne, et surtout, est-elle suffisante ?
Merci
ScreenUpdating est rétabli à True automatiquement par Excel en fin de macro.
Il en est de même pour DisplayAlerts.
Pour l'erreur 9, je ne vois pas au niveau du code, sauf le cas où rien à prélever, et donc le tableau TT n'existe pas au moment de l'affectation !
Vérifie si on est dans ce cas sur le fichier en erreur, et si c'est le cas il faut mettre tout le bloc d'affectation sous condition :
.Close False
End With
If x > 0 Then
With ThisWorkbook.Worksheets("Suppléance Centrale")
n = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
For i = 0 To UBound(TT, 2)
For j = 0 To 14
.Cells(n + i, rgC(j)) = TT(j, i)
Next j
Next i
End With
Erase TT: x = 0
End If
Next ARAh d'accord merci pour l'information.
Effectivement, le tableau du fichier 2 ne comporte jamais "ESC" dans la colonne voulue.
Pour l'instant, les fichier sources ne sont pas alimentés, c'est pour ça.
Mais même une fois qu'ils seront alimentés, il est possible qu'un ou plusieurs fichiers ne comportent pas "ESC", je vais donc rajouter le bloc, merci beaucoup.
Du coup, concernant l'edit de mon post précédent, je peux remplacer le "4" par "5", sans problème ?
Je vous tiens au courant.
Cordialement,