Comparaison nom de fichiers et copie si fichiers absents

Bonjour à tous,

j'ai entamé la création d'une macro sous excel qui doit à terme me permettre de comparer le contenu de 2 dossiers (que j'appellerai ici dossier 1 et dossier 2) et copier ensuite de 1 vers 2 les fichiers absents.
Pour se faire, mes dossiers étant stockés sur un serveur Sharepoint, j'ai donc exporté le contenu de mes dossiers, donc les noms des fichiers, du dossier 1 dans la Sheet1, mes données du dossier 2 dans la Sheet2.
J'ai créé un timer pour que cela soit automatique ainsi qu'ajouter la fonction rafraichissement des données puisque mes données sont sur Sharepoint et que des utilisateurs peuvent publier des documents dans 1.

Désormais, c'est la où je vous sollicite, dans mon sheet1 apparait les fichiers des users(sheet1), je souhaite comparer dans un premier temps qu'ils ne sont pas présents dans le dossier 2(sheet2) (comparaison du nom de fichier), et si celui-ci n'est pas présent je le copie.
Si il est présent je passe au suivant etc...

L'un de vous se sent il capable de m'aider dans ma démarche? Car pour être tout a fait honnête je n'ai quasiment aucune notion en vba.

Par avance merci

Bruno

Salut.

Pourrais-tu préciser ta version d'Excel? Depuis 2010, on dispose de Power Query qui permet de comparer deux listes et de déterminer les éléments de l'une qui ne sont pas dans l'autre. Ca soulagerait ton VBA et tu pourrais t'appuyer sur le tableau structuré résultant de Power Query pour copier tes fichiers.

Je précise aussi que Power Query permet de lister les fichiers d'un dossier. Le VBA servirait alors seulement à transférer les fichiers d'un dossier à l'autre.

Citation:

---

Envoyé par Pierre Fauconnier

Salut.

Pourrais-tu préciser ta version d'Excel?

---

Bonjour Pierre, merci de me répondre.

Alors je bosse sur Excel 2016. Cependant, je souhaite m'orienter vers du VBA car cette première macro va s'inscrire dans un cadre plus large.

En effet, comme expliquer, j'ai deux dossiers (bibliotheque) sur un serveur sharepoint. Le but étant vraiment de comparer les fichiers du dossier 1 avec les fichiers du dossier 2. Si un document (et uniquement dans ce sens la) de 1 ne se trouve pas dans deux, le copier dans 2.

Actuellement, ma macro dispose de la syntaxe pour rafraichir le classeur ainsi qu'un timer me lancant automatiquement la macro toutes les 5 mns.

Mon classeur lui dispose de 2 sheets, l'une est l'export depuis sharepoint de mon dossier 1, l'autre sheet l'export de mon dossier 2.

Je pensais comparer sheet 1 avec sheet 2 (peut être la commande find, je ne sais pas trop), et en fonction du résultat de la comparaison copie le/les fichiers non présents dans le dossier 2.

Je ne souhaite pas de message box, car le classeur est en permanence ouvert compte tenu de la périodicité choisie pour la macro, j'ai donc, une visu en temps réel sur les données du dossier 1 ou 2.

Est ce plus clair?

Je persiste à penser que Power Query pourrait te soulager grandement et s'intégrer dans une solution VBA qui rafraichirait la requête Power Query avant traitement.... soit!

Tu peux t'inspirer de ceci.

Deux tableaux t_Source et t_Cible avec chacun une colonne Fichier

La fonction GetMissingFiles te donne un array contenant les fichiers de t_Source qui ne sont pas dans t_Cible

Pièce jointe 584854

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Function GetMissingFiles(Source As Range, Target As Range) Const GenericFormula As String = "match(""{file}"",t_Cible[Fichier],0)" Dim Formula As String Dim f, Pos Dim s As Range, t As Range   ReDim f(0) For Each s In Source Formula = Replace(GenericFormula, "{file}", s.Value, , , vbTextCompare) Pos = Evaluate(Formula) If IsError(Pos) Then If Not IsEmpty(f(0)) Then ReDim Preserve f(UBound(f) + 1) f(UBound(f)) = s.Value End If Next GetMissingFiles = f End Function   Sub Test() Dim MissingFiles   MissingFiles = GetMissingFiles(Range("t_Source"), Range("t_Cible")) End Sub

---

Pièce jointe 584856

Citation:

---

Envoyé par Pierre Fauconnier

Je persiste à penser que Power Query pourrait te soulager grandement et s'intégrer dans une solution VBA qui rafraichirait la requête Power Query avant traitement.... soit!

---

Pierre, j'apprécie grandement ton aide. Ma macro doit être portative, si un jour je tombe lors de mes nombreux déplacements sur un office antérieur, je me retrouverai de nouveau comme aujourd'hui dépourvu de cette aide (j'entends par la que si une macro n'effectue pas ectte tache c'est moi même qui serai en charge d'effectuer les diverses comparaisons et copie de fichiers.

Je vais tester ce que tu m'as envoyé de suite.

je reviens vers toi dans quelques minutes.

Ce que je te propose ici fonctionnera à partir de la 2007. Les tableaux structurés tels qu'on les connait aujourd'hui n'existaient pas avant. Ils avaient fait une timide apparition dans le menu Données de la 2003 sous l'appellation Liste de données, d'où le raccourci CTRL+L) mais je n'ai jamais testé s'ils étaient manipulables comme nous le faisons aujourd'hui. De façon communément admise, les tableaux structurés, utilisés dans la solution que je te donne, sont "nés" avec la 2007 ;)

Citation:

---

Envoyé par Pierre Fauconnier

Ce que je te propose ici

---

je prends note, bon outre cela je ne dois pas être très doué, je n'ai pas réussi à intégrer la fonction que tu m'as envoyé dans ma maigre macro.
Je te joins tous les éléments qui peuvent t'éclairer sur ce que j'essaie de produire:


Dans ThisWorkbook

Code:

---

1 2 3

Private Sub worbook_open() copyoverlays End Sub

---

Dans le module

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Option Explicit Public timR As Variant Public Const proC = "copyoverlays"   Sub timer()   ThisWorkbook.RefreshAll timR = TimeValue(Now + TimeValue("00:05:00")) Application.OnTime timR, proC End Sub   Sub copyoverlays() ThisWorkbook.RefreshAll timer End Sub

---

Pièce jointe 584871
Sheet1 de mon classeur les fichiers se trouvant dans (exemple) d:\\dossier1

Pièce jointe 584872
Sheet2 de mon classeur les fichiers (ici aucun) se trouvant dans d:\\dossier2

finalité: comparer dossier1 et dossier2, si fichier pas dans 2 le copier

J'ai essayé d'insérer ta fonction mais en réalité n'ayant jamais travaillé qu'avec autre chose que des sub je ne sais pas trop où la placer.

EDIT: les données de mes feuilles sont exportées depuis les bibliotheques de mon sharepoint et se mettent à jour de façon automatique avec

Code:

---

ThisWorkbook.RefreshAll

---

[EDIT] J'ai modifié mon code en fonction de ton illustration qui montre que les noms de fichiers ne contiennent pas le chemin d'accès.

Juste pour info, vu que actualises avec RefreshAll, je pense que tu utilises Power Query sans le savoir pour récupérer les listes des fichiers de ton sharepoint, car depuis la 2016, c'est Power Query qui pilote l'acquisition des données externes dans Excel ;)

La fonction getMissingFiles renvoie un array (tableau VBA) que l'on va parcourir pour récupérer les fichiers que l'on doit copier. Tu places le code de cette fonction dans un module standard et elle s'utilise comme n'importe quelle fonction VBA. A elle seule, elle ne fait pas tout le boulot et il faut donc boucler sur les éléments du tableau qu'elle renvoie pour transférer les fichiers.

Pièce jointe 584899


Voici un exemple fonctionnel avec quelques fichiers. A gauche t_Source, à droite t_Target, pour l'instant incomplet. On y retrouve la fonction, et une procédure TransferFiles qui pourrait être celle que tu appelles sur le timer. Tu peux donc copier le code ci-dessous dans un module standard. Lorsque tu vas démarrer TransferFiles, la macro va appeler la fonction pour créer son tableau des fichiers manquants puis va les transférer avec FileCopy.

Tu noteras que je ne travaille pas avec les feuilles du classeur mais avec les tableaux structurés en utilisant leur référence structurée pour spécifier les plages à traiter (Range("t_Source") et Range("t_Cible")). Tu dois bien entendu adapter ces noms à ceux de tes tableaux...

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 34

Function GetMissingFiles(Source As Range, Target As Range) Const GenericFormula As String = "match(""{file}"",t_Cible[Fichier],0)" Dim Formula As String Dim f, Pos Dim s As Range, t As Range   ReDim f(0) For Each s In Source Formula = Replace(GenericFormula, "{file}", s.Value, , , vbTextCompare) Pos = Evaluate(Formula) If IsError(Pos) Then If Not IsEmpty(f(0)) Then ReDim Preserve f(UBound(f) + 1) f(UBound(f)) = s.Value End If Next GetMissingFiles = f End Function   Sub TranferFiles() Dim MissingFiles Dim SourcePath As String, TargetPath As String Dim SourceName As String, TargetName As String Dim i As Long   SourcePath = "C:\data\Temp\TranfertFichiers\Source\" TargetPath = "C:\data\Temp\TranfertFichiers\Target\"   MissingFiles = GetMissingFiles(Range("t_Source"), Range("t_Cible")) For i = LBound(MissingFiles) To UBound(MissingFiles) SourceName = SourcePath & MissingFiles(i) TargetName = TargetPath & MissingFiles(i) FileCopy SourceName, TargetName Next i End Sub

---

Après l'exécution de TransferFiles et l'actualisation de mes tableaux (par Power Query... ok, je sors... ;) ), on voit que t_Source et t_Cible contiennent les mêmes infos.

Pièce jointe 584900

Cela dit, en s'appuyant sur l'idée de penser d'abord Excel avant de penser VBA, on pourrait également ajouter une colonne à la requête source pour renseigner les fichiers présents dans la source et pas dans la cible. Une simple boucle sur cette colonne permettrait de trouver les fichiers à transférer. Cette colonne formulée va s'adapter à chaque mise à jour de la liste, de sorte que tu as juste à te préoccuper du transfert, après un éventuel recalcul de la feuille

Pièce jointe 584937

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Sub TransferFiles1() Dim SourcePath As String, TargetPath As String Dim SourceName As String, TargetName As String Dim i As Long: i = 1   SourcePath = "C:\data\Temp\TranfertFichiers\Source\" TargetPath = "C:\data\Temp\TranfertFichiers\Target\"   ThisWorkbook.RefreshAll Range("t_Source").Parent.Calculate   Do While i <= Range("t_Source").Rows.Count If Not Range("t_Source[Cible?]")(i).Value Then SourceName = SourcePath & Range("t_Source[Fichier]")(i).Value TargetName = TargetPath & Range("t_Source[Fichier]")(i).Value FileCopy SourceName, TargetName End If i = i + 1 Loop End Sub

---

Citation:

---

Envoyé par Pierre Fauconnier

[B]Cela dit,

---

Bonjour Pierre, je rentre d'une journée de congé, je m'empresse de tester et reviens vers toi!

Pierre, je me permets de te demander une explication, t_source et t_target, font référence je le comprends à mes dossiers source et destination, cependant, quand tu me dis que je dois les adapter à mon classeur pourrais tu être plus clair, si possible, car comme je te l'ai montré je travaille sur deux feuilles, la première VJTF, l'autre HQOFFICIAL.

Si je veux adapter ton code je dois bien à un moment préciser dans la macro ce détail j'imagine.

Je m'excuse encore mais je débute vraiment dans le domaine et sans formation ce n'est pas simple de tout assimilé.


Je vais la faire plus simple, colonne A fichiers source et colonne C destination (comme dans ton exemple)

De ce fait je ne travaille que sur une feuille de classeur, comme toi, avec colonne A et C... j 'ai le message Method Range of Object_global failed

Voici la macro

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 34 35 36 37 38 39 40 41 42

Option Explicit Public timR As Variant Public Const proC = "TransferFiles"   Sub timer() timR = TimeValue(Now + TimeValue("00:00:10")) Application.OnTime timR, proC End Sub Function GetMissingFiles(Source As Range, Target As Range) Const GenericFormula As String = "match(""{file}"",t_Cible[Name],0)" Dim Formula As String Dim f, Pos Dim s As Range, t As Range   ReDim f(0) For Each s In Source Formula = Replace(GenericFormula, "{file}", s.Value, , , vbTextCompare) Pos = Evaluate(Formula) If IsError(Pos) Then If Not IsEmpty(f(0)) Then ReDim Preserve f(UBound(f) + 1) f(UBound(f)) = s.Value End If Next GetMissingFiles = f End Function Sub TranferFiles() Dim MissingFiles Dim SourcePath As String, TargetPath As String Dim SourceName As String, TargetName As String Dim i As Long   SourcePath = "\\hbe-lc2is-portal.hrfd.ms.fr\DavWWWRoot\VJTF(BFA)\Documents\RECEIVED\01 Situational Awareness\01 Reports\OVERLAYS\" TargetPath = "\\hbe-lc2is-portal.hrfd.ms.fr\DavWWWRoot\OfficialHQ\Documents\OVERLAYS\"   MissingFiles = GetMissingFiles(Range("t_Source"), Range("t_Cible")) For i = LBound(MissingFiles) To UBound(MissingFiles) SourceName = SourcePath & MissingFiles(i) TargetName = TargetPath & MissingFiles(i) FileCopy SourceName, TargetName Next i End Sub

---

avec le debug j'ai une erreur à cette ligne

Code:

---

MissingFiles = GetMissingFiles(Range("t_Source"), Range("t_Cible"))

---

Method Range of Object_Global failed

j'ai bien renommé dans la macro ton "fichier" par mon "name" (nom de colonne) étant donné que pour moi il est récupéré depuis le sharepoint et qu'à chaque rafraichissement il remet name.

Ma colonne A est ma source ma colonne C ma destination.

J'avoue ne pas voir et comprendre l'erreur

L'erreur rencontrée me fait dire que tes tableaux ne sont pas nommés t_Source et t_Cible. Peux-tu vérifier cela?

Citation:

---

Envoyé par Pierre Fauconnier

L'erreur rencontrée me fait dire que tes tableaux ne sont pas nommés t_Source et t_Cible. Peux-tu vérifier cela?

---

Les tableaux ne sont pas nommés??? la je suis perdu. Car pour moi, j'ai mon fichier xslm qui contient une feuille, avec deux tableaux (colonneA et C) certes mais à aucun moment je n'ai du nommé un tableau. Vas tu me faire découvrir encore une chose que je ne sais pas? ;-)

Citation:

---

Envoyé par bruno62300

[...]Vas tu me faire découvrir encore une chose que je ne sais pas? ;-)

---

Arf... Je crois bien... je te conseille vivement la lecture de mon tuto: https://fauconnier.developpez.com/tu...ux-structures/


Ca me fait dire que tu n'importes pas tes données via les outils d'Excel... C'est un copier coller d'autres listes ou une macro qui crée ces plage?

Les tableaux structurés sont à mon sens incontournables pour une utilisation efficace d'Excel et une programmation allégée...

Pour tes tableaux, il te suffit de te placer dans une cellule remplie de A puis Insertion > Tableaux > Tableau. Tu coches la case "mon tableau comporte des entêtes puis OK. Tu pourras alors nommer ton tableau via le l'onglet "Création de tableau" tout à droite du ruban (le contrôle pour nommer le tableau est tout à gauche de cet onglet). Tu pratiques à l'identique pour la plage de la colonne C...

Vraiment, je te conseille de te mettre à ces tableaux structurés, appelés aussi tables de données ou tables dynamiques ou tableaux dynamiques)... Que du bonheur et normalement d'une prise en mains rapide... ;)

Citation:

---

Envoyé par Pierre Fauconnier

Ca me fait dire que tu n'importes pas tes données via les outils d'Excel

---

En effet, je passe par sharepoint, sous sharepoint, j'ai une option exportée vers excel voila comment je récupère mes infos.

Je teste ta solution. Je ne vais pas manquer de lire ton tuto afin de parfaire mes maigres notions.

Pour info, Pierre, je ne suis pas du tout issu du milieu informatique ou bureautique...mais aujourd’hui, afin de me soulager dans le travail j'essaie de m'aider des outils technologiques mis à ma disposition.

Je te remercie grandement encore une fois de partager tes connaissances.

Je vais adapter le code pour ne pas travailler avec des tableaux structurés. Tes données commencent en ligne 1 avec une ligne d'entête?

Citation:

---

Envoyé par Pierre Fauconnier

Je vais adapter le code pour ne pas travailler avec des tableaux structurés. Tes données commencent en ligne 1 avec une ligne d'entête?

---

oui la ligne 1 est le titre de la colonne "NAME"
la ligne 2 est mon premier fichier

Citation:

---

Envoyé par Pierre Fauconnier

Je vais adapter le code

---

Bonjour Pierre, as tu une bonne nouvelle pour moi? :-)

Edit: j'ai pu renommer mes tableaux en lisant ta documentation, t_source et t_cible, mais il m'affiche désormais une autre erreur. Je regarde et reviens de nouveau vers toi!

Citation:

---

Envoyé par bruno62300

oui la ligne 1 est le titre de la colonne "NAME"
la ligne 2 est mon premier fichier

---

Pierre ta macro fonctionne!!!! Good job, mais car il ya toujours un mais, une fois qu'il a copié le fichier comme la macro tourne en boucle (logique c'est ce que je veux en cas d'ajout de documents que la copiue se fasse automatiquement) j'ai une erreur

Run-time error 52
Bad file name or number

sur la ligne FileCopy SourceName, TargetName (j'utilise ta macro numero 1 sans la partie vrai/faux)