Liaison entre 2 classeurs excel

[bobtoison]

Bonjour à tous,

Je cherche à automatiser une saisie de données en créant des liaisons entre 2 classeurs excel, l'un (que l'on appellera classeur A) que je reçois tous les jours avec des données brutes, chaque jour classeur A rajoute un onglet supplémentaire avec un nouveau tableau de données.
L'autre classeur, que j'ai créé (classeur B) incorpore les données de classeur A et les met en forme. Ce classeur B me sert pour un troisieme classeur et il en est une etape indispensable.

Je vous expose mon problème :

Nous sommes le 1er decembre

le classeur A n'a qu'un seul onglet le 1er decembre
Chaque jour classeur A ajoute un onglet correspondant au jour supplémentaire. Au 31 décembre il contient donc 31 onglets.

je conçois classeur B pour tout le mois de décembre
il contient 31 onglets qui correspondent aux 31 jours du mois
Dans le 1er onglet je crée un lien tout simple en A1 "=ClasseurA/onglet1/plage de cellules"
Nous sommes le 1er decembre donc tout fonctionne, l'onglet 1 existe bel et bien dans le classeur A donc les données sont rappatriées sans problème.

Dans les onglets 2,3,4,5,...,31 de mon classeur B, je crée des liaisons avec le classeur A type "=ClasseurA/onglet2,3,4,....31/plage de données" en anticipant leurs arrivées les jours suivants.
Je ferme classeur B.

Le 2 décembre

je reçois classeur A qui contient maintenant 2 onglets
J'ouvre classeur B, il actualise onglet 1 et onglet 2 sans problème.
Alors bien sûr il n'actualise pas onglet 3,4,5,6.....,31 puisque ces derniers n'existent pas encore dans classeur A. A la rigueur, ça n'est pas grave. Le problème est que classeur B a remplacé dans les 29 onglets restant tous mes liens type "=ClasseurA/onglet3,4,5...,31/plage de données" par "=ClasseurA/#REF/plage de données".
Je suis donc obligé, tous les jours, de remplacer tous les "#REF" par "onglet 3,4,5....,31".

Je me demandais donc, au terme de ce long développement (merci pour votre patience), s'il existait un moyen pour que les liens de classeur B sur les onglets de classeur A qui n'existent pas encore, restent tels quels ?
Une solution existe-t-elle ?
Est-ce que c'est peine perdue ?

Je ne cherche pas spécialement une solution toute faite, mais juste savoir si une solution existe.

Merci beaucoup à ceux qui auront eu la patience de lire ce casse tête.

Bonne journée

Cordialement,

Bob

ci dessous des images pour illsutrer mes propos...

Classeur B


Classeur B anticipant l'onglet 03-01-2009


Classeur A au 2 decembre donc sans onglet 03-01-2009


Classeur B au 2 decembre, récupère les données des onglets 01-01-2009 et 02-01-2009 du classeur A mais dans l'onglet 03-01-2009 remplace mon lien vers l'onglet à venir du classeur A par "#REF" (entouré en rouge)

 

[pierrejean]

Re : Liaison entre 2 classeurs excel

bonjour

A tester

quelque chose du genre:

=SI(ESTERREUR(TaFormule);"";TaFormule)

 

[bobtoison]

Re : Liaison entre 2 classeurs excel

Bonjour Pierre Jean,

Merci beaucoup pour votre réponse.
Votre formule répond bien à un problème de "#REF", mais pas celui qui me pose problème.

Votre formule permet d'éliminer les "#REF" présent dans un tableau en les remplaçant par "". Mon probleme ce n'est pas les "REF" présent dans mon tableau, mais le "REF" présent dans ma formule.(cf dernière image de mon post)


Classeur B est fait pour automatiser un processus d'incorporation de données du classeur A.
J'ai créé des formules dans classeur B pour appeler des données dans des onglets qui n'existent pas encore en classeur A.
De manière à ce que chaque jour lorsque j'enregistre mon nouveau classeur A
classeur B prenne automatiquement les données du nouvel onglet.
Problème : toutes les formules du classeur B qui appellent les données d'onglets inexistant
passent de "=ClasseurA/onglet3/plage de données" à "=ClasseurA/"#REF"/plage de données"
donc les formules du classeur B deviennent inutiles et je dois tous les jours réécrire "onglet3" "onglet4" "onglet5" etc dans chacune de mes formules...
ça doit pas paraitre très clair... Peut être que j'essaie juste de faire un truc impossible....

En tout cas merci beaucoup d'avoir réfléchi sur mon cas,

Bonne soirée,

bob

 

[JCGL]

Re : Liaison entre 2 classeurs excel

Bonjour à tous,

Un INDIRECT() pourrait, peut-être, faire l'affaire en récupérant le nom de la feuille en cours par GAUCHE() et CELLULE() ?

Voir l'aide INDIRECT() par F1

A+ à tous

 

[CBernardT]

Re : Liaison entre 2 classeurs excel

Bonjour bobtoison, Pierrejean et JCGL,

Il me paraît peu probable de pouvoir créer une liaison qui n'existe pas par formule. Je te propose de réaliser une petite macro qui créera les liaisons en éditant des formules adaptées aux classeurs, feuilles et cellules existants.

 

[bobtoison]

Re : Liaison entre 2 classeurs excel

Bonjour JCGL et CbernardT,

Merci pour vos propositions.
JCGL
J'avoue ne pas connaitre les fonctions INDIR() GAUCHE() et CELL()
Je suis super novice sur Excel (je me limite aux fonctions super basiques jusqu a RECHERCHEV)
Je vais essayer de voir comment utiliser les 3 que tu me proposes.
Au moins j'aurai découvert 3 nouvelles fonctions.

CBernardT,
Je craignais qu'une macro soit inevitable...
ça va être mon baptême (je n'y ai jamais touché)
Aurais-tu un conseil pour débuter ou suis-je irrécupérable ?
Je sais qu'une macro fonctionne comme la fonction Enregistrer d'un magnetoscope, on la lance et toutes les actions qu'on accomplit pendant l' "enregistrement" sont mémorisés par la macro. Une fos qu'on relance la macro elle accomplit tous les gestes/clics/operations qu'on a effectuées.
J'ai du mal à saisir celà dit comment je peux rendre une macro "dynamique" (qui prend en considération que classeur A ajoute un onglet chaque jour....)

En tout cas encore une fois merci pour votre aide.

Cordialement

Bob

 

[CBernardT]

Re : Liaison entre 2 classeurs excel

Bonjour bobtoison, Pierrejean et JCGL,

Je te propose de réaliser un extrait de tes classeurs et de les placer en Zip sur ce post afin que l'on puisse étudier la manière de procéder.

pour la petite histoire, il y a quelques années, j’étais au même niveau que toi et patiemment j’ai pu me perfectionner dans la programmation VBA d’Excel au travers des exemples présents sur le forum et de l’aide apportée par des participants et ainsi me donner la possibilité maintenant de réaliser quasiment toutes les simulations et applications possibles.

Donc courage et persévérance sont les deux ..... de la réussite en VBA.

 

[bobtoison]

Re : Liaison entre 2 classeurs excel

Bonjour CBernardT, Pierre Jean, JCGL,

JCGL
J'ai testé INDIRECT, GAUCHE et CELL,
Le souci c'est qu'elles se contentent de changer le contenu du tableau a l'interieur de mes onglets du classeur B, la formule du classeur B faisant référence à l'onglet qui n'existe pas encore dans le classeur A quant à elle change irrémédiablement ma référence à l'onglet qui n'existe pas dans classeur A en "#REF" rendant toute automatisation impossible.
En tout cas, encore une fois, merci d'avoir réfléchi sur mon cas, j'ai pu au moins découvrir 3 nouvelles fonctions excel !

CbernardT,
J'ai mis en PJ un fichier comprenant 3 dossiers.
Dossier 1er decembre : comprend le classeur A tel que je le reçois le 1er décembre et le classeur B préparé avec 5 dates d'avance
Dossier 2 decembre : classeur A du 2 decembre + classeur B
Dossier 3 decembre : classeur A du 3 decembre + classeur B.

Je vais tenter des experiences de macro dans mon coin pour voir si je trouve qqchose (je n'aime pas rester les bras croisés).
Si jamais t'as des pistes je suis preneur !

Merci beaucoup en tout cas !
Je ne sais pas si je deviendrai expert en VBA mais si je peux au moins atteindre un niveau qui me permette de me débrouiller un peu ça sera déjà énorme.

Cordialement,

Bob

 

[pierrejean]

Re : Liaison entre 2 classeurs excel

Re

Salut JC
Salut Bernard

Une proposition par fonction personnalisée a tester:

Function Valeur(classeur As String, feuille As String, cellule As String)
Application.Volatile
ab = False
On Error Resume Next
 f = Workbooks(classeur).Sheets(feuille).Name
 If Err.Number <> 0 Then ab = True
On Error GoTo 0
If ab Then
  Valeur = ""
Else
  Valeur = Workbooks(classeur).Sheets(feuille).Range(cellule)
End If
End Function

@ bobtoison

Utilisation de la fonction
dans le classeur B
Faire Alt F11 , eventuellement Insertion Module
Dans le module recopier la fonction
Puis en lieu et place de =[ClasseurA]Feuil2!B10 ecrire Valeur("ClasseurA.xls","Feuil2","B10")

Edit:

Voir test réalisé sur dossier 2 decembre Feuille 05-12-2009 du classeur B

 

[bobtoison]

Re : Liaison entre 2 classeurs excel

@ Pierre Jean,

J'ai téléchargé ton essai et ai tenté une application, ça marche impeccablement !
Merci infiniment !

Il ne me reste plus qu'à comprendre comment ça fonctionne.

Avant que tu ne mettes la piece jointe à disposition j'ai suivi tes recommandations à la lettre, je suis bien allé dans la section VBA, module 1 de mon classeur B, j'ai bien copier/coller la formule que tu m'as donné. J'ai ensuite femré la fenetre module 1 puis la fenetre VBA. J'ai ensuiten changé l'intitulé de la formule de la feuille 05-12-2009 par =Valeur("ClasseurA","05-12-2009","A3") dans le classeur B mais au moment d'appuyer sur Enter ça m'affichait un message d'erreur.

Alors que dans ton fichier tout marche comme sur des roulettes.

Est-ce que copier/coller la formule dans un module suffit à activer ma macro ?
Est-ce qu'on doit activer d'une maniere ou d'une autre le module créé ou s'active-t-il tout seul ?

En tout cas merci beaucoup à tous d'avoir consacré du temps à mon casse tête. Franchement, ça va m'être super utile ! (une fois que j'arriverai à l'appliquer par moi même ;-p )

Cordialement,

Bob

 

[JCGL]

Re : Liaison entre 2 classeurs excel

Bonjour à tous,
Salut PJ ,

Tu as mis :

=Valeur("ClasseurA","05-12-2009","A3")

Peux-tu essayer avec :

=Valeur("Classeur A.xls","05-12-2009","A3")

A+ à tous

 

[bobtoison]

Re : Liaison entre 2 classeurs excel

Salut JCGL,

Effectivement je devais ajouter ".xls" à Classeur A et mettre ";" au lieu de "," dans ma formule. Tout était dans le fichier de PierreJean en plus.

En essayant de généraliser ma formule sur mes "vrais" tableaux j'ai découvert un nouveau problème, la formule que je dois copier coller Valeur("ClasseurA.xls";"Feuil2";"A3") présente un inconvénient vu que j'ai de gros tableaux à traiter : je ne peux pas utiliser la poignée pour etirer ma formule pour tout le tableau, "A3" restera "A3".
Mais bon j'imagine que là, j'ai pas trop le choix...
Je vais essayer me débrouiller avec la macro de PJ.

Vous assurez !

Merci et à bientôt !

Bob

 

[JCGL]

Re : Liaison entre 2 classeurs excel

Bonjour à tous,

As-tu essayer en enlevant les ""

=Valeur("Classeur A.xls";"05-12-2009";A3)

Si PJ ne repasse pas avant, je regarderais

A+ à tous

 

[bobtoison]

Re : Liaison entre 2 classeurs excel

En enlevant les "" à "A3",
J'ai un message d'erreur "Excel ne parvient pas calculer car la formule car la formule fait référence à son propre résultat".
Je pense que "A3" fait référence à la valeur de A3 dans le classeur A tandis que A3 sans "" fait référence à la cellule A3 du classeur B.
Et globalement, changer la formule à chaque cellule de mon tableau est en fait trop titanesque... C'est ballot, alors que la macro de PierreJean a bien résolu le problème des onglets inexistants...

A +

Bob

 

[pierrejean]

Re : Liaison entre 2 classeurs excel

Re compte tenu du fait que l'adresse dans la feuille A est la même que dans la feuille B (et dans ce cas seulement)

Modification de la fonction et de son ecriture pour permettre la recopie incrementée : voir fichier