VBA ne pas mentionner le nom d'un fichier

[BastienN93]

Bonjour à tous,

J'ai réussi à rédiger mon code pour effectuer un matching selon plusieurs critères, et jusqu'ici tout va bien.
Je souhaite à présent déverser ces résultats dans un fichier qui sera utilisé par tous, tout en conservant l'indépendance de mon fichier de matching (qui contient la macro) de sorte à ce que le fichier final ne contienne pas de code mais des données uniquement.
J'ai donc réussi à rédiger un bout de code qui fait une recherche V et replace les infos au bon endroit.

Le truc important c'est que le code ne doit pas mentionner le chemin d'aucun des deux fichiers, ni leur noms (.xls et .xlsm)
Voici le bout de code qui me pose un soucis: la recherche V fonctionne, sauf que j'obtiens une erreur #NOM? dans toutes les cases du fichier final. En revanche, si j'actualise la formule, une fenêtre me demande de mettre à jour les liens, et de lui montrer à quel fichier je fais référence (mon fichier macro donc) et là les données d'actualisent.
J'aimerai donc éviter ce message d'erreur et qu'Excel comprenne directement qu'il s'agit du fichier macro.

Dim MonFichier
Dim resultats_matching

resultats_matching = ThisWorkbook.Name        'comprendre le fichier qui détient tout mon code et les résultats du premier matching qui a fonctionné parfaitement

MonFichier = Application.GetOpenFilename("Excel Workbooks,*.xls;*xlsx")
    Workbooks.OpenText Filename:=MonFichier      'j'ouvre le fichier final qui doit être rendu
        Sheets("Clients list").Select

Application.DisplayAlerts = False

Range("AG3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-25],'[resultats_matching]Revenus'!SYNTHESEREVENUS,2,FALSE)),"""",VLOOKUP(RC[-25],'[resultats_matching]Revenus'!SYNTHESEREVENUS,2,FALSE))"
    LastRow = Range("AF" & Rows.Count).End(xlUp).Row
                Range("AG3").Copy Range("AG3:AG" & LastRow)

Il y a peut être des étapes inutiles, je suis novice, mais tout fonctionne, sauf cette dernière partie de mise à jour des liens. Si j'indique à Excel où chercher la data, le matching est effectué parfaitement.
J'ai donc essayé d'appeler mon fichier macro "résultats_matching" pour éviter de faire référence au nom du fichier mais cela marche à moitié puisque j'ai une erreur..

*SYNTHESEREVENUS fait référence à la plage de données dans laquelle Excel doit aller chercher.

Merci beaucoup d'avance, j'espère avoir été le plus exhaustif possible.

 

[vgendron]

Re : VBA ne pas mentionner le nom d'un fichier

Hello

la formule est une chaine de caractère
Resultatmatching est une variable qui contient une chaine de caractères
ce que tu souhaites, c'est insérer le contenu de la variable dans la chaine complète

essaie ceci

"=IF(ISNA(VLOOKUP(RC[-25],'[" &resultats_matching &"]Revenus'!SYNTHESEREVENUS,2,FALSE)),"""",VLOOKUP(RC[-25],'[" &resultats_matching &"]Revenus'!SYNTHESEREVENUS,2,FALSE))"

avec un exemple tout simple

pour écrire "TrucMuche" dans la cellule A1
soit directement
[A1]="TrucMuche"

ou avec une variable qui contient "muche"

variableX="Muche"

[A1]="Truc" & variableX

 

[BastienN93]

Re : VBA ne pas mentionner le nom d'un fichier

Salut, merci de ta réponse.
En fait j'ai peut etre faux en utilisant la variable resultat_matching, je pensais pouvoir dénommer un fichier par resultat_matching, de telle sorte à ce que, lorsque la formule va chercher une donnée particulière, il ne figure pas dans la formule:

=RECHERCHEV(A1;'[Fichier_matching.xlsm]Revenus'!$B$5:$H$255;2;FAUX)

(la plage $B$5:$H$255 est renommée par SYNTHESEREVENUS dans Excel mais c'est pas le problème)
mais plutôt :

=RECHERCHEV(A1;'[resultat_matching]Revenus'!$B$5:$H$255;2;FAUX)

Avec resultat_matching qui serait mon fichier où Excel doit chercher les données, de telle sorte à ce que le nom du fichier ne figure pas dans le code et que je n'ai pas de soucis si les fichiers bougent où changent de nom. Je précise également qu'au moment où la macro tourne, le dit fichier "Fichier_matching.xlsm" est ouvert car celui ci même qui contient la macro

Je ne sais pas si c'est possible à réaliser !

Merci beaucoup

 

[vgendron]

Re : VBA ne pas mentionner le nom d'un fichier

Bon la. je crois qu'il va falloir passer par la case "poster un fichier exemple"

je ne comprend pas ce que tu veux (ou ne veux pas) en fait.

Dans le Code VBA.. l'utilisation de la variable te permet de ne pas avoir à taper le nom du fichier excel..
Variable=activeworkbook.name

ensuite. le nom contenu dans cette variable est utilisé pour construire la formule.
mais au final. dans la formule . sur la feuille excel, le nom du fichier apparait forcément..
???!!???

 

[BastienN93]

Re : VBA ne pas mentionner le nom d'un fichier

Désolé pour la confusion, je sais que c'est difficile de comprendre sans contexte...
Malheureusement je ne peux pas donner de fichier, les données sont confidentielles.

Pour répondre à ta question, en effet, ma variable me permet de ne pas mentionner le nom du fichier dans mon code, et cela fonctionne. Et sur la feuille Excel, le nom du fichier ne figure pas, je ne vois que le nom de la variable:

=SI(ESTNA(RECHERCHEV(H3;[resultats_matching]Revenus!SYNTHESEREVENUS;2;FAUX));"";RECHERCHEV(H3;[resultats_matching]Revenus!SYNTHESEREVENUS;2;FAUX))

Comme tu le vois, la formule mentionne bien la variable et non le nom du fichier ce qui est parfait. Mon soucis c'est qu'à l'exécution de la macro, j'obtiens le résultat #NOM?, car d'après ce que je comprends, Excel n'arrive pas à comprendre où piocher les données (dans le fichier matching qui contient la macro).

En revanche à la fin de l'exécution, si je retourne dans la cellule en question et que j'appuie sur ENTREE, j'ai une fenetre qui s'ouvre en me demandant de mettre à jour les liaisons. Si je lui indique mon fichier matching qui contient la macro, alors tout se remet en place comme il faut.

Ce que j'aimerai, c'est éviter d'avoir cette étape #NOM? où je dois ensuite re-valider la formule pour qu'il m'ouvre une fenetre et me demande le fichier.

Dans l'idéal, j'aimerai qu'il comprenne directement où récupérer les données.
Si ce n'est pas possible, le simple fait qu'il ouvre lui même la fenêtre me demandant de lui indiquer où prendre les données (à la manière d'un MsgBox) me convient également

J'espère que je ne t'ai pas embrouillé, j'ai peut etre mal formulé ma demande à la base !

Merci

 

[Dranreb]

Re : VBA ne pas mentionner le nom d'un fichier

Bonjour
En tout cas votre resultats_matching est un nom de variable, pas un bout de constante texte.
Alors si c'est sa valeur que vous voulez reproduire dans le texte, ce n'est certainement pas
comme ça : "… …H3;[resultats_matching]Revenus!… …" mais en la concaténant parmi les parties constantes,
comme ça : "… …H3;[" & resultats_matching & "]Revenus!… …"

Parcez que là ce que vous avez fait c'est un peu comme si on vous avait demandé : "Dites moi ce que voulez" et que vous auriez répondu en disant bien, comme on vous l'avait demandé: "ce que vous voulez".

 

[Dranreb]

Re : VBA ne pas mentionner le nom d'un fichier

Maintenant, si c'est vraiment dans la formule finale que vous ne voulez pas voir apparaître le nom du classeur de macros, rien ne vous interdirait à l'ouverture du classeur sans macro, d'y créer un nom SynthèseRevenus qui se référerait à une plage du classeur de macros…
Mais je ne vois pas trop ce que ça apporterait. Ça ne changerait rien au fait que c'est ce classeur de macros là et pas un autre, qui resterait noté dans un nom du classeur au lieu d'être directement visible dans les formules.

 

[BastienN93]

Re : VBA ne pas mentionner le nom d'un fichier

Bonjour

Merci Dranreb pour votre réponse, je comprends maintenant que ce que j'ai essayé de faire ne sert strictement à rien et ne veut rien dire. Je laisse donc de coté cette mauvaise idée. Cependant, je suis toujours à la recherche d'une alternative car comme vous le soulevez, créer un nom se référant à la plage en question n'apporte rien.

Puisque je repars de zéro, ma question serait:

Comment puis-je, avec un code VBA ou pas, ne pas faire figurer le nom d'un classeur dans une formule?
A savoir ne pas voir ceci dans ma formule, avec un exemple simple: imaginons que je doive récupérer la valeur d'une somme de cellules contenues dans un classeur différent du classeur actif, mais tout de même ouvert (MonFichier.xlsm serait donc ouvert).

=SOMME('[MonFichier.xlsm]Revenus'!$C$5:$C$7)

Mais par quelque chose qui pointerait vers MonFichier.xlsm sans mentionner son nom.
J'ai essayé en donnant un nom à ma plage mais en vain car si je suis dans un autre classeur, la plage n'est pas reconnue, même si les deux classeurs sont ouverts...

Peut être que cela ne passe pas par une formule mais par du code pur et dur?
Je n'en ai aucune idée personnellement, je n'ai pas trouvé de réponses ailleurs.

En tout cas, merci beaucoup pour le temps que vous m'accordez!

 

[vgendron]

Re : VBA ne pas mentionner le nom d'un fichier

Re

Pour qu'excel sache OU chercher les valeurs, il faut lui dire le nom du fichier d'une manière ou d'une autre..
maintenant. je crois comprendre que ce fichier change de temps en temps et que tu n'as pas envie de te taper la modif de toutes les formules qui contiennent ce nom..
une piste serait de passer par la fonction Indirect
le nom du fichier (qui doit de toute facon apparaitre quelque part) est mis dans une cellule A1 par exemple
et dans tes formules, tu utilises Indirect() - Pour la syntaxe exacte: cf aide excel

 

[Dranreb]

Re : VBA ne pas mentionner le nom d'un fichier

Bonjour.
BastienN93, il n'y aurait aucun intérêt de ne mentionner dans la formule ni le nom du classeur, ni celui de la feuille, ni l'adresse de la plage. Mais ce code, à tester, repris du premier message, mettrait tout ça sans que rien n'en soit mentionné dans le code, juste en utilisant le nom SYNTHESEREVENUS du classeur de macros. Ce dernier doit être le classeur actif à ce moment là.

Dim MonFichier, ExprVLOOKUP As String
ExprVLOOKUP = "VLOOKUP(RC[-25]," & [SYNTHESEREVENUS].Address( _
   True, True, xlR1C1, True) & ",2,FALSE)"
MonFichier = Application.GetOpenFilename("Excel Workbooks,*.xls;*.xlsx")
If VarType(MonFichier) <> vbString Then Exit Sub
Workbooks.Open Filename:=MonFichier  'j'ouvre le fichier final qui doit être rendu
With ActiveWorkbook.Sheets("Clients list")
   .[AG3].Resize(.Cells(.Rows.Count, "AF").End(xlUp).Row - 2).FormulaR1C1 _
      = "=IF(ISNA(" & ExprVLOOKUP & "),""""," & ExprVLOOKUP & ")"
   End With

Je vous déconseille de passer par un nom vers une référence externe, même correctement créé. Ça ne ferait qu'empirer les choses si vous renommiez ou déplaciez le classeur de macros.

 

[Dranreb]

Re : VBA ne pas mentionner le nom d'un fichier

Bonjour
Si vous tenez quand même à remplacer dans la formule l'adresse de référence de "SYNTHESEREVENUS" du classeur de macros par un nom "SynthRevenus" du classeur cible, vous pouvez créer ce nom au moyen de cette instruction :

ActiveWorkbook.Names.Add "SynthRevenus", "=" & ThisWorkbook. _
   Names("SYNTHESEREVENUS").RefersToRange.Address(External:=True)

Mais il faut me dire ce que vous espérez en tirer comme avantage, car c'est probablement illusoire.

Vous pouvez juste encore faire l'économie de l'adresse de la plage en mettant le nom d'origine à la place, comme ça :

Const NomSrc = "SYNTHESEREVENUS"
Dim S() As String
S = Split(ThisWorkbook.Names(NomSrc).RefersToRange.Address(External:=True), "!")
S(1) = NomSrc
ActiveWorkbook.Names.Add "SynthRevenus", "=" & Join(S, "!")

Mais le nom du classeur source (puis son chemin, une fois qu'il sera fermé) restera quand même toujours encore figé quelque part à l'arrivée, quoi que vous fassiez.