Discussion :

[Lucci]

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Pierre Fauconnier]

Bonjour

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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?

[Lucci]

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 :

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 ?

[Pierre Fauconnier]

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é...

[Lucci]

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

Ok merci je testerai.

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

?

Mais à quoi correspond " & Cle, cn?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Pierre Fauconnier]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

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.

[Lucci]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Pierre Fauconnier]

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...

[Lucci]

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.

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...

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...

Bonne nuit...

Merci à toi aussi

[Pierre Fauconnier]

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

[Lucci]

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 ). 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.