RechercheH autrer fichier VBA

Bonjour,

Je bloque sur une fonction en VBA, je souhaiterais utiliser HLookup pour m'afficher une valeur d'un autre fichier Excel.
La synthaxe fonctionne bien lorsque je l'inscrit dans une cellule mais je n'arrive pas à la faire fonctionner en VBA.

je souhaiterais récupérer une valeur d'un tableau test.xlsx en fonction de l'entête dans un fichier vierge avec un bouton en cellule "V5"

Lorsque j'utilise la formule suivante : =RECHERCHEH("B161";'[test.xlsx]spec colo'!$B$1:$BM$13;2;FAUX)

cela fonctionne, la valeur -2 s'affiche dans mon fichier nouveau.xlsm en cellule V5

J'aimerais faire la même chose en VBA, j'ai donc essayé les choses suivantes :

Dim wb, rech

wb = Workbooks("C:\test\test.xlsx").Worksheets(1).Range("B1:D6")
rech = Application.HLookup("B161", wb, 2, False)

ActiveSheet.range("V5") = rech

Cela ne fonctionne pas, je pense qu'il y a un problème de syntaxe dans ma variable wb, j'ai essayé pleins de choses mais je ne trouve pas.

Bonjour RazoG

Pour moi c'est normal, vous exécutez une fonction alors que le classeur n'est pas ouvert (je pense)

Si voulez avoir le résultat, il faut alors inscrire la formule et coller la valeur du résultat

  With ActiveSheet.Range("V5")
    .FormulaLocal = "=RECHERCHEH(""B161"";'[test.xlsx]spec colo'!$B$1:$BM$13;2;FAUX)"
    .Value = .Value
  End With

Ou ouvrir le classeur

A+

Bonjour BrunoM45,

Effectivement je pensais pouvoir le faire sans ouvrir mon fichier Excel.
J'ai testé pour chaque lignes que je souhaitais récupérer et ça fonctionne, merci beaucoup.

Par contre si je peux abuser pour comprendre, j'ai essayé de le faire fonctionner d'une manière différente, ça a marché mais ça ne fonctionne plus de la manière suivante :

J'ai ouvert une nouvelle instance définie dans une variable comme ceci :

Dim WbBase ' Fichier utilisé 
Dim ShBase ' Feuille utilisé
Dim CelluleReference ' Range ou s’exécute ma rechercheH
Dim CheminComplet ' Chemin du fichier pour la rechercheH
Dim rechDL15min, rechDL15max ' Les 2 valeurs à récupérer lors de ma rechercheH
Dim xlApp As New Excel.Application   ' ouverture d'une instance 
Dim xlBook As New Excel.Workbook   ' définition du fichier dans l'instance
Dim xlSheet As New Excel.Worksheet   ' définition de la feuille

CheminComplet = "C:\test\values.xlsx"    ' Chemin du fichier pour la rechercheH

Set xlBook = xlApp.Workbooks.Open(Filename:=CheminComplet)
Set xlSheet = xlBook.Sheets(1)
  With xlSheet
        Set CelluleReference = .Range("B1:D15")
  End With

 Set WbBase = Workbooks("C:\TEST\monfichier.xlsm") 'Fichier ou je récupère les valeurs de ma rechercheH
 Set ShBase = WbBase.ActiveSheet

 rechDL15min = Application.HLookup("B161", CelluleReference, 2, False)
 rechDL15max = Application.HLookup("B161", CelluleReference, 3, False)

  ShBase.Range("W9") = rechDL15min
  ShBase.Range("W10") = rechDL15max

Mon instance Excel est bien ouverte car si j'essaye d'ouvrir le fichier values.xlsx, il m'indique que mon utilisateur à déjà ouvert le fichier. mais il ne récupère aucune valeur.

Mon code est il juste ? y a t'il une protection à enlever sur Excel pour que le VBA puisse s'exécuter ?

C'est juste une interrogation car votre solution est plus "propre" et surtout plus rapide à exécuter.

Merci beaucoup

Bonjour Razog,

Je n'avais pas eu le temps de me pencher sur le sujet plus que ça, désolé

Mais je viens de m'apercevoir qu'avec le fichier non ouvert, la formule fonctionne,
sauf qu'il faut bien rentrer le chemin d'accès complet

=RECHERCHEH("B161";'C:\Temp\Test\[Test.xlsx]Feuil1'!$A$1:$D$6;2;FAUX)

Que vous pouvez variabiliser

Pour ce qui est de votre code, il faut le faire ainsi

Sub Test()
  Dim WbBase As Workbook ' Fichier utilisé
  Dim ShBase As Worksheet ' Feuille utilisé
  Dim CelluleReference As Range ' Range ou s’exécute ma rechercheH
  Dim CheminComplet As String ' Chemin du fichier pour la rechercheH
  Dim rechDL15min, rechDL15max ' Les 2 valeurs à récupérer lors de ma rechercheH
  Dim xlApp As New Excel.Application   ' ouverture d'une instance
  Dim xlBook As New Excel.Workbook   ' définition du fichier dans l'instance
  Dim xlSheet As New Excel.Worksheet   ' définition de la feuille

  CheminComplet = "C:\Temp\test\values.xlsx"    ' Chemin du fichier pour la rechercheH

  Set xlBook = xlApp.Workbooks.Open(Filename:=CheminComplet)
  Set xlSheet = xlBook.Sheets(1)
  Set CelluleReference = xlSheet.Range("B1:D15")

  Set WbBase = ThisWorkbook  'Fichier ou je récupère les valeurs de ma rechercheH
  Set ShBase = WbBase.ActiveSheet

  rechDL15min = Application.HLookup("B161", CelluleReference, 2, False)
  rechDL15max = Application.HLookup("B161", CelluleReference, 3, False)

  ShBase.Range("W9") = rechDL15min
  ShBase.Range("W10") = rechDL15max
End Sub

A+

Eh bien merci Bruno !
Effectivement, je n'avais pas précisé que j'avais essayé votre formule avec le fichier fermé et ça fonctionne impeccable.

Merci d'avoir pris le temps de corriger mon code, j'aurais du déclarer proprement mes variables ! j'ai fais le test et tout fonctionne mais je trouve votre méthode plus propre et surtout moins gourmande dans ma situation.

Merci beaucoup