Accéder au contenu principal

Créer des références externes dynamiques en Excel (1)

Dans des formules Excel, on a parfois besoin de se référer a une plage de cellules se trouvant dans un autre document (classeur/workbook).
Exemple: on a un document "portefeuille.xls" qui se réfere au document "tauxChange.xls".
Si le chemin d'acces au document externe change, la référence ne fonctionnera plus.
Pour éviter ce probleme, une solution serait de pouvoir utiliser une référence "relative", qui permettrait, par exemple, de pointer vers le classeur "tauxChange.xls" se trouvant dans le meme dossier que le classeur "portefeuille.xls". Ceci permettrait d'utilliser la paire de fichiers n'importe où, pourvu qu'ils soient tous deux dans le meme dossier.
Mais comment faire ?
  1. Recupérer le chemin du document en cours en C5:
    =CELL("filename") permet de retrouver le chemin complet du document ou il est utilisé, et me retournera:
    H:\test excel\[portefeuille.xls]Actifs
    "Actifs" étant le nom de la feuille ou est utilisée la fonction.
  2. A partir de la,
    =LEFT(C5,FIND("[",C5)-1) me donnera en C6:
    H:\test excel\
    soit le dossier du document en cours.
  3. Il suffit ensuite de construire un formule en C7, sous forme de chaine de caracteres:
    ="'"&C6&"[tauxChange.xls]Feuille1'$A$2"
    Ne pas omettre d'encadrer tout ce qui précede $A$2 avec des apostrophes ' '.
    Le = est suivi de guillemet, apostrophe, guillemet.
  4. Exécuter cette formule en C8 en utilisant la fonction INDIRECT:
    =INDIRECT(C7)
Et hop, on récuperera a chaque fois le cellule A2 du document tauxChange.xls qui se trouve dans le MEME dossier que le fichier appelant.
Si on veut utiliser une fonction VBA pour se faciliter la vie, une solution est proposée dans la deuxième partie de ce post.
Libellés :

Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

ROW_NUMBER OVER PARTITION en Access

Ceux qui ont l'habitude de travailler avec une "grosse" base données comme SQL Server / Oracle / PostGreSQL, sont parfois frustrés face à certaines lacunes du SQL d'Access.   Prenons par exemple: ROW_NUMBER() OVER PARTITION, dont l'absence rend certaines requêtes très compliquées.   J'ai donc écrit une petite fonction VBA qui peut être appelée depuis un query Access et qui simulera assez bien ce ROW_NUMBER() OVER PARTITION.   Notez que ceci ne fonctionnera pas correctement dans une vue ou un formulaire interactif. Par contre comme source d'un rapport ou d'un export Excel, c'est impeccable.   En pratique il est préférable d'initialiser la fonction avec une chaîne de caractères "improbable" avant de lancer le rapport, comme indiqué dans le code.
Enregistrer un commentaire
Lire la suite

Calculate MAX of a list in VBA

Strangely such a function is not available in VBA. Here is one that works with any number of strings or numbers. Function Largest(ParamArray a() As Variant) As Variant 'returns the largest element of list 'List is supposed to be consistent: all nummbers, dates, strings. 'Nulls are allowed, but not in first position. Eventually provide a first low dummy value 'e.g:   largest(2,6,-9,7,3)         -> 7 '       largest("d", "z", "c", "x") -> "z" 'by Patrick Honorez --- www.idevlop.com     Dim result As Variant     Dim i As Integer          result = a(LBound(a))          For i = LBound(a) + 1 To UBound(a)         If result < Nz(a(i), result) Then             result = a(i)         End If     Next i     Largest = result End Function
Enregistrer un commentaire
Lire la suite