Créer une fonction Excel d'instruction SELECT sur une base ACCESS (ADO?)

Bonjour,

Fort de l’aide précieuse que m’ont apportée bidou et fring, j’apprends doucement le VBA. La deuxième phase (sur 3) nécessite plus que du VBA, je demande donc encors votre aide afin d’avoir un exemple… proche de mes besoins.
Je pense devoir utiliser ADO pour réaliser ce qui suit, toutefois merci de me corriger si je fais fausse route.

Toujours sous Excel 2000 ( ;) )
Une base de données Access2000 DBmyCREA située dans V:

Objectif :
Créer une fonction avec paramètres : SQLmyCREA(annee ;mois ; champ ; table ;autre).
Où SQLmyCREA exécute la requête suivante sur DBmyCREA:

Code:

---

1 2 3

SELECT champ FROM table WHERE X=annee , Y=mois , Z=autre ;

---

Avez-vous un exemple de code qui donnerait ça ?

Merci d’avance
Bien à vous

Bonjour

Après avoir coché la référence à Microsoft ADO 2.x, on peut s'inspirer de ceci

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub RequeteAccess() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset     ' Choisir une des deux lignes suivantes, selon que tu as créé un dsn ou pas ' Le dsn est plus simple à gérer lors du déploiement sur plusieurs postes qui ' pointent vers une même base cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Données\bd1.mdb;" cn.Open "dsn=tests access"   rs.Open "select cle, nom from table1 where cle<10", cn   ... ...   rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub

---

Si tu souhaites en faire une fonction, alors, il faut faire attention à refermer les connexions lorsque tu n'en as plus besoin, mais pas avant, car ton recordset (le résultat de la requête SQL) sera perdu à la fermeture de la connexion.

Tu pourras avoir une fonction pour obtenir la connexion

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Function GetConnexion() As ADODB.Connection Dim cn As New ADODB.Connection   ' Choisir une des deux lignes suivantes, selon que tu as créé un dsn ou pas ' Le dsn est plus simple à gérer lors du déploiement sur plusieurs postes qui ' pointent vers une même base cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Données\bd1.mdb;" cn.Open "dsn=tests access"   Set GetConnexion = cn Set cn = Nothing End Function

---

Une autre pour obtenir le jeu d'enregistrements selon ton critère

Code:

---

1 2 3 4 5 6 7

Function GetEnregistrementsTable1(cn As ADODB.Connection, Cle As Long) As ADODB.Recordset Dim rs As New ADODB.Recordset   rs.Open "select nom from table1 where cle < " & Cle, cn Set GetEnregistrementsTable1 = rs Set rs = Nothing End Function

---

Les deux fonctions étant utilisées au sein d'une procédure

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Sub UtiliserFonctions() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset   Set cn = GetConnexion() Set rs = GetEnregistrementsTable1(cn, 10)   ... ...   rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub

---

Comme tu le vois dans les ouvertures de connexion, l'utilisation d'un DSN s'avère plus simple que de créer la chaine de connexion, puisque c'est le dsn qui gère une fois pour toute l'emplacement du fichier. En cas de modification de cet emplacement, tu modifies une seule fois le dsn sans devoir modifier tous les applicatifs qui se connecteront à la base.

Ok?

Bonjour et merci d’avoir répondu.

Je ne suis malheureusement pas allé bien loin.
J’ai coché MS ADO dans référence, copier le premier code, exécuté puis message d’erreur :

Citation:

---

Erreur de compilation

Type défini par l’utilisateur non défini

---

En googlant je tombe sur un site de MS… Il semblerait que je n’ai pas sélectionné la bonne bibliothèque. Pourtant c’est bien “Microsoft ADO Ext. 2.7 for DDL and security ». Et c’est la seule référence comportant le nom ADO.


Sinon j’ai le plus grand mal le comprendre le code. Quel est le nom de ma fonction ? Quelles serons les paramètres ?

La bonne référence est
Microsoft Active Data Objects Library 2.7

Quel code ne comprends-tu pas? Il n'y a même pas dix lignes de code effectif sur l'ensemble de ce que je t'ai donné...

Citation:

---

Envoyé par Pierre Fauconnier

La bonne référence est
Microsoft Active Data Objects Library 2.7

---

Ok merci je testerai.

Citation:

---

Envoyé par Pierre Fauconnier

Quel code ne comprends-tu pas? Il n'y a même pas dix lignes de code effectif sur l'ensemble de ce que je t'ai donné...

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Function GetConnexion() As ADODB.Connection Dim cn As New ADODB.Connection   ' Choisir une des deux lignes suivantes, selon que tu as créé un dsn ou pas ' Le dsn est plus simple à gérer lors du déploiement sur plusieurs postes qui ' pointent vers une même base cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Données\bd1.mdb;" cn.Open "dsn=tests access"   Set GetConnexion = cn Set cn = Nothing End Function

---

Ouvrir la connexions ok.

Code:

---

1 2 3 4 5 6 7

Function GetEnregistrementsTable1(cn As ADODB.Connection, Cle As Long) As ADODB.Recordset Dim rs As New ADODB.Recordset   rs.Open "select nom from table1 where cle < " & Cle, cn Set GetEnregistrementsTable1 = rs Set rs = Nothing End Function

---

Concrètement, lorsque je voudrais utiliser le module je devrais écrire dans une cellule:
GetEnregistrement(nom;table;cle)
et ça donnerait le résultat de la requête suivante:

Code:

---

select nom from table1 where cle < " & Cle, cn

---

?

Mais à quoi correspond " & Cle, cn?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Sub UtiliserFonctions() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset   Set cn = GetConnexion() Set rs = GetEnregistrementsTable1(cn, 10)   ... ...   rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub

---

Et là une procédure pour enchainer... ok.

Citation:

---

Code:

---

1 2 3 4 5 6 7

Function GetEnregistrementsTable1(cn As ADODB.Connection, Cle As Long) As ADODB.Recordset Dim rs As New ADODB.Recordset   rs.Open "select nom from table1 where cle < " & Cle, cn Set GetEnregistrementsTable1 = rs Set rs = Nothing End Function

---

Concrètement, lorsque je voudrais utiliser le module je devrais écrire dans une cellule:
GetEnregistrement(nom;table;cle)
et ça donnerait le résultat de la requête suivante:

Code:

---

select nom from table1 where cle < " & Cle, cn

---

?

Mais à quoi correspond " & Cle, cn?

---

Là, c'est du VBA. Je ne connais pas ton niveau en ce domaine.
Non, la fonction n'est pas utilisable telle quelle dans une cellule Excel. Elle renvoie un jeu d'enregistrements. Ce jeu peut contenir un enregistrement unique ou plusieurs enregistrements ou aucun enregistrement, selon la clause WHERE de la requête SQL. Si tu veux renvoyer la valeur d'un champ en particulier, il faut adapter la fonction ou le code...

Citation:

---

Mais à quoi correspond " & Cle, cn?

---

Cle représente la valeur passée en paramètre de la fonction, et sert à filtrer les enregistrements de la requête.
cn est l'objet connexion dont a besoin l'objet recordset rs pour pouvoir se connecter à la source de données.

Connais-tu VBA et as-tu déjà travaillé avec les objets ADO?

Pourrais-tu préciser ce que tu souhaites réaliser? Pas ce que tu penses devoir faire pour obtenir le résultat, mais le résultat lui-même, ainsi que la situation de laquelle tu pars pour arriver au résultat? Car il y a peut-être plus simple que ce à quoi tu penses.

Mon niveau en programmation est médiocre, je ne suis pas « informaticien ». Je connais un peu la POO, j’ai utilisé Ruby. Sinon, ce que j’ai codé c’est essentiellement du sript shell (Bash). J’utilise beaucoup le SQL (même si à cause de Business Object, je n’ai plus pratiqué de SQL depuis quelque temps).
En VBA, je pars de 0. J’ai envie de prendre le temps d’apprendre ce langage car j’utilise Excel et ACCESS au boulot. Je pense qu’apprendre par la pratique me permettra d’assimiler plus vite. Ça tombe bien j’ai une idée…

Le but est de générer des comptes de résultats analytiques de manière automatisée (et ainsi gagner 5x2 semaines par an). Les informations sont dans une base ACCESS.
Voilà comment ça devrait fonctionner :

Première étape (réalisée grâce à l’aide trouvée sur le forum) :
Une feuille avec une liste d’UF (disons des services, je suis dans le secteur hospitalier) et une feuille avec un Compte de résultat analytique type (CREA type).
La macro crée une feuille par service. Le nom de chaque feuille est le nom de l’UF. Le contenu est celui de CREA type.

Deuxième étape (j’en suis là) :
Écrire une fonction qui sera utilisée pour extraire de la base ACCESS chaque valeur des comptes de résultat. Une fonction du style :
=MyCREA(année ; mois ; Valeur; table ;UF)
A partir de cette fonction dans une case Excel la macro réalisera la requête suivante sur la base ACCESS :

Code:

---

1 2 3

SELECT valeur FROM table WHERE annee=année, mois=mois, service=UF ;

---

Et inscrirait la valeur.

Exemple1 : le CREA de la cardiologie. Dans la cellule dans laquelle doit apparaître le montant de dépenses Titre1 (dépenses directes en personnel) à juin 2008.
=MyCREA(2008 ;06 ;Titre1 ;DEPENSES ;CARDIO)
Exemple2 :le CREA de dialyse. Dans la cellule dans laquelle doit apparaître le montant des dépenses Titre2 à juin.
=MyCREA(2008 ;06 ;Titre2 ;DEPENSES ;DIALYSE)

Troisième étape, automatisation (je n’y suis pas pour l’instant) :
Remplir mon CREA type avec la fonction MyCREA pour chaque dépense, chaque produit…
=MyCREA(2008; 06 ; Valeur; table ;GENERIQUE)
Et créer une macro qui remplacera dans chaque CREA de service le terme générique par l’UF.
Exemple :
=MyCREA(2008 ;06 ;Titre2 ;DEPENSES ;GENERIQUE) dans le CREA du service ORL sera remplacé par :
=MyCREA(2008 ;06 ;Titre2 ;DEPENSES ;ORL)



Au final, lorsque je lancerai le module ça donnera pour chaque valeur de la liste (chaque service):
Une copie de la feuille CREA type
Remplacement automatique dans chaque formule =MyCREA de GENERIQUE par le code du service
L’extraction du montant pour toutes les dépenses et recettes grâce à MyCREA
Et ça donnera un compte de résultat analytique.

Voilà voilà, j’espère avoir été assez clair.

Merci pour ton aide.

Ok...

Je me dis ceci...

Avec une base access bien faite, des requêtes bien faites dans Access, tu pourrais peut-être t'orienter vers un tableau croisé dynamique dans Excel. Simple, efficace...

Tu peux aussi, via MSQuery, récupérer des données Access dans des feuilles Excel puis les analyser avec des fonctions Excel telles que SOMMEPROD, ...

L'avantage de procéder ainsi par rapport à du code VBA, c'est que c'est beaucoup moins lourd à mettre en place et à maintenir, et que c'est moins figé. Tu utilises alors VBA pour les quelques bricoles pour lesquelles cela vaut la peine de l'utiliser.

Sinon, c'est un peu réinventer la roue...

Vois si ce que je te propose peut te convenir. Je pense en tout cas que c'est certainement une piste à creuser...

Bonne nuit...

Citation:

---

Envoyé par Pierre Fauconnier

Avec une base access bien faite, des requêtes bien faites dans Access, tu pourrais peut-être t'orienter vers un tableau croisé dynamique dans Excel. Simple, efficace...

---

C'est ce que j'ai fait en un premier temps. En ligne les UF, en colonne les valeurs. Le CREA type va chercher les valeurs par des recherchesV. Ca fonctionne bien mais:
- malgré les apparences c'est très lourd (valeurs changeantes, 4 niveaux d'analyse, )
- si j'avais ma fonction MyCREA je pourrait utiliser la procédure du post précédant pour tout les documents du contrôle de gestion... et pas seulement pour les comptes de résultat ana.

Citation:

---

Tu peux aussi, via MSQuery, récupérer des données Access dans des feuilles Excel puis les analyser avec des fonctions Excel telles que SOMMEPROD, ...

---

Mêmes problèmes...

Citation:

---

Sinon, c'est un peu réinventer la roue...

---

Carrément! Je suis persuadé que quelqu'un a déjà créé une fonction avec paramètres qui effectue une requête sur une base ACCESS, mais je ne trouve pas sur internet...

Citation:

---

Bonne nuit...

---

Merci à toi aussi

Citation:

---

Carrément! Je suis persuadé que quelqu'un a déjà créé une fonction avec paramètres qui effectue une requête sur une base ACCESS, mais je ne trouve pas sur internet...

---

Je t'ai donné une fonction qu'il fallait bien entendu adapter à ton cas.

En m'inspirant de ceci

Citation:

---

SELECT valeur
FROM table
WHERE annee=année, mois=mois, service=UF ;

---

j'ai recréé une fonction qui fait exactement ce que tu souhaites. Pour la clarté du code et la facilité de la maintenance, j'ai travaillé avec un dsn pour la connexion aux données.
Sur base d'une table appelée "matable", dont la structure contiendrait au moins les champs suivants; Annee (numérique), Mois (numérique), Service (texte) et Valeur (numérique), voici une fonction qui renvoie la valeur en fonction du service, du mois et de l'année

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Function MyCrea(Annee, Mois, Service) As Double Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sql As String Dim Valeur As Double   sql = "select valeur from matable where annee = " & Annee & " and mois = " & Mois & " and service ='" & Service & "'" cn.Open "dsn=hopital sur access" rs.Open sql, cn   If Not rs.EOF Then MyCrea = rs!Valeur   rs.Close cn.Close   Set rs = Nothing Set cn = Nothing   End Function

---

Si ce n'est que, à mon avis, la structure de ta table n'est pas celle-là, et que tu as probablement des dates à la place des champs Annee et Mois, ce qui imposera un regroupement, mais c'est parfaitement faisable.

A toi de voir et de venir nous dire...

:bravo:
Génial merci infiniment!

Elle sera adaptée de manière à avoir les montants cumulé à une date. Comme je n’ai pas Windows chez moi, je vais devoir attendre d’être au boulo et d’avoir le temps (tout est toujours urgent :roll:). Mais dès que c’est testé je te donne des nouvelles et je copie/colle le code (au cas où ça servirait à d’autres…)

Bonne fin de semaine.