Discussion :

[bruno62300]

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

[Pierre Fauconnier]

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.

[bruno62300]

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?

[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!

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[bruno62300]

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.

[Pierre Fauconnier]

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

[bruno62300]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub worbook_open()
copyoverlays
End Sub

Dans le module

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Sheet1 de mon classeur les fichiers se trouvant dans (exemple) d:\\dossier1


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 :

Sélectionner tout - Visualiser dans une fenêtre à part

ThisWorkbook.RefreshAll

[Pierre Fauconnier]

[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.




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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Pierre Fauconnier]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[bruno62300]

[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

[bruno62300]

Voici la macro

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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?

[bruno62300]

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? ;-)

[Pierre Fauconnier]

[...]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...

[bruno62300]

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.

[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?

[bruno62300]

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

[bruno62300]

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!

[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)