[XL-2007] Fonction non définie dans l'expression [Résolu]

[Bamban]

Bonjour tout le monde,

Voila j'ai un léger problème qui survient entre access 2007 et excel 2007.

J'explique :
Etape une. J'ai réalisé une requète SQL sous access. Un dizaine de champs dont un en particulier qui se présente comme suit : Offre Complète: offreComplete([TYPE_OFFRE];[RESEAU];[CLIENT]). En fait, offreComplete est une fonction VBA codée sous access qui permet, après observation des 3 champs entrés en arguments, de renvoyer une chaîne string. Quand j'exécute la requète sous access, elle fonctionne.

Etape deux. Je vais sous excel pour faire le traitement de cette table. Comme j'ai bien automatiser un poil les choses, je crée une connexion à cette requête (onglet données > connexions > ajouter, je sélectionne ma base de donnée et ma requète, une fois la connexion crée, je vais dans données > données externes > connexions existante et hop). Et là, erreur ! Fonction OffreComplete non définie dans l'expression.

Je récapépayte : la fonction VBA fonctionne, la requête access fonctionne, la mise à jour sous excel fonctionne si j'enlève le champ "offre complète", la mise à jour plante si je remet ce footu champ

Je précise avant de me faire lobotomiser, la solution de la FAQ microsoft (http://support.microsoft.com/kb/275110/fr) marche pas. C'est pas adapté à mon problème vu que la requête fonctionne sous access.

Merci de votre aide !

[tototiti2008]

Bonjour BamBan,

Les données externes utilisent un lien ODBC vers ta base Access
Si Odbc comprend le SQL, il ne comprend pas le VBA, et comme tu pointes sur une requête, il essaye d'actualiser la requête en exécutant son SQL, qui lui fait référence à ta fonction VBA qu'il ne comprend pas...

Bref, les fonctions VBA fonctionnent bien dans Access mais ne peuvent pas être appelées depuis l'extérieur, désolé.

Donc soit tu fais une requête sans cette fonction, et tu refais ta fonction en Excel (dans la mesure où tu as les données nécessaires), soit tu remplis une table temporaire avec le résultat de ta requête (mais ça peut poser des problèmes de mise à jour) et tu fais le lien sur la table temporaire, c'est tout ce qui me vient à l'esprit

[jfontaine]

Bonjour,

Peut être une piste si une fonction Access se comporte de la même façon qu'une fonction SQL Server

pour la connexion, j'utilise un ODBC, mais tu peux remplacer par une chaine de connexion Access

Ici la fonction est ufn_DatePaques et a comme paramètre l'année

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Dim cnn As New ADODB.Connection
Dim rec As New ADODB.Recordset
 
cnn.Open "dsn=Mabase;uid=User;pwd=Pass"
 
rec.Open "select dbo.ufn_DatePaques(2011)", cnn, adOpenDynamic, adLockReadOnly
 
Range("A1").CopyFromRecordset rec
 
rec.Close
Set rec = Nothing
cnn.Close
Set cnn = Nothing

[Bamban]

Envoyé par tototiti2008

Bref, les fonctions VBA fonctionnent bien dans Access mais ne peuvent pas être appelées depuis l'extérieur, désolé.

Je suis pas d'accord. Techniquement, je ne saurai pas t'expliquer pourquoi ce que tu vient de dire n'est pas applicable dans mon cas. Je précise : j'ai au moins 3 ou 4 requêtes qui utilisent exactement la même fonction VBA (et d'autres fonctions aussi). Et j'ai en parallèle des fichiers excel qui exploitent des requêtes qui comportent des champs remplis par des fonction VBA sous access. Des explications ?

Merci pour ta réponse en tout cas.

[tototiti2008]

Bonjour jfontaine,
Re,

j'ai au moins 3 ou 4 requêtes qui utilisent exactement la même fonction VBA (et d'autres fonctions aussi). Et j'ai en parallèle des fichiers excel qui exploitent des requête qui comportent des champs remplis par des fonction VBA sous access. Des explications ?

Non, mais je suis intéressé de savoir comment ça se fait que ça marche dans d'autres cas. J'étais vraiment persuadé que c'était impossible...
Aurais-tu un exemple de fonction VBA utilisée dans une requête Access appelable par les données externes ? Je ferais des tests de mon coté

[Bamban]

Ca m'a l'air bien compliqué ta solution jfontaine :/

Elles sont assez simple en réalité, les fonctions que j'utilise. Ce fonctions se servent uniquement de la valeur d'autres champs de la requête pour donner un résultat.

Un exemple de fonction :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Public Function offreComplete(offre As Variant, reseau As Variant, client As Variant, offreComm As Variant) As Variant
 
    If IsNull(offre) Then
        offreComplete = Null
 
    Else
 
        Select Case offre
            Case Is = "SYNCHRO"
                If reseau = "V" Then
                    offreComplete = "PBX V"
                Else
                    offreComplete = "PBX D"
                End If
            Case Is = "NET"
                If client = "PIC" Then
                    offreComplete = "NET PIC"
                Else
                    offreComplete = offre
                End If
            Case Is = "SYNCH"
                If IsNull(offreComm) Then
                    offreComplete = offre
                Else
                    offreComplete = offre & " (" & offreComm & ")"
                End If
            Case Else
                offreComplete = offre
        End Select
 
    End If
 
End Function

[tototiti2008]

Re,

@jfontaine : désolé, ton code n'a pas l'air de fonctionner avec une fonction VBA, doit être spécifique aux fonctions Transact SQL stockées sur SQL Server, je pense

@Bamban : Oui, ça c'est la fonction que tu n'arrives pas à appeler, moi aussi je peux te fournir celle que j'ai faite et que je n'arrives pas à appeler (très courte) :

Code :

1
2
3
Function toto(z As String) As String
    toto = Left(z, 1)
End Function

Je l'utilise dans une requête Access sans problème
Mais les données externes sur cette requête produisent la même erreur que chez toi

[Bamban]

Ah ...

Donc on retourne au point de départ alors

Ben je comprend vraiment pas pourquoi certains fichiers excel autoriseraient les fonctions VBA sur les requêtes externes et pas d'autres. J'essai de décortiquer mon fichier excel qui fonctionne pour trouver une option, un paramètre qui différerais de celui où ça ne marche pas. Une idée de où chercher ? :/

[tototiti2008]

Re,

Si, vois sur quelle requête pointe les données externes qui fonctionnent, et édite la requête dans Access pour voir quelle fonction personnalisée est utilisée
Poste-nous le code VBA de la fonction en question le cas échéant

[Pierre Fauconnier]

Salut

Envoyé par Bamban

...
Ben je comprend vraiment pas pourquoi certains fichiers excel autoriseraient les fonctions VBA sur les requêtes externes et pas d'autres. ...

Je suis très dubitatif sur ce que tu dis, car je confirme les dires de tototit2008, il n'est pas possible d'utiliser en externe une requête Access qui utilise une fonction perso développée dans la base.

Si tu as un cas qui fonctionne, ce serait sympa de nous mettre la fonction perso, le sql de la requête sql qui l'emploie et le sql de la requête Excel qui fait que ça tourne pour que l'on puisse tester, car j'ai de très gros doutes.

[Bamban]

Erf, je viens de comprendre le problème.

Mon postulat était bien erroné. En réalité, les fichiers excel qui utilisent mes tables qui contiennent des champs calculés pas VBA ne sont liés à des requêtes des sélection mais des tables. Tables bien bêtes crées à partir de requêtes (de création de tables, donc) qui elles possèdent des champs calculés en VBA.

Gloups, je suis eu.

La question qui découle de ça c'est : c'est vraiment pas possible de lier une requête de sélection qui possède un champ calculé en vba ? Si j'intègre la fonction VBA à un module de mon fichier excel ?

Merci en tout cas pour la révélation de l'origine du problème.

[tototiti2008]

Bonjour Pierre Fauconnier,
Re,

Ce que tu peux faire c'est mettre ta fonction VBA dans Excel, faire des données externes sur une requête qui contient tout sauf cette fonction VBA, faire une colonne calculée dans Excel qui se base sur la fonction VBA d'Excel (il y a même des options des données externes permettant de recopier automatiquement les colonnes calculées en fonction des lignes retournées par les données externes)

[Pierre Fauconnier]

Envoyé par Bamban

...
La question qui découle de ça c'est : c'est vraiment pas possible de lier une requête de sélection qui possède un champ calculé en vba ? Si j'intègre la fonction VBA à un module de mon fichier excel ?...

Non, ce n'est vraiment pas possible, et c'est tout aussi impossible que Access utilise au sein d'une de ses requêtes une fonction perso développée dans Excel.

Tu dois extraire les données d'Access via une requête qui ne reprend que les champs non calculés, les placer dans la feuille Excel puis recomposer les champs calculés dans des nouvelles colonnes Excel avec ta fonction perso en Excel.

[Edit] Grillé par toto [/Edit]

[Bamban]

C'est bien ce dont j'avais peur... C'est quand même plus sympa quand tout sort d'access et qu'il n'y a besoin d'aucun calcul sur excel.

Bon, je sais donc à quoi m'en tenir !

Merci pour votre aide. Dans tous les cas je n'ai pas perdu mon temps j'ai découvert un forum bien réactif et très sympathique (et pro avec ça ).

A bientôt pour une nouvel prise de tête :jap:

[tototiti2008]

Re,

L'autre solution est la table temporaire dont je te parlais au début :
tu ne mets pas ta fonction VBA dans Excel, mais dans Access tu crées une requête Création de table (par exemple) qui crée une table spécifique à laquelle Excel se connectera
Le tout est de "rafraichir" cette table temporaire au bon moment

[Bamban]

Oui, en fait c'est la solution qu'avait utilisé mon prédécesseur. Je n'avais pas pensé au fait qu'il utilise en effet une table intermédiaire.
D'où le fait que j'étais persuadé que ça pouvais marcher à ma manière ! :p