Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

[Testeur]

Bonjour,

Je cherche à copier la valeur de plusieurs cellules dans des fichiers fermés dans un classeur ouvert. Ces cellules se trouvent au même emplacement dans les différents fichiers.

Les cellules doivent être copiées en ligne dans le fichier "bilan". Au final, les données de chaque fichier source correspondent donc à une ligne du fichier "bilan".

Tous les fichiers se trouvent dans le même répertoire.

J'ai cherché sur internet, mais soit la solution indiquée ne fonctionne pas soit je ne comprends pas les indications.

Merci par avance de votre aide, je suis un newbie en la matière et en plus c'est urgent.

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Bonsoir Testeur, bienvenue sur XLD,

J'ai cherché sur internet, mais soit la solution indiquée ne fonctionne pas soit je ne comprends pas les indications.

Vous avez forcément dû voir que ce genre de problème se règle par la méthode ADO.

A priori vous n'aurez rien de plus sur ce forum, alors faites un effort pour comprendre cette méthode.

Et si c'est "urgent" vous auriez pu joindre un fichier source et donner plus d'indications sur ce que vous voulez.

A+

 

[Testeur]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Je suis désolé, je vais vous paraître complètement nul, j'ai lu la méthode ADO mais j'ai rien compris

Voici, un exemple de fichier source (les données et la mise en page ont été modifiées pour des raisons de confidentialité), la valeur des cases en jaune sont à copier dans un fichier "synthèse". J'ai plusieurs centaines de fichier source, les cases à copier sont aux mêmes emplacements.

Merci par avance,

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Re,

Vous avez écrit :

Les cellules doivent être copiées en ligne dans le fichier "bilan". Au final, les données de chaque fichier source correspondent donc à une ligne du fichier "bilan".

Avec votre fichier source je ne vois pas du tout.

A+

 

[Testeur]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

L'idée est de copier la valeur des cellules sources vers le fichier "synthèse" comme suit:

Fichier source n°1---------->Fichier "synthèse"
A30 ------------------------>A2
F30 ------------------------>B2
A32 ------------------------>C2
F32 ------------------------>D2
F36 ------------------------>E2
etc...

Fichier source n°2 --------> Fichier "synthèse"
A30 ------------------------>A3
F30 ------------------------>B3
A32 ------------------------>C3
F32 ------------------------>D3
F36 ------------------------>E3

et ainsi de suite pour chaque fichier source.

Je ne sais pas si je suis très clair.

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Bonjour Testeur, le forum,

Finalement des formules de liaisons classiques sont plus simples que la méthode ADO :

Sub CopierFichiersFermés()
Dim chemin$, w As Worksheet, feuil$, P As Range, lig&
Dim fichier$, col As Byte, c As Range, f$
chemin = ThisWorkbook.Path & "\"
Set w = Feuil2 'CodeName de la feuille Synthèse
feuil = "Feuil1" 'nom à adapter
Set P = [A30,F30,A32,F32,F36:F37,F39,F41:F42,F44,C47:F66]
lig = 2
Application.ScreenUpdating = False
w.Rows("2:" & w.Rows.Count).Delete 'RAZ
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
While fichier <> ""
  If fichier <> ThisWorkbook.Name Then
    col = 1
    For Each c In P
      f = "='" & chemin & "[" & fichier & "]" & feuil & "'!" & c.Address
      w.Cells(lig, col) = f
      col = col + 1
    Next
    w.Cells(lig, col) = fichier 'nom du fichier en colonne 91
    lig = lig + 1
  End If
  fichier = Dir 'fichier suivant du dossier
Wend
w.UsedRange = w.UsedRange.Value 'supprime les formules
w.Activate
End Sub

Téléchargez les fichiers joints dans le même dossier (répertoire).

Bonne journée et A+

 

[Testeur]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Merci,

Je vais tester les fichiers et si vous me le permettez vous poser des questions complémentaires si des choses m'échappent.

Merci encore

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Re,

Avec le tableau VBA rest (transposé) et ExecuteExcel4Macro c'est plus rapide :

Sub CopierFichiersFermés()
Dim chemin$, w As Worksheet, feuil$, P As Range, ncol As Byte
Dim fichier$, lig&, rest(), col As Byte, c As Range, a$, f$
chemin = ThisWorkbook.Path & "\"
Set w = Feuil2 'CodeName de la feuille Synthèse
feuil = "Feuil1" 'nom à adapter
Set P = [A30,F30,A32,F32,F36:F37,F39,F41:F42,F44,C47:F66]
ncol = P.Count + 1
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
While fichier <> ""
  If fichier <> ThisWorkbook.Name Then
    lig = lig + 1
    ReDim Preserve rest(1 To ncol, 1 To lig)
    col = 1
    For Each c In P
      a = c.Address(ReferenceStyle:=xlR1C1)
      f = "'" & chemin & "[" & fichier & "]" & feuil & "'!" & a
      rest(col, lig) = ExecuteExcel4Macro(f)
      col = col + 1
    Next
    rest(col, lig) = fichier 'nom du fichier en colonne 91
  End If
  fichier = Dir 'fichier suivant du dossier
Wend
'---restitution---
If lig Then w.[A2].Resize(lig, ncol) = Application.Transpose(rest)
w.Rows(lig + 2 & ":" & w.Rows.Count).Delete
w.Activate
End Sub

Fichiers joints.

A+

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Re,

J'ai testé sur Win XP - Excel 2003, et ce n'est pas ce que je pensais.

Avec les fichiers joints la macro du post #8 est un petit peu moins rapide que celle du post #6.

A+

 

[Testeur]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Merci,

Cela marche ! mais avec au maximum 3 fichiers sources
Par contre dès que l'on dépasse ce nombre de fichiers à agréger ça retourne une erreur:
"erreur d'exécution 1004"

ligne surlignée en jaune: rest(col, lig) = ExecuteExcel4Macro(f)

Merci pour votre aide

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Re,

Curieux, j'ai testé sans problème la macro du post #8 avec 10 fichiers sources.

A+

 

[Testeur]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

J'ai également essayé sur Libre Office où est générée l'erreur suivante:
erreur d'exécution BASIC, '9'
Index hors de la plage définie

Ligne surlignée: ReDim Preserve rest(1 To ncol, 1 To lig)

Qu'en pensez vous ?

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Re,

Par copier-coller j'ai créé 200 fichiers sources.

Chez moi la macro du post #8 s'exécute sans problème en 40 secondes.

A+

 

[job75]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Re,

Et finalement sur 400 fichiers sources :

- macro du post #6 => 92 secondes

- macro du post #8 => 82 secondes, c'est donc bien un peu plus rapide.

A+

 

[Testeur]

Re : Copier la valeur de plusieurs cellules de fichiers fermés dans un autre fichier

Pour précision, j'ai changé la plage des cellules à copier:

Set P = [A26,F26,A30,F30,C40:F42,F45,F46,F51,C54:F94]

cela marche aléatoirement, 3, voire 4 et même 5 fichiers, mais après ça bloque. Je suis perdu avec les mystères de l'informatique