Macro compilation excel

Version imprimable

29/03/2017, 14h06
le petit excelleur

Macro compilation excel

Salut la communauté,

Bon voilà j'ai un petit problème qui est simple pour quelqu'un qui maîtrise Excel à mon avis.
Mais qui me bloque depuis 3 jours, je cherche une macro que je ne trouve pas ...

Je vous explique.

J'ai sur un serveur un dossier dans lequel se trouve x fichiers.
Tous ces fichiers ont exactement la même mise en page et ont tous une feuille (Export) qui contient 30-35 lignes.

Je cherche tout simplement une macro qui me permette de synthétiser tous ces fichiers (sans Les ouvrir) en un seul Excel sur une seule feuille...

Et cela me bloque depuis maintenant 3 jours... je fais donc appel à la communauté pour voir si une âme charitable serait prête à m'aider un peu.

Merci à tous
29/03/2017, 21h57
le petit excelleur

Pas si simple que ça en fait ;)

Ahah étant donnée l'absence de réponse je présume que c'est pas si simple que ça ce que je demande ;)

Si néanmoins un expert Excel à un peu de temps à me consacrer ça serait vraiment cool ;)

Merci, À tous et bonne soirée
30/03/2017, 14h24
Pierre Fauconnier

Salut.

Pourquoi sans les ouvrir? La macro pourrait ouvrir chaque fichier, copier les données puis le fermer et passer au suivant.

Sinon, tu peux créer une requête SQL sur fichier (en fonction de la mise en forme de tes données) pour récupérer uniquement les données, mais techniquement, ça ouvre quand même le fichier Excel.
30/03/2017, 14h34
riaolle

Bonjour,
Voici un tuto pour lire et écrire dans un classeur fermé.
Ensuite y a plus qu'à copier les valeurs comme si les classeurs étaient ouverts :)
30/03/2017, 19h26
le petit excelleur

Bonjour à tous les deux,

Oui en fait je me suis mal exprimé je pense Pierre, je cherche une macro qui peux les ouvrir et les fermer cela ne me gènes pas du moment que les données sont reprises dans mon classeurs cible et que je ne me retrouve pas à la fin avec mes x classeurs d'ouverts (donc ce que tu dis est en fait ce que je cherche je pense, le but est elle reprendre les donnes des différents classeurs et Les remettes dans un seul et même).

J'ai regardé ton lien riaolle mais je cherche plus simple à mon avis, ce que je cherche à faire en fait est de trouver une macro qui me permet de faire la manip expliqué dans mon premier message mais si elle ouvre et referme mes classeurs ça ne gêne pas.

Le problème c'est que malgré les tests et recherches divers je n'ai rien trouvé qui fonctionne jusqu'à maintenant...

Les classeurs à fusionner ont-ils la même structure? Même nombre de colonnes et un moyen de déterminer le nombre de lignes?

Tu pourrais en tout cas utilement t'inspirer de ces codes que j'ai donné aujourd'hui dans une autre discussion. Il faut les adapter, notamment pour déterminer la plage à copier, et également en insérant le code depuis l'ouverture jusqu'à la fermeture du classeur dans une boucle dont il faudra déterminer le début et la fin...
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 Function RangeCopy(WorksheetName As String, RangeAddress As String, Optional wk As Workbook) As Range Dim sh As Worksheet Dim rng As Range If wk Is Nothing Then Set wk = ActiveWorkbook Set sh = wk.Worksheets(WorksheetName) Set RangeCopy = sh.Range(RangeAddress) End Function Sub TestCopy() Dim SourceWk As Workbook Dim WkName As String Dim ShName As String Dim RngName As String WkName = "c:\tests\source.xlsx" ShName = "Données" RngName = "A1:D10" Set SourceWk = Workbooks.Open(WkName) ThisWorkbook.Activate RangeCopy(ShName, RngName, SourceWk).Copy Range("a1") SourceWk.Close savechanges:=False End Sub

30/03/2017, 20h01
kiki29

1 pièce(s) jointe(s)

Salut, à tenter si tu es curieux : fusion fichiers xls*, txt d'un dossier
30/03/2017, 20h03
le petit excelleur

Oui pierre ils ont exactement le même nombre de colonne et le même nombre de ligne mais certaines lignes sont vides dans certains classeurs, je ne sais pas si cela est gênant ou non?

En fait je pense que la macro est pas si compliqué si on est un peu (voir beaucoup ahaha) meilleur que moi.
Mais pour répondre Les colonnes ont toutes la même taille de À à G
Et Les lignes vont de 2 à 55 avec certaines lignes vides sur certaines feuilles
30/03/2017, 20h33
Pierre Fauconnier

Le code que je te propose fait cela sans souci. Ca ira pour l'inclure dans la boucle ou tu souhaites un coup de mains?

Les fichiers sont repris dans une liste? Ce sont tous les fichiers d'un même dossier? Bref, qu'est-ce qui permet de les lister pour la boucle?
30/03/2017, 20h42
le petit excelleur

Honnêtement je veux bien un peu d'aide sur ton histoire de boucle.
Les fichiers sont tous dans le meme dossiers mais on pas tous le même nom.
Il n'y a pas de liste mais si c'est nécessaire et plus simple je peux en faire une avec Les noms des fichiers ;)

Voilà un code dont tu devras adapter certaines données...

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
Function getSourceRange(WorksheetName As String, RangeAddress As String, Optional wk As Workbook) As Range
  Dim sh As Worksheet
  Dim rng As Range
 
  If wk Is Nothing Then Set wk = ActiveWorkbook
  Set sh = wk.Worksheets(WorksheetName)
  Set getSourceRange = sh.Range(RangeAddress)
End Function
 
Sub CopyWorkbooks()
  Dim Path As String
  Dim ShName As String
  Dim SourceRangeAddress As String
  Dim SourceRange As Range
  Dim SourceWk As Workbook
  Dim TargetRange As Range
  Dim WkName As String
 
  ShName = "Datas"
  SourceRangeAddress = "a1:d55"
  Path = "e:\tests"
  Set TargetRange = Range("a2")
  WkName = Dir(Path & "\*.xlsx")
  Do While WkName <> ""
    Set SourceWk = Workbooks.Open(Path & "\" & WkName)
    ThisWorkbook.Activate
    Set SourceRange = getSourceRange(ShName, SourceRangeAddress, SourceWk)
    SourceRange.Copy TargetRange
    Set TargetRange = TargetRange.Offset(SourceRange.Rows.Count)
    SourceWk.Close savechanges:=False
    WkName = Dir()
  Loop
End Sub

30/03/2017, 21h53
le petit excelleur

Merci ;)

Merci pierre,
J'essaie ça et je te dis si ça fonctionne.
Merci en tout cas du temps que tu prends pour me répondre.

Juste dans ShName = "Datas", le « datas » je dois le remplacer mais je vois pas trop par quoi ?

Pareil là en tentant de comprendre
Les "" il faut aussi Les remplacer ?
Dans

Code:

Do While WkName <> ""

Merci encore

Il faut remplacer Datas par le nom de la feuille qui contient la plage source. Quant au "" de la ligne DoWhile... il faut les laisser en l'état.

Je commenterai le code demain matin pour que tu puisses comprendre plus aisément ce qu'il fait ;)

Ceci dit, je viens de vérifier et cela fonctionne bien chez moi. Néanmoins, tu peux te passer de la fonction qui détermine la plage. Je l'avais utilisée dans un autre exemple mais elle n'amène rien de mieux ici. Tu peux définir la plage directement dans la fonction de consolidation
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 Sub CopyWorkbooks() Dim Path As String Dim ShName As String Dim SourceRangeAddress As String Dim SourceRange As Range Dim SourceWk As Workbook Dim TargetRange As Range Dim WkName As String ShName = "Datas" SourceRangeAddress = "a1:d55" Path = "e:\tests" Set TargetRange = Range("a2") WkName = Dir(Path & "\*.xlsx") Do While WkName <> "" Set SourceWk = Workbooks.Open(Path & "\" & WkName) ThisWorkbook.Activate Set SourceRange = SourceWk.Worksheets(ShName).Range(SourceRangeAddress) SourceRange.Copy TargetRange Set TargetRange = TargetRange.Offset(SourceRange.Rows.Count) SourceWk.Close savechanges:=False WkName = Dir() Loop End Sub

30/03/2017, 22h09
le petit excelleur

Dac, je te remercie vraiment.
C'est cool de ta part.
Bon je te laisse tranquille ce soir quand même ;)
Merci Pierre !

Salut à Tous,

je viens d'essayer ta macro Pierre, et elle ne fonctionne pas.

A mon avis c'est juste parce que j'ai mal remplit quelque chose (un champ à remplacer dans la macro que je n'ai pas fait je pense).

J'ai modifié comme suit le code :
Code:

1 2 3 4 5 6 ShName = "le nom de ma feuille" SourceRangeAddress = "a1:d55" Path = "le lien de mon dossier qui héberge toutes mes feuilles de données" Set TargetRange = Range("a2") WkName = Dir(Path & "\*.xlsx") Do While WkName <> ""
Peux-tu si cela ne te gènes pas trop, mettre des petits commentaires à coté de ton code afin que je puisse faire fonctionner cette macro, ou me dire ou j'ai fait mon erreur :)

A +

03/04/2017, 20h10
Pierre Fauconnier

Je vais faire cela.

Mais... Que se passe-t-il qui te fait dire qu'elle ne fonctionne pas? Message d'erreur? Résultat erroné? Rien ne se passe?

Dire "ça ne fonctionne pas" sas autre commentaire ne nous aide pas beaucoup... :roll:

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
Sub CopyWorkbooks()
  Dim Path As String ' Nom complet, racine comprise, du chemin des fichiers à scanner
  Dim ShName As String ' Nom de la feuille source dont il faut copier les données. Doit être Identique dans tous les classeurs source
  Dim SourceRangeAddress As String ' Adresse de la plage à copier. Doit être identique sur toutes les feuilles source
  Dim SourceRange As Range ' sera utilisé pour définir la plage source à copier
  Dim SourceWk As Workbook ' Objet qui pointera vers chaque classeur source dans la boucle
  Dim TargetRange As Range ' Adresse de la première cellule de la plage d'arrivée. Sera recalculée à chaque boucle
  Dim WkName As String ' Nom du classeur source à ouvrir. Sera redéfini à chaque boucle
 
  ShName = "Datas" ' Nom de la feuille source qui contient les données. IDENTIQUE pour tous les classeurs source
  SourceRangeAddress = "a1:d55" ' Plage à copier dans le classeur source. IDENTIQUE à chaque classeur source
  Path = "e:\tests" ' Nom complet du dossier, sans \
  Set TargetRange = Range("a2") ' Première plage de collage. Sera "offsetée" en fonction des données copiées à chaque boucle
  WkName = Dir(Path & "\*.xlsx") ' Nom du premier classeur trouvé dans le dossier source
  Do While WkName <> "" ' Boucle tant qu'il y a un fichier non traité dans le dossier. DIR "épuise" les fichiers correspondant au pattern
    Set SourceWk = Workbooks.Open(Path & "\" & WkName) 'Ouverture du fichier source et attribution du fichier à SourceWk
    ThisWorkbook.Activate ' Activation du classeur de traitement (celui qui contient la macro) et, a priori, les plages d'arrivée
    Set SourceRange = SourceWk.Worksheets(ShName).Range(SourceRangeAddress) ' Instanciation de la plage source
    SourceRange.Copy TargetRange ' Copie de la plage source sur la plage d'arrivée, définie plus haut pas son coin supérieur gauche
    Set TargetRange = TargetRange.Offset(SourceRange.Rows.Count) ' Recalcul du nouveau coin supérieur gauche pour la prochaine copie (décalage du nombre de lignes copiées)
    SourceWk.Close savechanges:=False ' Fermeture du classeur d'origine sans sauvegarde
    WkName = Dir() ' Recherhce du nom du classeur suivant. Si DIR est passé sur tous les classeurs, renvoie "" pour sortir de la boucle
  Loop ' Boucle
End Sub

03/04/2017, 21h16
le petit excelleur

Oups, oui ce n'est pas très explicite je te l'accorde ;)

En fait je n'ai pas de message d'erreur mais rien ne se passe du tout.

C'est la traduction du 'ne fonctionne pas ' :)

Quand tu mets WkName = Dir(Path & "\*.xlsx"), il faut que je remplace le "\*.xlsx" par le nom exact du premier classeur en être "" ?
03/04/2017, 21h30
Pierre Fauconnier
Non, tu dois le remplacer par le modèle du nom de fichier que tu veux récupérer.
- *.xlsx récupérera tous les fichiers xlsx;
- semaine??.xlsx récupérera les fichiers qui commencent pas le mot semaine suivi de deux caractères puis de l'extension xlsx;
- Ventes*.xlsx récupérera tous les fichiers de type xlsx qui commencent par ventes;
- ??12*.xlsx récupérera les fichiers pour lesquels on a deux caractères puis 12 puis un nombre indéterminé de caractères puis l'extension xlsx;
- ...
En fait, * remplace un nombre indéterminé de caractères, et ? remplace n'importe quel caractère à l'endroit où tu le places dans le nom générique (le "pattern" ou patron du nom de fichiers). Ces caractères te permettent de définir une liste de fichiers sans devoir les citer tous nommément en définissant le masque (le pattern, en anglais) auquel doivent correspondre les noms de fichier. On retrouve ces caractères dans les filtres, la fonction RECHERCHEV, les fonctions SOMME.SI.ENS et apparentées, la fonction EQUIV...

C'est un ancien truc qui nous vient du DOS...
03/04/2017, 21h38
le petit excelleur

OK,

Donc dans mon cas, ça sera :

Code:

WkName = Dir(Path & "S*.xlsx")

Ce qui voudra dire tous les fichiers qui commence par un S, nous sommes bien d'accord ?

Merci encore pour le temps que tu passes à m'aider ;)
03/04/2017, 21h49
Pierre Fauconnier

1 pièce(s) jointe(s)

Citation:

Envoyé par le petit excelleur

[...]

WkName = Dir(Path & "S*.xlsx")

Ce qui voudra dire tous les fichiers qui commence par un S, nous sommes bien d'accord ?[...]

Oui, n'oublie pas le \ si tu ne l'as pas mis dans la variable Path...

N'oublie pas que tu peux démarrer la macro avec F8 (losque le curseur d'édition est dans la macro) pour l'exécuter en pas à pas. Tu peux alors vérifier tes variables, notamment les variables locales en affichant la fenêtre idoine (Menu Affichage/Fenêtre variables locales).

La ligne jaune n'a pas encore été exécutée. Sur l'illustration ci-dessous, tu peux voir la fenêtre des variables locales après l'attribution du nom du fichier récupéré par DIR à la variable WkName. Tu comprends ainsi que tu dois recomposer le nom complet avec son chemin en concaténant le path et le nom de fichier, sans oublier le \ entre les deux...

Pièce jointe 260658
03/04/2017, 21h58
le petit excelleur

Oui je me sert du pas à pas quand j'exécute la macro pour voir si il n'y a pas d bug par endroit.

Oui donc ça sera plutôt :

Code:

WkName = Dir(Path & "\S*.xlsx")

Tel que ça ça devrait fonctionner !!!

Allez je t'ai assez embêté pour ce soir je pense, bonne soirée à toi ;)
03/04/2017, 22h01
Pierre Fauconnier

Pas de soucis... Il n'y a pas d'embêtement, sinon je ne répondrais pas.

J'espère que ça va fonctionner.

Bonne soirée également :)
04/04/2017, 08h44
le petit excelleur

Salut Pierre, et les autres membres de la communauté,

Alors, j'ai bien testé ta macro hier soir.
Or comme lors de mes précédents test, je nai pas de message d'erreur mais rien ne fonctionne.

J'ai copié la macro tel que tu me l'as donné en changeant les éléments suivants :

ShName = "export"
SourceRangeAddress = "A2:G15"
Path = "D:\Users\Documents\testexport"
Set TargetRange = Range("A2")

Or rien ne se passe, je ne comprends pas trop ce que je rate car il me semble pourtant avoir fait toutes les modifs...

Si quelqu'un à une idée maintenant que je m’approche du but ;)
04/04/2017, 10h09
Pierre Fauconnier

Peux-tu donner le code de la macro utilisée?

L'as-tu bien testée en pas à pas? => regarde après chaque ligne exécutée si ce qu'elle est censée réalisé a bien été effectué.

Bonjour Pierre,

Oui je l'ai testé en pas à pas et je ne trouve pas le problème en fait c'est d'ailleurs ce qui m'étonnes un peu...

Le code est ci-dessous :

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
 
Sub CopyWorkbooks()
  Dim Path As String ' Nom complet, racine comprise, du chemin des fichiers à scanner
  Dim ShName As String ' Nom de la feuille source dont il faut copier les données. Doit être Identique dans tous les classeurs source
  Dim SourceRangeAddress As String ' Adresse de la plage à copier. Doit être identique sur toutes les feuilles source
  Dim SourceRange As Range ' sera utilisé pour définir la plage source à copier
  Dim SourceWk As Workbook ' Objet qui pointera vers chaque classeur source dans la boucle
  Dim TargetRange As Range ' Adresse de la première cellule de la plage d'arrivée. Sera recalculée à chaque boucle
  Dim WkName As String ' Nom du classeur source à ouvrir. Sera redéfini à chaque boucle
 
  ShName = "export" ' Nom de la feuille source qui contient les données. IDENTIQUE pour tous les classeurs source
  SourceRangeAddress = "A2:G15" ' Plage à copier dans le classeur source. IDENTIQUE à chaque classeur source
  Path = "D:\Users\Documents\testexport" ' Nom complet du dossier, sans \
  Set TargetRange = Range("A2") ' Première plage de collage. Sera "offsetée" en fonction des données copiées à chaque boucle
  WkName = Dir(Path & "\S*.xlsx") ' Nom du premier classeur trouvé dans le dossier source
  Do While WkName <> "" ' Boucle tant qu'il y a un fichier non traité dans le dossier. DIR "épuise" les fichiers correspondant au pattern
    Set SourceWk = Workbooks.Open(Path & "\" & WkName) 'Ouverture du fichier source et attribution du fichier à SourceWk
    ThisWorkbook.Activate ' Activation du classeur de traitement (celui qui contient la macro) et, a priori, les plages d'arrivée
    Set SourceRange = SourceWk.Worksheets(ShName).Range(SourceRangeAddress) ' Instanciation de la plage source
    SourceRange.Copy TargetRange ' Copie de la plage source sur la plage d'arrivée, définie plus haut pas son coin supérieur gauche
    Set TargetRange = TargetRange.Offset(SourceRange.Rows.Count) ' Recalcul du nouveau coin supérieur gauche pour la prochaine copie (décalage du nombre de lignes copiées)
    SourceWk.Close savechanges:=False ' Fermeture du classeur d'origine sans sauvegarde
    WkName = Dir() ' Recherhce du nom du classeur suivant. Si DIR est passé sur tous les classeurs, renvoie "" pour sortir de la boucle
  Loop ' Boucle
End Sub

Pierre,

En fait après avoir refait le F8, rien ne semble poser problème dans la première feuille.
Dès que j'arrive à la seconde feuille au niveau de SET SourceRange

il apparaît : "erreur d’exécution '9': L'indice n'appartient pas à la sélection.

Si tu veux je peux t'envoyer plus pour que tu comprennes je ne sais pas ce qui pourrait t'aider car honnêtement je suis un peu à court là...

04/04/2017, 12h17
Pierre Fauconnier

Tu es sûr que la structure des tes fichiers source est bonne?

Tu as bien dans chaque fichier source une feuille qui porte le nom que tu as donné à shName?