Mettre à jour des cellules liées à une feuille de calcul intégrée à Word

Bonjour,

Novice sur Excel, j'ai besoin de votre aide sur Excel 2016.

J'ai créé un document Word modèle dans lequel j'ai inséré une feuille Excel (Insertion -> Tableau -> Feuille de calcul Excel). Ce document est rempli par un utilisateur qui double clique sur le tableau et remplit les différentes cellules, notamment la cellule F24, consommables, puis enregistre le fichier Word avec une référence comme nom.

Je vous mets en pièces jointes deux exemples de documents types remplis avec les références PALPP001_1 et PALPP002_1.

A coté de cela, j'ai créé un fichier Excel appelé Classeur_Gamme (dispo également en pièce jointe) dans lequel j'ai deux colonnes :

• Gamme
• Consommables

Dans Gamme, on y retrouve une liste des références rentrées par les utilisateurs et dans Consommables, les consommables liés à ces références. Comme vous l'aurez deviné, je veux lier les cellules consommables du Classeur_Gamme avec la cellule F24 des différentes feuilles Excel intégrées à des fichiers Word.

Je l'ai fait en utilisant la formule INDIRECT et en sélectionnant la cellule dans la feuille de calcul intégrée :

=INDIRECT("'[Feuille de calcul dans Chemin " & A2  & ".docx]Feuil1'!$F$24")

(Chemin étant le chemin du fichier Word et A2 est la cellule contenant la référence)

Cette formule fonctionne parfaitement mais le problème est que les données sont en "#REF!" si la feuille de calcul Excel correspondante n'est pas ouverte en même temps.

J'aimerais donc aller plus loin en créant une macro (car je pense que c'est le seul moyen) permettant de mettre à jour les cellules automatiquement, sans avoir à ouvrir la feuille.

Je suis très novice en VBA mais j'ai essayé de créer une macro qui ouvre et referme rapidement des classeurs pour mettre à jour.

Sub MiseAJour()
    'cherche dernière ligne de la colonne
    derniere_ligne = Cells(Rows.Count, 1).End(xlUp).Row

    For Each C In Worksheets("Feuil1").Range(Cells(2, 1), Cells(derniere_ligne, 1)).Cells
        gamme = C.Value

        chemin = "Chemin du fichier" & gamme & ".xlsx"

    'Ouvre
        Set src = Workbooks.Open(chemin)

    'Ferme
        src.Close
        Set src = Nothing

    Next

End Sub

Plusieurs problèmes à cette macro :

- Je ne peux pas mettre le chemin exact de la feuille de calcul, c'est pour cela que j'ai fait un essai avec un fichier xlsx directement. J'ai essayé en reprenant le chemin ( Feuille de calcul dans Chemin\PALPP001_1.docx)mais j'obtiens une erreur. Je pense que cela vient de la partie "Feuille de calcul dans", je ne sais pas si VBA peut le lire ou sait l'interpreter. Peut-être est-ce parce que cette partie n'est pas en anglais.

- L'autre soucis est lorsque je lance la macro : cela fonctionne, les classeurs s'ouvrent et se referment, les cellules se mettent à jour avec la valeur mais elle disparait directement et redevient "#REF!". Seule la dernière ligne est mise à jour.

Voici mon problème exposé. Je ne sais pas si mon début de VBA est dans la bonne direction.

Je suis à l'écoute de tous vos conseils et vos solutions. Je vous remercie par avance.