XL 2016 copier une colonne de données dans tous les fichiers d'un répertoire et les coller les unes à la suite des autres dans une feuille excell

[YCab]

Bonsoir le forum,

Dans un post du 12/11/2019 de job75 que j'ai trouvé en cherchant une solution à mon problème (copier une colonne de données dans tous les fichiers d'un répertoire et les coller les unes à la suite des autres dans une feuille excell), la macro suivante était suggérée:

sub MAJ()
Dim chemin$, fichier$, feuille$, ncol%, lig&, f$, derlig As Variant, ad$
chemin = ThisWorkbook.Path & "\" 'à adapter événtuellement
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
feuille = "Feuil1" 'nom de la feuille à copier dans les fichiers
Application.ScreenUpdating = False
With Feuil1 'CodeName de la feuille des résultats, à adapter
.Rows("2:" & Rows.Count).Delete xlUp 'RAZ
ncol = .[A1].CurrentRegion.Columns.Count
lig = 2
While fichier <> ""
f = "'" & chemin & "[" & fichier & "]" & feuille & "'!"
derlig = ExecuteExcel4Macro("MATCH(9^99," & f & "R1C1:R1048576C1)")
If IsNumeric(derlig) Then
If derlig > 1 Then
ad = .Cells(2, 1).Resize(derlig - 1, ncol).Address
With .Cells(lig, 1).Resize(derlig - 1, ncol)
.FormulaArray = "=" & f & ad 'formule de liaison matricielle
.Value = .Value 'supprime la formule
End With
lig = lig + derlig - 1
End If
End If
fichier = Dir 'fichier suivant
Wend
.UsedRange.Replace 0, "", xlWhole 'supprime les zéros
End With
End Sub

J'ai construit ma propre macro en m'en inspirant et je bute sur un problème qui est que la macro ne trouve pas mes fichiers. Le message d'erreur est "le fichier *.xlsx n'existe pas,..."
Je pensais que la commande:
fichier = Dir(chemin & "*.xlsx") permettait de désigner le 1er fichier du répertoire, comme c'est indiqué dans le commentaire, mais visiblement ce n'est pas le cas…
Auriez-vous une idée de ce qui peut poser problème dans mon cas?

Autre question, comment se fait-il que la commande:
fichier = Dir
suffise à passer au fichier suivant?

Merci pour vos réponses

 

[_Thierry]

Bonsoir @YCab , le Forum

En tout cas ce n'est pas Dir et Fichier = Dir qui est en cause, voir ci-joint, si tu sauves ce classeur dans un répertoire contenant d'autres XLSX... et tu verras...

Le problème vient d'ailleurs, mais les ExecuteExcel4Macro , je crois que ca fait 20 ans que je n'en ai plus vu !!!
Je me rapelle que c'était un moyen de créer une formule en lien avec un fichier fermé...
Bon courage
@+Thierry

 

[ChTi160]

Bonsoir Ycab ,le Forum
Histoire de Saluer mon ami Thierry (qui n'a pas beaucoup dormi Lol)
peut être en mettant entre autres

Dim fichier as Object

et les déclarations ne sont pas des plus adaptées Lol

Dim chemin$, fichier$, feuille$, ncol%, lig&, f$, derlig As Variant, ad$

jean marie

 

[_Thierry]

Salut Jean Marie
Fichier en l'état , passe sur la macro , voir mon test.
Oui en ce moment je dors mal et très peu !
Bien à toi
@+Thierry

 

[job75]

Bonsoir à tous,

Il ne peut pas y avoir de message d'erreur avec l'instruction fichier = Dir(chemin & "*.xlsx")

S'il n'y a aucun fichier .xlsx dans le dossier on aura fichier = "" un point c'est tout.

PS : fichier est une variable String, toutes les déclarations des variables sont correctes...

A+