Fonction INDIRECT / MACRO ALTERNATIVE

[Charles.rat]

Bonjour à tous et d'avance merci pour vos contribution

Je vous propose un cas spécifique, j'ai un problème récurrent pour tout utilisateur EXCEL, à savoir la fonction indirect ne fonctionne qu'avec les fichiers (en lien) ouverts. je souhaiterai régler le problème par MACRO et bien sur je ne maitrise pas le VB

Je poste mon Fichier ; celui ci est une fichier de pointage d'heure pour divers utilisateurs et pour divers projets. Chaque utilisateur à son propre fichier de pointage individuel nommé par son numéro d'identifiant (j ai pas posté les exemple des fichiers de pointage, celui-ci n'a pas d'intérêt pour ma demande.
Je souhaite une macro qui me permet de remplir automatiquement les liens vers les fichiers individuelles de pointage à la création d'un nouvel utilisateur.

Voir ci-dessous la structure du fichier " Releve heures 2016_TEST" // note j'ai simplement verrouillé les cellule que je souhaite protéger, sans mot de passe

Détail:

la feuille "BILAN_2016" , feuille parent , et la feuille ou je renseigne un nouvel utilisateur ou un nouveau projet
Quand je renseigne un nouvel utilisateur, je rentre un nom et un N° d'utilisateur. je crée un fichier de pointage individuel nommé par le n° utilisateur .xlsx

Je souhaiterai que la MACRO balai les lignes de mes différentes feuilles et renseigne le lien (vers les fichiers de pointage individuelle) ave le N° utilisateur qui se trouve en colonne B de la feuille "BILAn_2016" ; et ça pour toutes les feuilles de ce classeur (note, j'ai enlevé les mois après février pour alléger le fichier). Ainsi j'ai pas à faire du ctrl F remplacé sur toutes les feuilles à chaque fois que je crée un nouvel utilisateur.

en espérant que ma demande est asse clair, d'avance merci

 

[vgendron]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Hello

première chose
dans ta feuille bilan..
plutot que de te taper Janvier E3 + Février E3 + Mars E3 ....+décembre E3
tu peux simplifer en faisant
=SOMME(Janvier_2016écembre_2016!E3)

ca te prend la somme des cellules E3 de TOUTES les feuilles situées ENTRE la feuille Janvier et la feuille Décembre.
et oui. j'aime bien cette astuce aussi ;-)

pour l'histoire du lien...
quand j'ouvre ton fichier.. alors que je n'ai pas les fichiers 20 360 385 350 370 .xls, les colonnes sont bien remplies avec des valeurs..??
et le contenu de la cellule (E4) par exemple est

='C:\users\[360.xlsx]Janvier_2016'!E$2

donc je me dis que peut etre. pas besoin de la fonction indirect mais juste de

='C:\users\[ &C4 &".xlsx]Janvier_2016'!E$2

sinon. il y a une fonction indirect.ext (qui je crois n'a pas besoin d'ouvrir le fichier)??
si tu ne l'as pas. installe le module "MoreFunc"
tu peux le trouver ici par exemple
Morefunc | Libérez les revues de téléchargement et de logiciel | CNET Download.com

 

[Charles.rat]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Hello Vgendron

Merci pour ta réponse

Concernant ta proposition, le code que tu me propose ne fonctionne pas tel que tu l'as saisi; avec une fonction indirect, le PB de devoir laisse le classeur enfant ouvert.

Concernant More Func, cela ne fonctionne pas avec Excel 2013 64bits. Le fichier est public, je ne peux pas demander à tous les utilisateurs de l'installer.

Merci

 

[Charles.rat]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Hello Vgendron

Merci pour ta réponse

Concernant ta proposition, le code que tu me propose ne fonctionne pas tel que tu l'as saisi; avec une fonction indirect, le PB de devoir laisse le classeur enfant ouvert.

Concernant More Func, cela ne fonctionne pas avec Excel 2013 64bits. Le fichier est public, je ne peux pas demander à tous les utilisateurs de l'installer.

Merci

En complément, merci pour ton astuce, c'est vrai que c'est plus pro

Le fichier est rempli, je crois que le fichier garde les dernières valeurs chargé, par contre si tu recalcule, il ne trouvera pas les liens vers les fichiers enfants.

Merci et bonne journée

 

[Modeste]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Bonjour Charles.rat,
Salut vgendron,

Pas certain de comprendre, mais l'idée serait d'inscrire -par macro- les formules des cellules E3 et suivantes des feuilles Janvier_2016 et Février_2016, en indiquant comme nom de fichier, la valeur en colonne B?
Si c'est bien de ça qu'il s'agit, je ne suis pas convaincu qu'une macro qui passerait en revue toutes les lignes de chacune de feuilles qui figureraient dans le classeur soit une bonne idée (ça risque d'être longuet ). Il me semble que je m'occuperais d'une ligne uniquement.

Teste ce qui suit, dans un premier temps:

• clic droit sur l'onglet de la feuille Février > Visualiser le code
• colle les lignes ci-dessous dans la nouvelle fenêtre qui apparaît
• double-clic sur une cellule de la colonne B (où un N° est renseigné) et valide tout de suite (c'est une formule qui figure en colonne B, je le sais, mais c'est juste pour que tu vérifies si le résultat est celui que tu veux!)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 And Target.Row > 2 Then
    Cells(Target.Row, 5).Resize(1, 73).Formula = "='C:\XLD\Charles.rat\[" & Target & ".xlsx]" & Me.Name & "'!E$2"
End If
End Sub

Le chemin renseigné C:\XLD\Charles.rat doit être adapté (sans "toucher" aux apostrophes, barres obliques inverses ou crochets droits
Pour que la chose fonctionne, il faut (évidemment) que le fichier-cible (20.xlsx, 360.xlsx, etc.) existe préalablement
J'ai considéré que le nom de la feuille serait identique dans le fichier récapitulatif et dans le fichier-cible

 

[Charles.rat]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Bonjour Modeste et Merci d'ouvrir la voix.

Tu as bien compris ma demande et ta macro fonctionne, voir mes remarques ci-dessous pour achever le boulot :

- quand je crée un nouvel utilisateur, je rempli une nouvelle ligne avec un identifiant dans la colonne A et un numéro d'utilisateur dans la colonne B. Les feuilles de relevées mensuelles se remplisses automatiquement par lien.

- Je souhaiterai que ta macro balai toutes les lignes des fichiers de relevés mensuelles (sauf celle avec un zéro dans la colonne A).

- je peux créer un bouton pour lancer ta macro quand je crée un nouvel utilisateur.

Merci de ton aide

 

[Modeste]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Bonjour,

Bonjour Modeste et Merci d'ouvrir la voix

Ah ça je ne sais pas trop: je ne suis pas d'un naturel très loquace Pour ce qui est d'ouvrir la voie, d'autres s'en sont chargés bien avant moi!

Je souhaiterai que ta macro balai toutes les lignes des fichiers de relevés mensuelles (sauf celle avec un zéro dans la colonne A)

Ce que je disais, c'était que je ne voyais pas l'intérêt de balayer toutes les lignes de toutes les feuilles, alors que la modification ne concerne qu'une personne à la fois.
Je proposerais donc (puisque les mêmes personnes sont sur la même ligne dans toutes les feuilles) de ne scanner que ces lignes-là. La question qui demeure étant: "le fichier 20.xlsx ou 385.xlsx existe-t-il avant l'encodage du N° en colonne B de la feuille Bilan_2016?"
Pour ce qui est des feuilles, les 12 feuilles de l'année sont-elles présentes dans le classeur ou les ajoutes-tu au fur et à mesure? Idem, pour les classeurs individuels: les 12 feuilles mensuelles seront-elles présentes? En dehors de ces 12 feuilles et de la feuille Bilan, le classeur de consolidation en contient-il d'autres? La feuille Bilan est-elle toujours en 1[SUP]re[/SUP] position?

Si les fichiers existent préalablement, que tous les classeurs contiennent les 12 feuilles des mois de 2016, nommées à l'identique et qu'elles figurent de la 2[SUP]e[/SUP] à la 13[SUP]e[/SUP] position dans ton classeur récapitulatif, supprime la macro de la feuille Février et colle le code modifié comme ci-dessous, dans la fenêtre de code de la feuille Bilan_2016. La macro s'exécutera au changement de valeur en colonne B de cette feuille Bilan et mettra à jour la formule dans les 12 feuilles.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 And Target.Row > 2 Then
     If Target <> "" Then
        For f = 2 To 13
            Sheets(f).Cells(Target.Row, 5).Resize(1, 73).Formula = "='C:\XLD\Charles.rat\[" & Target & ".xlsx]" & Sheets(f).Name & "'!E$2"
        Next f
    End If
End If
End Sub

Le chemin est toujours à adapter!

 

[Charles.rat]

Re : Fonction INDIRECT / MACRO ALTERNATIVE

Merci modeste

c'est parfait !!

Encore merci pour ton aide