XL 2021 Récupérer des données dans plusieurs classeurs sans les ouvrir - Résolu

[Richard30]

Bonjour,

J'ai tenté avec des formules mais j'ai bien l'impression qu'il n'y a pas de salut sans VBA et il se trouve que je n'y connais rien... en VBA !
Voila donc mon problème :
- dans 1 dossier j'ai jusqu'à 150 fichiers ayant tous la même structure.
- les fichiers évoluent au fil des mois
- je sais facilement extraire les noms des fichiers comme vous le verrez en colonne A
==> je souhaite récupérer les données qui se trouvent dans les cellule désignées en ligne 6
==> je souhaite que ça fonctionne si je change le chemin en cellule B1 ==> si c'est un point bloquant, je pourrais me débrouiller pour faire autrement.

Est-ce qu'une âme charitable saurait me trouver une solution ?

Merci par avance,
Bonne fin de journée

 

[chris]

Bonjour
Le salut est plutôt du côté de PowerQuery aujourd'hui, ce que tu as commencé à faire

Mais on n'y raisonne pas avec les références ligne colonne d'excel mais avec des noms de champs donc sans exemple d'une source on ne peut t'aider

 

[Richard30]

Bonjour
Le salut est plutôt du côté de PowerQuery aujourd'hui, ce que tu as commencé à faire

Mais on n'y raisonne pas avec les références ligne colonne d'excel mais avec des noms de champs donc sans exemple d'une source on ne peut t'aider

Bonjour Chris et merci pour ta réponse.
Voici une paire de fichiers en PJ.
Je suis curieux de voir ce qu'est Power Query.
Merci en tout ca

 

[Richard30]

Bonjour Chris et merci pour ta réponse.
Voici une paire de fichiers en PJ.
Je suis curieux de voir ce qu'est Power Query.
Merci en tout ca

Et j'ai oublié d'ajouter que j'ai bien tenté Power Query mais je n'ai réussi qu'à lister les fichiers du répertoire dans lequel ils sont stockés, je n'ai pas trouvé comment faire pour extraire des données de ce fichiers.

 

[laurent950]

Bonsoir @Richard30

En VBA c est complexe si vous connaissez pas le langage ?

 

[job75]

Bonsoir à tous,

En VBA c est complexe si vous connaissez pas le langage ?

Bah il y a de nombreux exemples comme celui-ci qui utilisent les formules de liaison :

Sub Import()
Dim chemin$, feuille$, ListeFich, ListeCel, tablo(), i&, form$, j%
chemin = [B1]
feuille = [B2]
ListeFich = [A8:A100] 'à adapter
ListeCel = [B6:N6] 'à adapter
With [B8:N100] 'tableau des résultats
    ReDim tablo(1 To .Rows.Count, 1 To .Columns.Count)
    For i = 1 To UBound(tablo)
        If Dir(chemin & ListeFich(i, 1)) <> "" And Right(ListeFich(i, 1), 4) Like "xls?" Then
            form = "='" & chemin & "[" & ListeFich(i, 1) & "]" & feuille & "'!"
            For j = 1 To UBound(tablo, 2)
                tablo(i, j) = form & ListeCel(1, j)
            Next j
        End If
    Next i
    .Formula = tablo
    .Value = .Value
End With
End Sub

A+

 

[laurent950]

Bonsoir @job75

Je sais largement faire cela, c'est très simple pour nous.
Juste complexe pour ceux qui connaissent pas le VBA.

 

[kiki29]

Salut, pour info si curieux lire/pratiquer ceci

 

[Richard30]

Bonsoir à tous,

Bah il y a de nombreux exemples comme celui-ci qui utilisent les formules de liaison :

Sub Import()
Dim chemin$, feuille$, ListeFich, ListeCel, tablo(), i&, form$, j%
chemin = [B1]
feuille = [B2]
ListeFich = [A8:A100] 'à adapter
ListeCel = [B6:N6] 'à adapter
With [B8:N100] 'tableau des résultats
    ReDim tablo(1 To .Rows.Count, 1 To .Columns.Count)
    For i = 1 To UBound(tablo)
        If Dir(chemin & ListeFich(i, 1)) <> "" And Right(ListeFich(i, 1), 4) Like "xls?" Then
            form = "='" & chemin & "[" & ListeFich(i, 1) & "]" & feuille & "'!"
            For j = 1 To UBound(tablo, 2)
                tablo(i, j) = form & ListeCel(1, j)
            Next j
        End If
    Next i
    .Formula = tablo
    .Value = .Value
End With
End Sub

A+

Merci @job75 ça fonctionne parfaitement et en plus c'est rapide.
Je vais gagner un temps fou.
Merci beaucoup

 

[chris]

Bonjour

Modfier le chemin en jaune puis Données, Actualiser tout

Cela traite tout le contenu du dossier dont le nom contient SGCD donc si d'autres classeurs s'ajoutent ils sont traités

On peut présenter différemment la synthèse :

Tout dépend de l'utilisation ensuite.
A toi de dire quelle structure t'arrange le mieux

 

[job75]

Bonjour le forum,

Avec cette solution on construit en même temps la liste des fichiers .xlsx du dossier :

Sub Import()
Dim chemin$, feuille$, ncol%, ListeCel, fichier$, tablo(), i&, form$, j%
chemin = [B1]
feuille = [B2]
ncol = 14 'de A à N
ListeCel = [A6].Resize(, ncol) 'à adapter au besoin
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
With [A8:A1000].Resize(, ncol) 'tableau des résultats
    ReDim tablo(1 To .Rows.Count, 1 To ncol)
    While fichier <> ""
        i = i + 1
        tablo(i, 1) = fichier
        form = "='" & chemin & "[" & tablo(i, 1) & "]" & feuille & "'!"
        For j = 2 To ncol
            tablo(i, j) = form & ListeCel(1, j)
        Next j
        fichier = Dir 'fichier suivant
    Wend
    .Formula = tablo 'restitution
    .Value = .Value 'supprime les formules
End With
End Sub

A+