Formule un peu spéciale dans Excel

Question

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

tpoinsot · Answer

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

JAG130677 · Answer

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

tpoinsot · Answer

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

Molenn · Answer

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

tpoinsot · Answer

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

JAG130677 · Answer

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

tpoinsot · Answer

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

JAG130677 · Answer

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

tpoinsot · Answer

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

JAG130677 · Answer

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

jmf0 · Answer

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).

Formule un peu spéciale dans Excel

11 réponses

Votre réponse

Discussions similaires