XL 2016 Liaisons classeurs fermés [RÉSOLU]

[Brice G]

Bonjour,
j'ai un problème que je ne parviens pas à régler, je m'explique.
J'ai un classeur de destination qui fait appel (à partir de formules RECHERCHEV et de listes déroulantes (contrôle ActiveX)) à un fichier source, ma base de données, qui est un autre classeur excel.

Lorsque j'ouvre ma source PUIS mon classeur destination, aucun problème tout fonctionne ; en revanche, si je n'ouvre pas la source, les menus déroulants sont "vides". Et ce, même si j'ouvre la source APRES le classeur de destination.
A l'ouverture de mon classeur de destination, j'ai le message d'invite qui me demande si je souhaite mettre à jour les liaisons, mais me signifie que ma source (qui est dans le même dossier) est introuvable. Même en "modifiant la source" à partir de ce menu (cf image ci-dessous), Excel trouve la "référence invalide".



J'ai cherché à plusieurs endroits, essayés plusieurs manipulations pour comprendre d'où venait le problème, mais je ne trouve pas, dsl.

PS : Ma seule solution actuellement serait de créer un environnement pour ouvrir toutes mes sources (j'en ai 2) et ma destination, mais cela me semble lourd.

 

[Brice G]

Pour info, je ne parviens même pas à créer un environnement, enfin un espace de travail, cela n'est plus possible depuis la version 2013 d'après ce que j'ai trouvé.
Du coup, je n'ai aucune autre solution, j'ai changé tous les noms de dossier menant aux fichiers en question en enlevant tous les espaces, je ne trouve pas. Je ne peux même pas rompre la liaison par exemple, j'obtiens le même résultat, même message d'erreur.
J'ai essayé de créer une macro qui ouvrait la source (ça ça fonctionne), puis qui changerait la propriété d'une de mes combobox : listfillRange (simplement réécrire cette propriété permet de retrouver la liste dans les combobox...), mais apparemment en enregistrant une macro je ne peux changer les propriétés de mes combobox.

Je vous mets d'ici quelques minutes les deux fichiers en question simplifiés.

 

[Spinzi]

Bonjour,

une solution est d'utiliser le complément gratuit d'Excel "Power Query".
Cela vous permettra de ramener directement des informations depuis une autre base.

Exemple :
_1 fichier base
_1 fichier de synthèse qui va piocher dans le fichier base

Sans power Query, il faut ouvrir les 2 sources (a quelques exceptions près) pour mettre à jour.

Avec Power Query, vous importez le fichier base directement dans votre fichier de synthèse. Il se met à jour automatiquement en fonction des dernières modifications effectuées sur le fichier base.
Et comme le fichier base remonte dans le fichier synthèse, aucun probleme pour les formules.

Spinzi

 

[Brice G]

Bonjour Spinzi, merci pour votre temps, vu celui que je passe sur ce fichier.....

Donc si je comprends, ma solution se trouverait sans doute dans PowerQuery, je le télécharge, essaie de faire ce que vous me préconisez, et j'espère, revient simplement pour vous remercier avec de l'émotion dans la voix.


Edit : Au passage, pour info :

 

[Brice G]

Après essai et avoir cherché quelque peu, je me demande ce que vous me proposiez de faire.
Car j'ai l'impression que cela revient "simplement" à insérer dans mon classeur de destination, une autre feuille où se trouverait ma base de données.
Je me trompe sans doute, car je ne vois pas l'utilité de power query alors, mais ce ne serait pas une solution possible.

 

[Spinzi]

Cela permet d'avoir directement dans votre fichier la base source, à jour.
Donc plus de problème d'indexation de source :
_aucun fichier à ouvrir
_mise à jour automatique en fonction du dernier enregistrement de la base source
_possibilité de créer des modèles de données (requêtes) pour spécifier, filtrer et plein d'autres actions directement sur la synthèse du fichier source.

Spinzi

 

[Brice G]

Merci pour les explications complémentaires, donc cela est bien plus qu'une simple copie d'une feuille sur mon classeur "synthèse" comme vous l'appelez, c'est une copie qui se met à jour automatiquement.

Bien, je vais essayer de mettre cela comme je le souhaite, mais avec 7.000 références dans la base de données, je crains d'avoir un fichier un peu lourd...nous verrons.
Encore merci.

 

[Spinzi]

Pour info, j'utilise cette technique avec bien plus de 7000 lignes et pas mal de colonnes.
Cela demande un temps de chargement inférieur à 5s.

Si jamais n'hésitez pas, mais le concept reste simple.

Spinzi

 

[Brice G]

Bien, alors bonne nouvelle, je vais essayer de charger seulement les colonnes qui me sont utiles de la base de données.


Edit : Après quelques passages sur l'aide en ligne de Microsoft, je crois que cela va beaucoup m'apporter et régler pas mal de problèmes ! J'ai pour le moment essayé avec une base de donnée 100 fois moins grande, mais sur le principe, ça a l'air super, merci beeaaauucccouup ! Je reviens vers vous pour les questions que, je n'en doute pas, je vais bientôt me poser.

 

[Brice G]

Avec Power Query, vous importez le fichier base directement dans votre fichier de synthèse. Il se met à jour automatiquement en fonction des dernières modifications effectuées sur le fichier base.

Oui j'ai vérifié cela c'est super tout s'affiche bien pour moi.

Et comme le fichier base remonte dans le fichier synthèse, aucun probleme pour les formules.

Là par contre, j'ai du mal à suivre, je dois réécrire mes formules (j'en ai un paquet...mais un rechercher-remplacer me sera utile ) pour qu'ils ne fassent plus appel à la base directement, mais à la requête créée dans mon fichier de synthèse ?
Je pense que oui, cela réglerait en effet pas mal de soucis, le hic pour mes menus déroulants, dont je devrais changer un à un (j'ai rien trouvé de mieux pour changer tout d'un coup) les propriétés correspondantes (dans les combobox).

 

[Brice G]

J'ai maintenant je crois compris le principe, et même réussi à régler le problème des menus déroulants.

Néanmoins, ma base, elle, fait appel à (oui encore) un autre fichier excel, pour remplir une ou deux de ses colonnes. Pour cette "seconde base", ou "sous base" qui pourra être modifiée elle-aussi indépendamment, je fais une seconde requête dans mon classeur de synthèse ou une requête dans la base ? Cette dernière solution ne me permet pas de tout actualiser simplement avec "actualiser" dans données.

Donc je vais faire les deux requêtes dans le même, ainsi, j'aurais deux requêtes différentes (deux feuilles de plus) dans mon fichier principal : le fichier de synthèse ?
Est-ce que j'ai compris ?

 

[Spinzi]

Bonjour,

c'est flou sans exemple mais le but est d'avoir un fichier de synthèse comprenant le requete Power Query de sorte que tout se trouve sur le même fichier.

 

[Brice G]

Merci pour votre temps, je vous mets un fichier demain.

 

[Brice G]

Voici deux fichiers qui permettent de mieux comprendre, j'ai épuré au maximum.

 

[Brice G]

Je pense avoir résolu tous mes problèmes :
- je fais deux requêtes dans mon fichier de synthèse (= de destination, le FICHE_DEVIS_test), une pour base_fournitures_test et une autre pour sortie_stock (une autre base de données que je ne vous ai pas mise ici) ;
- je change mes formules RECHERCHEV dans FICHE_DEVIS_test en conséquence en faisant appel aux deux nouvelles feuilles de requête dans FICHE_DEVIS_test et non plus directement aux bases de données.

Il n'y a plus qu'à actualiser quand on le souhaite, et tout fonctionnera à merveille (espérons-le ) ; un très grand MERCI à toi Spinzi pour cette façon de faire, j'en voit le bout, enfin, super.