[Résolu][Calc] CallFunction RECHERCHEV avec autre classeur

[Asghaard]

Bonjour à tous,

Aparté : "C'est mon premier message sur ce forum.
Je n'ai jamais osé posté de message jusqu'à aujourd'hui de peur de me faire réprimander sur le respect des très nombreuses règles à avoir en tête.
J'espère ne pas les enfraindre.
Je débute dans les macros sous LibreOffice.
Auparavant je réalisait des macros plutôt complexes sous VBA Excel mais, mon entreprise à décider de migrer vers LibreOffice me laissant démuni !"

J'utilise souvent la formule RECHERCHEV dans mes classeurs. cela permet à partir d'un n° d'identifiant de retrouvé le nom d'un équipement qui se trouve dans un classeur de référence.
Je souhaite réaliser une fonction qui me retournerait directement le même résultat et qui serait plus simple à utiliser.

Exemple simple :
exemple de la formule dans une cellule :

Code : Tout sélectionner

=RECHERCHEV(A1;'file:///CHEMIN_DOC_REFERENCE/NOM_FICHIER_REFERENCE.ods'#$NOM_FEUILLE.A$1:B$1000;2;0)

je souhaite réaliser une fonction qui s'écrirait

Code : Tout sélectionner

=EQUIPEMENT(A1;2)

Après qques recherche sur le net, j'y parvient pas. Voici où j'en suit :
NOTA : je test ma fonction en créant une procédure sans passage d'argument pour plus de rapidité lors des tests.

■ CAS 1 : Erreur du type NoSuchElementException

Code : Tout sélectionner

Sub TestFonctionEQUIPEMENT

 Dim oFonction as Object 
 oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess")  
  
 Dim params(3) ' 4 arguments à transmettre
 params(0) = "1118" ' <-- n° fictif pour faire les tests. Dans la fonction finale, ce sera un argument passé dans la fonction
 params(1)= "'file:///CHEMIN_DOC_REFERENCE/NOM_FICHIER_REFERENCE.ods'#$NOM_FEUILLE.A$1:B$1000;2;0"
 params(2)= "2"
 params(3)= "0"

Print oFonction.callFunction("RECHERCHEV",params()) ' <-- erreur : NoSuchElementException
 
End Sub

■ CAS 2 : : Erreur du type Variable d'objet non définie

Code : Tout sélectionner

Sub TestFonctionEQUIPEMENT

 Dim oFonction as Object 
 oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess")  
  
Print oFonction.callFunction("RECHERCHEV","1118","'file:///CHEMIN_DOC_REFERENCE/NOM_FICHIER_REFERENCE.ods'#$NOM_FEUILLE.A$1:B$1000","2","0") ' <-- Variable d'objet non définie
 
End Sub

Ma méthode n'est peut être pas la bonne.
Auriez-vous des propositions à me faire ?

PS : pour info si certains voulaient rechercher sur des sites anglais, le nom pour la fonction RECHERCHEV en anglais est VLOOKUP*

* édition : correction du terme de la fonction VLOOKUP

[Dude]

Salut,

Lire le suprême de code [Calc] Exécuter des fonctions par programmation
RECHERCHEV est inconnu du Basic, callFunction appelle toujours la fonction en anglais VLOOKUP
Une recherche dans le forum montre d'ailleurs un exemple de code :
https://forum.openoffice.org/fr/forum/v ... recherchev

[Asghaard]

Ok pour la fonction en anglais, c'était une erreur d’inattention.

J'ai continuer mes recherches et ai fait différents tests mais je bloque toujours au niveau du chemin du fichier.
(j'ai bien vérifier que le fichier existe bien à l'endroit indiqué dans l'argument).

Si je me construit un tableau dans une autre feuille du même classeur, le recherche fonctionne correctement.
Mon problème est donc de passer une plage d'un autre classeur en argument. (normalement c'est une plage nommée mais je vais pas trop compliquer pour le moment).

■ En copiant la feuille de l'autre classeur dans le document où s'exécute la macro, ça fonctionne.

Code : Tout sélectionner

Function TestFonction

 Dim oFonction as Object 
 oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess")  
 
 Dim oSheet As Object
 oSheet = ThisComponent.Sheets.getByIndex(1) ' Feuille 2

 Dim params(3) ' 4 arguments à transmettre
 params(0) = "250"
 params(1) = oSheet.getCellRangeByName("A1:B1500") 'La plage de données est passée en arguments
 params(2)= 2
 params(3) = 0

Print "TestFonction = " & oFonction.callFunction("VLOOKUP",params())

End Function

■ Voici le code qui me pose problème (IllegalArgumentException).

Code : Tout sélectionner

Function TestFonction

 Dim oFonction as Object 
 oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess")  
 
 Dim params(3) ' 4 arguments à transmettre
 params(0) = "250"
 params(1)= "'file:///CHEMIN_FICHIER.ods'#$NOM_FEUILLE.A$1:B$1500" ' <-- quelle syntaxe utiliser ?
 params(2)= 2
 params(3) = 0

Print "TestFonction = " & oFonction.callFunction("VLOOKUP",params()) <-- erreur : IllegalArgumentException

End Function

Une petite idée sur la syntaxe à utiliser ?

[Dude]

Mets en PJ les deux classeurs.
Prends soin de localiser la source dans un répertoire temporaire (ex : c:\temp) afin de faciliter le test.

[Piaf]

Bonjour
Teste

Code : Tout sélectionner

Function TestFonction
Dim oFonction as Object, oDoc as Object,oSheet As Object
Dim adresseDoc as String
Dim propFich(0) As New com.sun.star.beans.PropertyValue
	oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess") 
	adresseDoc = ConvertToURL("Chemin du fichier")
	propFich(0).Name = "Hidden"
	propFich(0).Value = True
	oDoc = StarDesktop.loadComponentFromURL(adresseDoc, "_blank", 0, propFich() )
	oSheet = oDoc.Sheets.getByIndex(0)
	Dim params(3)
	params(0) = "17"
	params(1) = oSheet.getCellRangeByName("A1:A1500")
	params(2)= 1
	params(3) = 0
	Print "TestFonction = " & oFonction.callFunction("VLOOKUP",params())
	oDoc.Close(True)
End Function

J'ai changé les paramètres pour les tests.
A+

[Asghaard]

@Piaf
J'étais sur le point de poster mes documents dépersonnalisé en pièce jointe quand j'ai vu ta solution.
Je l'ai testé vite fait, ça à l'air de fonctionner bien que l’exécution soit un peu longue (à cause des ouvertures/fermetures successives).

Je teste tout ça demain de façon plus approfondie et je posterai une solution et mettrai le sujet en résolu si c'est le cas.

Merci.

PS : je joins tout de même mes pièces jointes pour le moment avec la solution de Piaf intégrée vite fait.

[Piaf]

Re

bien que l’exécution soit un peu longue (à cause des ouvertures/fermetures successives).

Normal dans l'exemple le fichier est ouvert et fermé à chaque exécution de la macro.
Si tu dois utiliser la fonction de nombreuses fois, il est possible de garder le fichier ouvert et dans ce cas la réponse est beaucoup plus rapide.
Un petit exemple en utilisant le code de cris59 dans ce fil [Résolu]savoir si un document OOo est déjà ouvert

Code : Tout sélectionner

Option explicit

Function Reference(adresseDoc) as Object
Dim lesDocs as Object, leDoc as Object
Dim docOuvert as Boolean
Dim propFich(0) as new com.sun.star.beans.PropertyValue
	propFich(0).Name = "Hidden"
	propFich(0).Value = TRUE
	lesDocs = Stardesktop.Components.createEnumeration
	docOuvert = FALSE
	While lesDocs.hasMoreElements
		leDoc = lesDocs.nextElement
		If leDoc.URL = adresseDoc Then
			docOuvert = TRUE
			Reference = leDoc
			Exit Function
		End If   
	Wend
	If NOT docOuvert Then
		leDoc = Stardesktop.LoadComponentFromURL(adresseDoc, "_blank", 0, propFich())
		Reference = leDoc
    End If
End Function
    
Function TestFonction(Id)
Dim oFonction as Object, oDoc as Object,oSheet As Object
Dim adresseDoc as String
	oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess") 
	adresseDoc = ConvertToURL("Chemin du fichier/ReferencePays.ods")
	oDoc = Reference(adresseDoc)
	oSheet = oDoc.Sheets.getByIndex(0)
	Dim params(3)
	params(0) = Id
	params(1) = oSheet.getCellRangeByName("A1:B1500")
	params(2)= 2
	params(3) = 0
	TestFonction = oFonction.callFunction("VLOOKUP",params())
 '  oDoc.Close(True)
End Function

Il faut par contre ajouter une procédure qui ferme le fichier Références pays à partir du moment ou la fonction n'est plus nécessaire.
Par exemple

Code : Tout sélectionner

Sub CloseReference()
Dim lesDocs as Object, leDoc as Object
Dim adresseDoc as String
	adresseDoc = ConvertToURL("Chemin du fichier/ReferencePays.ods")	
	lesDocs = Stardesktop.Components.createEnumeration
	While lesDocs.hasMoreElements
		leDoc = lesDocs.nextElement
		If leDoc.URL = adresseDoc Then
			leDoc.Close(True)
			Exit Sub
		End If   
	Wend
End Sub

sur l'évènement Le document va être fermé.
A+

[Dude]

En principe, un CallFunction est utilisé en dehors de Calc.
Donc, je ne vois pas bien son intérêt dans ton classeur.
Pourquoi ne pas faire tout simplement une insertion de formule ?

Code : Tout sélectionner

Function PaysFichier(Id)
	sODS = ConvertToUrl ("c:\temp\ReferencePays.ods")
	sPlage = "'" & sODS & "'#$ListePays.A1:B1500"
	sFormule = "=VLOOKUP("""& Id & """;"& sPlage &";2)"
	PaysFichier = ThisComponent.CurrentSelection.setFormula(sFormule) 
End Function

[Asghaard]

Merci de vous pencher sur mes questions.
Je remet les mains dedans aujourd'hui.

En principe, un CallFunction est utilisé en dehors de Calc.
Donc, je ne vois pas bien son intérêt dans ton classeur.
Pourquoi ne pas faire tout simplement une insertion de formule ?

► C'est pour simplifier la syntaxe afin que tout le monde dans mon service puisse l'utiliser.

Aujourd'hui, sous Calc, la syntaxe serait :

Code : Tout sélectionner

=SI(ESTERREUR(RECHERCHEV(B3;'file:///C:/Temp/ReferencePays.ods'#$ListePays.A$1:B$500;2;0));"-";RECHERCHEV(B3;'file:///C:/Temp/ReferencePays.ods'#$ListePays.A$1:B$500;2;0))

L'idée est donc que la formule affichée soit :

Code : Tout sélectionner

=PAYS(B3;2)

ce qui est plu simple pour la lecture.

NOTA : Je prend l'exemple d'une fonction qui s'appelle PAYS mais comme évoqué dans mon premier message, il s'agit d'équipement, la fonction sera donc =EQUIPEMENT(B3;2).
Je ne peux pas fournir le vrai fichier et j'ai donc réalisé cette liste avec des pays fictifs histoire de pouvoir partager sur du concret.

De plus, nous travaillons souvent avec des plages nommées ce qui transforme le nom de la source en

Code : Tout sélectionner

DDE("soffice";"C:/Temp/ReferencePays.ods";"NomPlage";0)

La formule finale serait donc :

Code : Tout sélectionner

=SI(ESTERREUR(RECHERCHEV(B3;DDE("soffice";"C:/Temp/ReferencePays.ods";"NomPlage";0);2;0));"-";RECHERCHEV(B3;DDE("soffice";"C:/Temp/ReferencePays.ods";"NomPlage";0);2;0))

Surtout que parfois, cela sert juste à contrôler qquchose et la formule se retrouve imbriqué dans des condition "SI".
Ça devient trop compliqué à comprendre et ça effraie mes collègues.

NOTA : je débute sous LibreOffice et j'ai peut être pas la bonne solution pour faire une RechercheV dans une plage nommée.

Je vais quand même tester ta proposition pour voir l'effet concrète dans son utilisation.

[Asghaard]

@Dude
J'ai testé ta dernière proposition est c'est intéressant comme fonctionnement.
j'ai apporté une petite correction pour que le n°Id ne soit pas considéré comme du texte sinon ça ne fonctionnait pas :

Code : Tout sélectionner

sFormule = "=VLOOKUP(" &  Id & ";"& sPlage &";2)"

Ça pourrait faire l'affaire dans un premier temps, l'utilisation reste simple, seule la formule qui s'affiche "toute seule" pourrait compliquer la relecture.
Ça me plaît mais j'ai tendance à être perfectionniste donc je vais persévérer et voir les propositions de Piaf.

De plus, je suis au tout début de mon apprentissage avec les macros sous LibreOffice et bien que je n'aime pas du tout les macros sous LibreOffice (pour le moment), j'aime apprendre et je ne veux pas juger sans connaître mieux.

[Asghaard]

Voici donc ma solution de base qui me permet de mettre mon sujet comme résolu.

Merci à Piaf pour son aide qui m'a fait gagné beaucoup de temps.

Merci à Dude pour ses remarques et sa formule "magique" que j'utiliserait surement plus tard : l'idée de saisir une fonction qui se fait automatiquement remplacée par une autre formule me plaît.
Typiquement, je pense utiliser ce genre de fonctionnement avec des fonctions du style "=defaut" pour que la cellule soit remplacé par une formule par défaut.
Ça permet ainsi d'écrire dans une cellule qui contenait une formule (ce qui écrase le contenu), puis de rappeler facilement la formule sans la connaître, grâce à une simple fonction (je ne sais pas si c'est très clair ce que je raconte).

Dans ma solution ci-dessous, le document qui me sert de référence est ouvert en lecture seule et sera fermé lors de la fermeture du classeur
(exécution de la macro 'CloseReference' sur l'évènement 'le document va être fermé').
Le second argument de la formule est optionel. En son absence, c'est le contenu de la 2ème colonne qui est retourné.

Code : Tout sélectionner

Option Explicit

Const CheminDoc = "C:\Temp\FichierReference.ods"
Const PlageDocument = "A1:D1500"
Const MaxColonne = 4

Function EQUIPEMENT(ID As Integer, Optional NoColonne As Byte)

    Dim oFonction As Object, oDoc As Object, oSheet As Object
    Dim adresseDoc As String

    oFonction = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    adresseDoc = ConvertToURL(CheminDoc)
    oDoc = Reference(adresseDoc)
    oSheet = oDoc.Sheets.getByIndex(0)
    
    If IsMissing(NoColonne) Or NoColonne < 1 Or NoColonne > MaxColonne Then NoColonne = 2
    
    Dim params(3)
    params(0) = ID
    params(1) = oSheet.getCellRangeByName(PlageDocument)
    params(2) = NoColonne
    params(3) = 0
    EQUIPEMENT = oFonction.callFunction("VLOOKUP", params())

End Function

Function Reference(adresseDoc) As Object ' s'exécute à la fermeture du document

    Dim lesDocs As Object, leDoc As Object
    Dim docOuvert As Boolean
    
    Dim propFich(1) As New com.sun.star.beans.PropertyValue
    propFich(0).Name = "Hidden"
    propFich(0).Value = True
    propFich(1).Name = "ReadOnly"
    propFich(1).Value = True
    lesDocs = Stardesktop.Components.createEnumeration
    docOuvert = False
    
    While lesDocs.hasMoreElements
        leDoc = lesDocs.nextElement
        If leDoc.URL = adresseDoc Then
            docOuvert = True
            Reference = leDoc
            Exit Function
        End If
    Wend
    
    If Not docOuvert Then
        leDoc = Stardesktop.LoadComponentFromURL(adresseDoc, "_blank", 0, propFich())
        Reference = leDoc
    End If
    
End Function

Sub CloseReference()

    Dim lesDocs As Object, leDoc As Object
    Dim adresseDoc As String
    
    adresseDoc = ConvertToURL(CheminDoc)
    lesDocs = Stardesktop.Components.createEnumeration
    
    While lesDocs.hasMoreElements
        leDoc = lesDocs.nextElement
        If leDoc.URL = adresseDoc Then
            leDoc.Close (True)
            Exit Sub
        End If
    Wend
    
End Sub

Ainsi, je peux effectuer une recherche verticale qui s'écrit simplement :
=EQUIPEMENT(250) ◄ par défaut, retourne le contenu de la colonne 2
ou
=EQUIPEMENT(250;3) ◄ retourne le contenu de la colonne 3