Formule un peu spéciale dans Excel

Signaler
Messages postés
5
Date d'inscription
jeudi 3 décembre 2009
Statut
Membre
Dernière intervention
5 décembre 2009
-
Messages postés
1566
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
5 avril 2013
-
Bonjour, je suis à la recherche d'une formule ou d'un macro me permettant de faire une liaison avec d'autres classeur excel en reprenant les valeurs des fichiers dans les cellules. je m'expliques :

si j'ai :
[*] Cellule A1 : 2009
[*] Cellule A2 : bhs-bsv
[*] Cellule A3 : 150

je voudrais que la cellule A4 soit egale à la formule suivante :
[*]='O:\Permis\2009\[BHS-BSV-150.xls]BHS-PT-001'!$B$5

en vous remerciant par avance
Bonne journée
@+ JA

11 réponses

Messages postés
345
Date d'inscription
mardi 1 juin 2004
Statut
Membre
Dernière intervention
17 octobre 2014
3
bonjour,
ta formule me parait bien, où est ton problème ?
Il ne reste qu'à la taper dans la zone prévue à cet effet.

thip
Messages postés
5
Date d'inscription
jeudi 3 décembre 2009
Statut
Membre
Dernière intervention
5 décembre 2009

Je cherche en fait que la formule se fasse en fonction des valeurs des cellules

c'est à dire :
# ='O:\Permis\Valeur Cellule A1\
[Valeur Cellule A2+ Valeur Cellule A3.xls]BHS-PT-001'!$B$5

j'espère que je me suis bien exprimé

merci d'avance
Messages postés
345
Date d'inscription
mardi 1 juin 2004
Statut
Membre
Dernière intervention
17 octobre 2014
3
Pas simple, de modifier la formule à la volée, au moment d'un calcul. Est-ce possible?
Je propose un bouton, dont le code modifierait la formule de ta cellule A4 en fonction des autres. Là c'est simple,

s1 = Range("A1").Value
s2 = Range("A2").Value
s3 = Range("A3").Value

Range("A4").Formula = "='O:\Permis" & s1 & "" & s2 & s3 & ".xls]BHS-PT-001'!$B$5"


thip
Messages postés
797
Date d'inscription
mardi 7 juin 2005
Statut
Membre
Dernière intervention
23 février 2011
7
C'est possible rien qu'avec des formules EXCEL, mais ça ne va pas t'aider. La contrainte fait que ça ne sera probablement pas intéressant pour toi.

Fichier Excel : Je mets tout ce dont j'ai besoin pour créer la liaison en paramètres.
D6 : Chemin du fichier (ex. C:\Test\ )
D7 : Nom du fichier (ex. Toto.xls )
D8 : Nom de la feuille liée (ex. Feuil1 )
D9 : Adresse de la celulle (ex. A1)

La formule à utiliser est la formule INDIRECT. Ce qui te donne :

=INDIRECT("'" & D6 & "[" & D7 & "]" & D8 & "'!" & D9)
ce qui reconstitue le chemin de ta liaison.

Mais arrive le pb. La formule doit te marquer #REF, ce qui est normal comme l'indique l'aide : il faut que le fichier lié soit ouvert pour que la valeur soit remontée !
Mais tu peux régler à la rigueur ce pb avec une macro qui ouvre automatiquement le fichier paramétré et le referme aussitôt pour mettre à jour. Et lancer cette macro à la demande ou automatiquement (en surveillant le Worksheet_change )

Molenn
Messages postés
345
Date d'inscription
mardi 1 juin 2004
Statut
Membre
Dernière intervention
17 octobre 2014
3
Salut,
En voici une qui foncitonne bien :

Public Function indirect_f(sFilePath As String, shName As String)
    Dim wb As Workbook
    Dim xl As New Excel.Application
    
    Set wb = xl.Workbooks.Open(sFilePath)
    indirect_f = wb.Worksheets(shName).Range("$B$5").Value
    wb.Close
    xl.Quit
End Function


et dans A4 tu mets

=indirect_f( CONCATENER("O:\Permis";A1;"";A2;A3;".xls") ; "BHS-PT-001")

J'avais proposé le bouton car je ne pouvais pas ouvrir un workbook en cours de calcul. Il suffisait donc de l'ouvrir à partir d'une autre instance.

Il reste à généraliser car mon code force B5 et ne gère pas les erreurs.

thip
Messages postés
5
Date d'inscription
jeudi 3 décembre 2009
Statut
Membre
Dernière intervention
5 décembre 2009

Salut,
Tout d'abord merci,
je viens d'essayer mais cela ne marche par cela me met dans la cellule "#NOM?"

je débute dans le visual basic, (pour info j'utilise excel 2007)
j'ai ouvert une nouvelle feuille dans excel et j'ai mis dans le visual basic,
le code suivant :
Public Function indirect_f(sFilePath As String, shName As String)
    Dim wb As Workbook
    Dim xl As New Excel.Application
    
    Set wb = xl.Workbooks.Open(sFilePath)
    indirect_f = wb.Worksheets(shName).Range("$B$5").Value
    wb.Close
    xl.Quit
End Function


j'ai mis dans les cellules :
[*] A1 = 2009
[*] A2 = BHS-BSV-
[*] A3= 150
[*] A4 = =indirect_f( CONCATENER("O:\Permis";A1;"";A2;A3;".xls"); "BHS-PT-001")

Quand je fait "Afficher les étapes du calcul", et que je fait "Évaluer", à la place de "indirect-f", il met "#nom?"

en te remerciant par avance
JA
Messages postés
345
Date d'inscription
mardi 1 juin 2004
Statut
Membre
Dernière intervention
17 octobre 2014
3
Je viens de l'essayer sous Excel2007 et ça fonctionne correctement à conditions d'incorporer la fonction dans un module.
Si tu n'en n'as pas, fais Insertion + Module

thip
Messages postés
5
Date d'inscription
jeudi 3 décembre 2009
Statut
Membre
Dernière intervention
5 décembre 2009

Ah oui
cela fonctionne quand on ne fait pas correctement

juste une autre petite question, est-il possible de ne pas avoir le message "Voulez-vous enregistrer les modifications apportées à .....xls"
vu que je vais juste récupérer des valeurs et pas écrire dans l'autre fichier

en te remerciant par avance

bonne soirée
@+
JA
Messages postés
345
Date d'inscription
mardi 1 juin 2004
Statut
Membre
Dernière intervention
17 octobre 2014
3
si excel te pose la question, c'est que le fichier a été modifié en cours de route. Ferme le fichier et essaie encore.

Sinon, quand on ferme un workbook, on ajoute un parametre :

wb.Close SaveChanges:=False

ou True suivant ce qu'on veut.

thip
Messages postés
5
Date d'inscription
jeudi 3 décembre 2009
Statut
Membre
Dernière intervention
5 décembre 2009

Merci beaucoup pour l'aide
cela marche très bien

Pour Finir, je l'espère,
je voudrai savoir s'il est possible que le calcul se lance uniquement au changement de valeur de la cellule par exemple D2

est-il possible qu'excel récupère une seule fois les valeurs et qu'il les conservent, pour éviter qu'il y aille à chaque ouverture du fichier chercher les valeurs

merci
@+
JA
Messages postés
1566
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
5 avril 2013
6
Bonjour,

je n'ose ps te parler de l'utilisation de Application.Volatile, puisque ta question est posée dans le thème VB.Net et que ce dont j'allais te parler est relatif à VBA/Excel ...
Ai-je tort ? (je ne le crois pas).