Ouvrir plusieurs fichiers ayant des variables en un seul clic

Version imprimable

07/09/2016, 16h59
Invité

1 pièce(s) jointe(s)

Ouvrir plusieurs fichiers ayant des variables en un seul clic

Bonjour,

Depuis des mois, je galère à trouver une solution à mon problème. Je précise tout de suite, c'est Excel 2012 à mon boulot donc impossible d'installer des modules complémentaires.

Voilà l'histoire :)

Mon fichier va chercher des données dans différents fichiers sources. Le chemin d'accès de ces fichiers sources changent tous les mois.
Pour rendre mon fichier final dynamique, j'utilise :
• la formule LIEN_HYPERTEXTE avec des variables (une cellule pour le mois U1 et une autre pour l'année T2)
Exemple : =LIEN_HYPERTEXTE("\\serveur\secteurs\Daf\40_Filiales\Reporting Mensuel\8 pages\Réel FY"&T2&"\"&TEXTE(U1;"00")&" FY"&T2&"\Reporting package SNR IBERICA "&TEXTE(U1;"00")&".FY"&T2&".xlsm";"RP Espagne")• la formule INDIRECT également avec des variables
Exemple : =INDIRECT("'\\serveur\secteurs\Daf\Dcb\BASE FIN\Consolidation\FY"&$T$2&"\"&TEXTE($U$1;"00")&" FY"&$T$2&"\Livrables\P&L\[P&L réél brut FY "&$T$2&" "&TEXTE($U$1;"00")&".xls]"&TEXTE($U$1;"00")&"'!$"&B$1&"$"&$S4&"";VRAI)B1 étant une référence pour la colonne et S4 pour la ligne dans les fichiers source

Sans module complémentaire, je n'ai pas d'autre choix que d'ouvrir chaque fichier source (LIEN_HYPERTEXTE) pour que mes formules INDIRECT fonctionnent. Dans le cas contraire, j'obtiens un beau #REF!

Ayant une bonne dizaine de fichiers source à ouvrir, je voudrais rassembler tous les LIEN_HYPERTEXTE dans un seul bouton/macro/code. Un clic et toutes les sources s'ouvrent.

J'espère avoir été clair, j'ajoute un fichier joint pour l'exemple.
Un grand merci par avance !

Proposition

Bonjour primokorn.

Je cherche mais en attendant, en imaginant que tous les fichiers soient dans le même dossier, je peux proposer cette alternative :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim fd as FileDialog, chemin as Variant, fichier as string
Set fd = Application.FileDialog(msoFileDialogFolderPicker) 'Crée un objet boite de dialogue (choix de dossier)
With fd
    If .Show = -1 Then 'Affiche la boite de dialogue et retourne l'action de l'utilisateur
    chemin = fd.SelectedItems(1) & "\" 'La variable chemin prend pour valeur le chemin d'accès avec le nom du dossier sélectionné
    End If
End With
Set fd = Nothing 
fichier = Dir(chemin & "*") 'définit le fichier (ici tous les fichiers du dossier sélectionné)
Do While fichier <> "" ' Boucle while qui s'exécute tant qu'il y a des fichiers dans le répertoire
    Set wb = Workbooks.Open(chemin & fichier)
    Set wb = Nothing
    fichier = Dir
Loop

Dans ce cas, le chemin d'accès est récupéré par la boite de dialogue mais on peut très bien imaginer utiliser directement les valeurs en U1 et T2 pour alimenter la variable chemin.

08/09/2016, 09h27
Invité

Tout d'abord, merci pour ton aide.
Je maîtrise pas trop mal les formules mais je suis une bille avec VBA, alors quelques questions :

• Je le mets où le code ?
• Le chemin d'accès du dossier qui contient les fichiers, je le mentionne entre les deux guillemets ici :

Code:

chemin = fd.SelectedItems(1) & "\"

• Ça me fera un test mais mes fichiers sources ne sont pas forcément tous dans les mêmes dossiers.

Pour le code, il faut faire ALT + F11, insérer un nouveau module et coller tout ça dedans.
le code permet d'ouvrir une fenêtre qui demande de sélectionner le dossier contenant les fichiers, c'est cette information que l'on récupère par "fd.SelectedItems(1)"
Avec fichier = Dir(chemin & "*") on obtient le chemin d'accès complet "*" remplaçant n'importe quel fichier.
Pour spécifier un chemin d'accès, il faudrait passer outre la boite de dialogue :
Code:

1 2 3 4 5 6 7 Dim fd as FileDialog, chemin as Variant, fichier as string, mois as string, annee as string mois = Format(cells(1,21),"00") ' Cellule U1 annee = cells(2,20) ' Cellule T2 chemin = "\\serveur\secteurs\Daf\40_Filiales\Reporting Mensuel\8 pages\Réel FY" & annee & "\" & mois & " FY" & annee & "\Reporting package SNR IBERICA " & mois & ".FY" & annee & ".xlsm" Set wb = Workbooks.Open(chemin) Set wb = Nothing
Avec ce code, on ouvre un seul fichier, il faut donc répéter l'opération pour tous les fichiers mais dans le principe ça devrait ressembler à ça.
J'ai pas testé, il y a probablement des erreurs.

J'ai fini par réussir mais partiellement. Ce code VBA me permet d'ouvrir tous mes fichiers en une seule fois :
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 Sub open_links() ' ' open_links Macro ' ' ' Ouverture P&L brut Range("A1").Select Workbooks.Open Filename:= _ "\\serveur\Daf\Dcb\BASE FIN\Consolidation\FY" & Right(Range("T2"), 2) & "\" & Right(Range("U1"), 2) & " FY" & Right(Range("T2"), 2) & "\Livrables\P&L\P&L réél brut FY " & Right(Range("T2"), 2) & " " & Right(Range("U1"), 2) & ".xls" _ , UpdateLinks:=0 ActiveWindow.Visible = False Windows("P&L réél brut FY " & Right(Range("T2"), 2) & " " & Right(Range("U1"), 2) & ".xls").Visible = True Windows("Septembre.xlsm").Activate ' Ouverture RP Y Workbooks.Open Filename:= _ "\\serveur\Daf\40_Filiales\Reporting Mensuel\8 pages\Réel FY" & Right(Range("T2"), 2) & "\" & Right(Range("U1"), 2) & " FY" & Right(Range("T2"), 2) & "\Reporting package filialeX " & Right(Range("U1"), 2) & ".FY" & Right(Range("T2"), 2) & ".xls" _ , UpdateLinks:=0 ActiveWindow.Visible = False Windows("Reporting package filialeX " & Right(Range("U1"), 2) & ".FY" & Right(Range("T2"), 2) & ".xls").Visible = True Windows("Septembre.xlsm").Activate ' etc avec les autres fichiers ' T2 c'est l'année et U1 le mois End Sub
Ce code ouvre les fichiers et les laisse ouverts. On peut les ouvrir tout en les masquant en supprimant les 2 dernières lignes de chaque partie soit Windows("..........").Visible = True et Windows ("......").Activate

En gardant ce code, je voudrais que la ligne Windows(".........").Activate soit dynamique.
Actuellement, mon fichier de travail c'est September.xlsm mais ça sera Octobre.xlsm le mois prochain.
Si les fichiers que j'ouvre en automatique sont visibles, alors la macro doit repasser obligatoirement par mon fichier de travail pour pouvoir ouvrir les fichiers restants.

Merci.

18/10/2016, 19h46
Matiou73

Tu peux utiliser la fonction suivante :

Code:

MonFichier = ActiveWorkbook.Name

et pour y revenir :

Code:

Workbooks(MonFichier).Activate
19/10/2016, 08h57
Invité

Parfait, je te remercie beaucoup !
Tout fonctionne comme espéré.

Bonne journée.
19/10/2016, 09h35
RyuAutodidacte

Bonjour,
Petite question :
Tous les noms des fichiers sources sont connus a l'avance voir même générés dans le fichier principal ?
comme le dossier principal rassemblant tous les fiches des sources dont le nom est le mois et l'année ?
19/10/2016, 10h35
Invité

Bonjour,

Les fichiers source sont toujours stockés selon la même arborescence.
Seuls le mois et l'année changent. Du coup, les fichiers à récupérer changement tous les mois. J'utilise un fichier à part (calendrier Excel) pour gérer l'année, le mois et les jours ouvrables du mois en-cours. Toutes mes variables sont calculées automatiquement par rapport à aujourd'hui (cellules T2 et U1).
19/10/2016, 10h48
RyuAutodidacte

il y aurais bien une autre solution à tester qui serait de scanner le dossier principal du mois et de l'année en cours pour ouvrir chaque fichiers voulus selon une liste des fichiers à ouvrir

Voilà,
dsl je n'ai pas eu le temps de le tester et je n'ai pas de PC sous la main ni de serveur
Bien déterminer la racine de départ et je n'ai mis que 2 fichiers en espérant de les avoir bien écris

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
Sub OuvrirFichiers()
    chemin = "\\serveur\Daf\Dcb\BASE FIN\Consolidation\FY" & Right(Range("T2"), 2) & "\" & Right(Range("U1"), 2) & " FY" & Right(Range("T2"), 2) & "\Livrables\P&L" 'à détermine la racine de départ
   cherche chemin
   ThisWorkbook.Activate
End Sub
 
Function cherche(dossier)
        Dim chemin As String, itemsvu As String, nbitemsVu As Long, i As Long
        MesFichier = Array("P&L réél brut FY " & Right(Range("T2"), 2) & " " & Right(Range("U1"), 2) & ".xls", _
                            "Reporting package filialeX " & Right(Range("U1"), 2) & ".FY" & Right(Range("T2"), 2) & ".xls")
 
    chemin = dossier & "\"
    itemsvu = Dir(chemin, vbDirectory)
    Do
        nbitemsVu = nbitemsVu + 1
        If itemsvu <> "." And itemsvu <> ".." Then
            If (GetAttr(chemin & itemsvu) And vbDirectory) = vbDirectory Then
                Call cherche(chemin & itemsvu)
                 itemsvu = Dir(chemin, vbDirectory)
                For i = 1 To nbitemsVu - 1: itemsvu = Dir: Next i
            Else
                Fic = Application.Match(itemsvu, MesFichier, 0)
                If not IsError(Fic) Then
                     Workbooks.Open Filename:=itemsvu, UpdateLinks:=0
                     Windows(itemsvu).Visible = True
                End If
            End If
        End If
        itemsvu = Dir
    Loop While itemsvu <> ""
   End Function

Edit : une petite erreur dans le code, je l'ai rectifié dsl

19/10/2016, 13h49
Invité

Merci pour ta participation mais quel intérêt par rapport à ce que j'ai déjà ? Ça me semble trop compliqué pour mes faibles connaissances VBA :)

Pour info, tous les fichiers source ne sont pas forcément dans le même dossier et il n'est pas nécessaire d'ouvrir tous les fichiers contenus dans les dossiers.
19/10/2016, 14h00
RyuAutodidacte

ici on n'ouvre seulement les fichiers demander, mais pas tous les fichiers (dsl une erreur dans le code au 1er envoi mais rectifié)
on scan les fichiers voulu dans un dossier maitre et ses sous dossier et dès qui top l'un des fichier voulu il ouvre le fichier (on peut même rajouter un compteur lorsque les 12 fichiers sont ouvert il arrete la procédure)
on a plus qu'un seul chemin à indiquer et quelque soit l'endroit ou le fichier se trouve, il l'ouvre

Edit :
Il est vrai que par rapport au niveau VBA la 1ère solution trouvé est plus simple, mais imagine qu'il y a 50 fichiers à ouvrir cela va faire beaucoup de lignes de codes pour les 50 fichiers alors que la on lui indiquera seulement les noms des 50 fichiers
Et si le nom des fichiers sont récupérable dans une plage on aura même pas besoin de les écrire dans le code mais de récupérer la plage avec tous les noms
19/10/2016, 14h18
Invité

OK, je comprends mieux. Merci.
Pour le moment, j'ai 10-15 fichiers max à ouvrir alors je reste sur le "simple et long" :) Quand j'aurai un peu de temps, je regarderai ta solution.