VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

[__xD]

Bonjour à tous,

Pour mon stage informatique, je dois modifier un logiciel déjà existant codé en VBA. La problématique est la suivante :
-Chaque tableur excel possède un certain nombre d'onglets, dont des annexes
-Une même annexe peut être présente dans pluieurs fichiers excels différents
-Ces annexes ne sont pas reliées entre elles, actuellement lorsqu'une modification est effectuée sur une annexe, l'utilisateur doit aller faire cette mise à jour manuellement dans chaque tableur où l'annexe est présente.

Certaines annexes sont présentes dans 50 tableurs... 😛
Voici donc ce que je cherche à faire : je cherche à créer de nouveaux onglets (mon code fonctionne pour faire cela) et insérer l'annexe désirée dans cet onglet. Cependant, mon code procède par copier coller. Je voudrais maintenir le lien entre le nouvel onglet et le fichier, pour que si l'utilisateur effectue une modification dans l'onglet contenant l'annexe, cette modification s'effectuera également dans le fichier source...
Cela est-il possible ?

Merci de votre aide 🙂

PS : je joins le code permettant d'ajouter un onglet et d'y copier le contenu de l'annexe. 🙂

Sub AjoutAnnexe()

Dim classeurSource As Workbook, classeurDestination As Workbook
Dim fileToOpen As String

Static Compteur As Byte
'définir le classeur destination
Set classeurDestination = ThisWorkbook

Set classeurSource = Application.Workbooks.Open(Application.GetOpenFilename(), , True)

'Création d'un nouvel onglet
Compteur = Compteur + 1

classeurDestination.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Annexe_" & Compteur

'copier les données de la "Feuil1" du classeur source vers la "Feuil1" du classeur destination
classeurSource.Sheets("Sheet1").Cells.Copy classeurDestination.Sheets("Annexe_" & Compteur).Range("A1")

'fermer le classeur source

classeurSource.Close False

End Sub

 

[david84]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour,
as-tu testé le fait d'insérer dans ton code un collage avec liaison ?
Tu crée ainsi une liaison entre les 2 classeurs et lorsque tu ouvres le classeur de destination il te propose alors de mettre le lien à jour et donc d'actualiser le classeur.
A+

 

[__xD]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour,

Qu'entends-tu par collage avec liaison ? S'agit-il d'insérer un lien fixe dans le code ? Car il existe plusieurs annexes différentes et il faut donc que l'utilisateur puisse choisir quelle annexe ajouter...

 

[david84]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Re
pas besoin de macro pour cela : tu crées un lien entre ton classeur source et un autre classeur. A partir de ce moment, lorsque tu ouvriras cet autre classeur, Excel te proposera d'actualiser les données de ce classeur lié au classeur source.
A moins que je n'ai pas compris ta demande...
A+

 

[__xD]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Re,
effectivement, cela doit pouvoir fonctionner, je vais essayer. Au delà de la réalisation il faut également que le fonctionnement soit simplifié au maximum si l'utilisateur (novice) a besoin d'apporter des modifications par la suite... je vais efffectuer quelques test voir si ce fonctionnement est optimal. Merci de ton aide !

 

[gauguin]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour,

Vous pouvez utiliser, lors de la création automatique via VBA du nouvel onglet, la méthode Paste en fixant son argument Link à True.

LaFeuille.Paste Link:=True

Cordialement

 

[__xD]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour,

J'ai utilisé effectivement Paste, mais mon code ne fonctionne pas...
J'ai d'abord défini les deux classeurs destination et source pour l'ajout de l'onglet, et j'ai mis en place une vérification de si l'onglet existe déjà en utilisant l'objet Err. (Le compteur se remettant à zéro lorsque le tableau est fermé, il faut que l'utilisateur puisse ajouter une annexe aisément et qu'il n'y ait pas de conflit si une annexe_1 a été créée dans la session précédente)
Je ne comprends pas pourquoi cela ne fonctionne pas, la macro fonctionne jusqu'au moment où elle me propose quel fichier est à annexer, elle semble l'ouvrir puis le refermer mais il n'y aucun ajout d'onglet, et par conséquent pas de l'annexe... 🙁

Sub AjoutAnnexe()

Dim classeurSource As Workbook, classeurDestination As Workbook
Dim fileToOpen As String
Static Compteur As Byte
Dim Err As ErrObject

'définir le classeur destination qui reçoit l'annexe
Set classeurDestination = ThisWorkbook
Set classeurSource = Application.Workbooks.Open(Application.GetOpenFilename(), , True)    'choix du fichier à annexer
Compteur = 1
'Set fileToOpen = Application.GetOpenFilename()

    Do
'Vérifie si l'onglet existe, le crée s'il n'existe pas
    On Error Resume Next
    Err.Number = 0
    classeurDestination.Sheets.Add (After = Worksheets(Worksheets.Count))
    classeurDestination.Sheets(Worksheets.Count).Name = "Annexe_" & Compteur
    If Err.Number <> 0 Then
        Application.DisplayAlerts = False
        classeurDestination.Sheets(Worksheets.Count).Delete 'détruit la feuille créée
        Application.DisplayAlerts = True
        Compteur = Compteur + 1
    End If
    Loop While Err.Number <> 0


'copier les données de la "Feuil1" du classeur source vers la "Feuil1" du classeur destination
classeurSource.Sheets("Sheet1").Cells.Paste classeurDestination.Sheets("Annexe_" & Compteur).Range("A1")
classeSource.Sheets("Sheet1").Paste Link:=True

'fermer le classeur source

classeurSource.Close False

End Sub

 

[david84]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Re
pourquoi utiliser Application.GetOpenFilename alors que si c'est ton classeur source, tu dois connaître son chemin non ?
Si c'est le cas :

Dim rep As String
repertoire = indiquer le chemin du classeur source
'définir le classeur destination qui reçoit l'annexe
Set classeurDestination = ThisWorkbook
Workbooks.Open Filename:=repertoire

doit suffire.
A+

 

[__xD]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Effectivement, je connais son chemin, mais c'est pour permettre à l'usager futur de ne pas avoir à aller dans le code s'il déplace ses annexes où si les différentes annexes ne sont pas dans le même dossier 🙂
Cela ne règle pas mon problème global, la recherche de fichier à ouvrir fonctionne parfaitement, mais après, suivant comment je présente mon code, il rentre dans une boucle infinie, ou ne copie pas, ou s'arrête avant la fin, bref, je ne vois pas le problème. Tu as une idée ? J'ai dû oublier quelque chose quelque part 😱

 

[david84]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Re
sans tester, difficile de se rendre compte de ce qui peut clocher.
Je suis très pris en ce moment mais si tu places sur le forum un fichier source et un autre fichier (tous deux avec quelques données histoire de tester) en expliquant concrètement le résultat que tu veux obtenir au final, moi ou un autre pourrions tester ton code.
A+

 

[__xD]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour,

Merci beaucoup, je joins donc le tableur 1 qui contient le code ainsi que l'annexe à ajouter. Voici la totalité de mes contraintes (je n'ai pas tout mis au début pour ne pas faire fuir les messies 😉 )
-L'utilisateur doit pouvoir ajouter une annexe qui reste lié au fichier de base
-Il y a beaucoup d'annexes différentes, d'où le choix du fichier à l'ouverture pour ne pas que l'utilisateur change le nom du chemin
-Tout doit être automatique pour être simple : l'onglet doit être rajouté tout à droite et sa nomination doit se faire seule, j'ai choisi de les appeler "Annexe_1" puis 2, 3, etc. Il faut donc que le programme procède à une vérification : si l'onglet Annexe_x existe, on incrémente x et on recommence. J'ai mis en place une fonction booléenne qui effectue cette vérification, et lorsqu'elle est prise seule, la fonction fonctionne.
-Une fois la nomination effectuée, on copie le contenu d'annexe 1, et avec paste link=true, je mantiens le lien si j'ai bien compris.

Voici maintenant ce qui ne fonctionne pas... 😡
-Lors de la boucle de vérification

Do
Compteur = Compteur + 1
Loop While FeuilleExiste("Annexe_" & Compteur) = True 'Si la feuille existe, on continue, sinon on arrête

J'ai une erreur de dépassement de capacité, or au départ aucun onglet n'existe encore, la fonction devrait donc s'arrêter rapidement, je ne comprends pas...
-J'ai sans cesse une erreur à ce niveau du code :

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Annexe_" & Compteur

classeurSource.Sheets("Sheet1").Cells.Copy classeurDestination.Sheets("Annexe_" & Compteur).Range("A1")

classeurDestination.Sheets("Annexe_" & Compteur).Paste Link:=True

Un coup c'est que la méthode Add de Sheets a échoué... Ou alors que telle méthode n'appartient pas à tel objet... Et enfin, que la méthode Paste a échoué...

Merci si vous avez des idées, j'ai passé la journée la dessus hier, et impossible de trouver. J'espère que mes explications sont suffisamment complètes 🙂

 

[david84]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour,
j'ai regardé vite fait et je pense que le fait d'utiliser le lien sur l'ensemble des cellules de la feuille n'est pas viable (procédure trop longue car tu lies l'ensemble des cellules de l'onglet en comme dans la version 2007 tu as 1 048 576 lignes et 16 384 colonnes je te laisse faire le calcul).
Donc, soit tu ne lies qu'une plage déterminée de ton onglet et pourquoi pas si cette plage n'est pas trop importante, soit il te faut trouver une autre solution.

Si je comprends bien : tu as un classeur source et différents fichiers comportant des annexes et tu veux que lorsque l'on modifie une annexe quelconque, ton classeur source et les autres fichiers comportant une annexe du même nom soient également modifiées.
Est-ce bien cela ?
Cela suppose donc que les annexes comportant le même nom soient en fait identiques.
Est-ce bien le cas ?
Actuellement le classeur source existe-t-il ou est-ce une idée que tu as eue pour tenter de réunir en un seul fichier l'ensemble de tes annexes ?
Concernant les annexes, leur nombre est il appelé à évoluer (création de nouvelles annexes, suppression d'anciennes annexes, modification de leur nom,etc.) ?
A+

 

[__xD]

Re : VBA Insérer fichier dans un nouvel onglet et maintenant le lien avec ce fichier

Bonjour david84,

Effectivement, les annexes portant le même nom sont identiques. Mon classeur source n'a pas pour but de réunir toutes les annexes dans un même tableur, il me fait juste office de test pour vérifier que l'ajout et la mise à jour se passent bien. Dans le contexte du projet, l'utilisateur ouvre un tableur qui planifie la production de plusieurs équipes, et à ce tableur (contenant parfois 30 ou 40 onglets) on va rajouter une ou plusieurs annexes. Le programme imprime ensuite tous les onglets un à un : cela permet de ne plus avoir qu'à distribuer les feuilles par la suite. Si deux équipes ont besoin de l'annexe x, alors il faudra l'ajouter deux fois au tableur pour l'impression.

Je me suis arrachée les cheveux un bon bout de temps et au final cela était inutile : au fond, le nom du nouvel onglet n'est pas important, toute mon histoire sur l'incrémentation du compteur (qui ne marche pas 😱 ), je l'ai simplement retirée du code.

Voilà mon code final, qui fonctionne 🙂

 Sub AjoutAnnexe()
Dim classeurSource As Workbook, classeurDestination As Workbook
Set classeurDestination = ThisWorkbook
Set classeurSource = Application.Workbooks.Open(Application.GetOpenFilename(), , False, , , , , , , True)


With classeurDestination
    .Activate
    Sheets.Add After:=Sheets(Sheets.Count)
    classeurSource.Sheets(1).UsedRange.Copy
    .Sheets(Sheets.Count).Paste Link:=True
End With

ActiveWindow.DisplayZeros = False

classeurSource.Close False
End Sub