Discussion :

[Invité]

Bonjour,
J'utilise ado pour récupérer le résultat d'une requête avec une fonction personnalisé.

Code :

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

select MyFonction([champs]) from MaTable

Je sais qu'il faut utiliser l'objet command et lui fournir la procédure stocker, mais je ne suis pas très féru de ce genre de manip!

Si vous avez des liens , des conseils je suis preneur

Merci d'avance.

[nico84]

Bonjour,

Cela dépend de la base considérée :
si MaTable est dans une base access, MaFonction doit être une fonction publique décrite dans un module
si MaTable est SQL server, Oracle ou autre, MaFonction doit être définie dans cette base (et donc en transact-sql, pl-sql...)

En DAO c'est plus simple mais avec une base non access les temps de réponse peuvent être catastrophiques

[Invité]

bonjour nico84,et merci pour ta réponse!

la fonction ce trouve dans Access et la requête dans Excel!

le problème c'est de l'utiliser dans Excel via Ado. ici c'est un exercice de style pas un réel besoin dune question de culture générale disons!

Code Fonction Access :

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

1
2
3
4
 
Public Function test(champ)
test = "toto"
End Function

Code sub Excel :

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

1
2
3
4
5
6
7
Sub SubTest()
With CreateObject("ADODB.Connection")
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\dysorthographie\Desktop\Nouveau dossier (2)\Test.accdb;"
Set rs = .Execute("SELECT test([nom]) FROM table1;")
End With
 
End Sub

[nico84]

Ah je n'ai jamais fait cela non plus, mais cela peut m'intéresser

[Invité]

bonjour,
je relance la question, qui n’était pas urgente,mais qui s’avère commencé!

Code :

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

sql="select replace([champs],'a','b')from MaTable

bien évidement ça fonctionne dans ACCESS mais pas en ADO!

[Jean-Philippe André]

Salut,

comme point potentiel de probleme :
- l'emplacement de la fonction VBA que tu souhaites appeler (Excel vs. Access)

Du coup question, le code que tu as teste, il genere une erreur en particulier ou le resultat est vide ou autre ?

[Invité]

Bonjour Jean-Philippe ,


avec Sql serveur par exemple, on utilise l'objet Ado Command pour utiliser des procédure stockées. j'imagine qu'une procédure défini dans un module vba Access doit pouvoire s'utiliser de la même façon???


par exemple la méthode replace qui fonctionne dans le enquêteur d'Access, ne fonctionne pas en Ado! il devrait, soit directement soit en implémentant sa propre méthode, l'utiliser comme une procédure stockée en Ado.

Code implémentation de replace le point decimeaux :

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

Format(IIf(InStr([SECT],'.')=0,[SECT],Mid([SECT],1,InStr([SECT],'.')-1) & ',' & Mid([SECT],InStr([SECT],'.')+1,Len([SECT]))),'#0.000')

[marot_r]

Bonjour.

Autant que je sache, il n'est pas possible d'appeler des fonctions VBA de l'extérieur d'Access.
Cela viendrait du fait que VBA ne fait pas parti du moteur de Access et quand Access ne roule pas, il n'est pas "visible".

Personnellement, lassé de me battre avec Excel j'ai procédé différemment : je demande à Access de pousser mes données dans des "tables" Excel.
Le seul problème que j'ai rencontré avec cette méthode c'est que c'est un peu lent et que les formats de données sont perdus.
J'ai fait du code qui applique un format par défaut en fonction du type des données.
À part cela je bénéficient de 100% de la puissance de Access.

Ici mon code (brut de décoffrage)

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Private Sub CopierDonneesDansExcel(prmNomTableExcel As String, prmRSource As DAO.Recordset, prmFeuille As Object)
    'Copie les données de la source Access dans Excel
    Dim result As New clsResultVerif
 
    Dim Destination As Object 'Excel.Range
    Set Destination = prmFeuille.Range(prmNomTableExcel)
 
    Dim nbLigneDonnees As Long
    Dim nbColDonnees As Long
    Dim nbColDestination As Long
    Dim celluleOrigine As Object 'Excel.Range
    Set celluleOrigine = prmFeuille.Cells(Destination.row - 1, Destination.Column)
 
    Const xlCalculationAutomatic As Long = -4105
    Const xlCalculationManual As Long = -4135
 
    prmFeuille.Application.calculation = xlCalculationManual 'Neutralise le recalcul de Excel
 
    '=== Supprime les données existantes
    prmFeuille.Application.DisplayAlerts = False
    Call mdlExcel.AfficherCellule(celluleOrigine)
    nbLigneDonnees = Destination.Rows.Count
    nbColDestination = Destination.Columns.Count
    Call prmFeuille.ListObjects(prmNomTableExcel).Resize(prmFeuille.Range(celluleOrigine, celluleOrigine.Offset(1, nbColDestination - 1))) 'réduit le tableau à 1 ligne. NE PAS LE SUPPRIMER, cela casse les formules qui l'utilisent.
 
    ' === Supprime les lignes de données inutiles
    ' Conserve la 1ère ligne pour les formules éventuelles
    If nbLigneDonnees > 1 Then
        Call prmFeuille.Range(celluleOrigine.Offset(2, 0), celluleOrigine.Offset(nbLigneDonnees, nbColDestination - 1)).Clear 'Supprime les données
    End If
    ' --- Supprime les lignes de données inutiles
 
    ' === Supprime les données mais garde les formules
    Dim cellule As Object 'Excel.Range
    Dim ligne As Object 'Excel.range
    Set ligne = prmFeuille.Range(celluleOrigine.Offset(1, 0), celluleOrigine.Offset(1, nbColDestination - 1))
    For Each cellule In ligne.Cells
        Call mdlExcel.AfficherCellule(cellule)
 
        If Left(cellule.Formula, 1) <> "=" Then
            cellule.Clear
        End If
 
    Next cellule
    Set cellule = Nothing
    ' --- Supprime les données mais garde les formules
 
    prmFeuille.Application.DisplayAlerts = True
    '--- Supprime les données existantes
 
    Dim champ As DAO.Field
    Dim iChamp As Long
 
    '=== Copie les noms des champs
    prmFeuille.Application.DisplayAlerts = False
 
    For iChamp = 0 To prmRSource.Fields.Count - 1
        Set champ = prmRSource.Fields(iChamp)
 
        'Debug.Print champ.Name, champ.Type, dbBoolean, dbLong, dbDouble, dbText: DoEvents
 
        Set cellule = prmFeuille.Cells(Destination.row - 1, Destination.Column + iChamp)
        Call mdlExcel.AfficherCellule(cellule)
        cellule = champ.Name
        Set champ = Nothing
        Set cellule = Nothing
    Next iChamp
 
    prmFeuille.Application.DisplayAlerts = True
    '--- Copie les noms des champs
 
    '=== Copie les données
    prmFeuille.Application.DisplayAlerts = False
 
'    If mdlGlobal.EST_MODE_DEBUG Then
'        Dim c As New clsChrono: Call c.Demarrer(prmRSource.Name, True)
'    End If
 
    Call prmFeuille.Cells(Destination.row, Destination.Column).CopyFromRecordset(prmRSource)
 
'    If mdlGlobal.EST_MODE_DEBUG Then
'        c.Arreter: Call c.Afficher: Set c = Nothing
'    End If
 
    prmFeuille.Application.DisplayAlerts = True
    '--- Copie les données
 
    '=== Défini un tableau sur les données
    prmFeuille.Application.DisplayAlerts = False
    nbLigneDonnees = prmRSource.RecordCount
 
    If nbLigneDonnees = 0 Then
        nbLigneDonnees = 1 'Au minimum une ligne de données dans un objet ListObject
    End If
 
    nbColDonnees = prmRSource.Fields.Count
 
    If nbColDonnees > nbColDestination Then
            Call prmFeuille.ListObjects(prmNomTableExcel).Resize(prmFeuille.Range(celluleOrigine, celluleOrigine.Offset(nbLigneDonnees, nbColDonnees - 1)))
        Else
            Call prmFeuille.ListObjects(prmNomTableExcel).Resize(prmFeuille.Range(celluleOrigine, celluleOrigine.Offset(nbLigneDonnees, nbColDestination - 1)))
    End If
 
    prmFeuille.Application.DisplayAlerts = True
    '--- Défini un tableau sur les données
 
    '=== Formater les colonnes de données
    prmFeuille.Application.DisplayAlerts = False
 
    Dim Zone As Object 'Excel.Range
 
    For iChamp = 0 To prmRSource.Fields.Count - 1
        Set champ = prmRSource.Fields(iChamp)
 
        'Debug.Print champ.Name, champ.Type, dbBoolean, dbLong, dbDouble, dbText: DoEvents
 
        Set Zone = prmFeuille.Range(prmFeuille.Cells(Destination.row, Destination.Column + iChamp), prmFeuille.Cells(Destination.row + Destination.Rows.Count - 1, Destination.Column + iChamp))
        Call mdlExcel.AfficherCellule(Zone)
 
        Select Case champ.Type
            Case dbInteger, dbLong
                'Ne rien faire, pas de décimale
 
            Case dbSingle, dbDouble, dbCurrency
                Zone.NumberFormat = "0.00"
 
            Case dbdate
                Zone.NumberFormat = "yyyy\-mm\-dd hh\:mm\:ss"
 
            Case Else
                'Ne rien faire, utilise le format par défaut de Excel
        End Select
 
        Set champ = Nothing
        Set Zone = Nothing
    Next iChamp
 
    prmFeuille.Application.DisplayAlerts = True
    '--- Formater les colonnes de données
 
    prmFeuille.Application.calculation = xlCalculationAutomatic 'Rétabli le recalcul de Excel
 
End Sub

Suppose que la source porte le même nom que la table (ce qui simplifie grandement la maintenance).

Au préalable il faut s'être assuré qu'il n'y a plus de filtre appliqué sur la table Excel sinon l'effacement ne se fait pas correctement et les lignes cachées restent.
Ici du code qui fait cela :

Code :

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

1
2
3
4
5
        '=== Supprime les sélections dans les filtres
        If Feuille.ListObjects.Count <> 0 And Feuille.AutoFilterMode Then
            Feuille.ShowAllData
        End If
        '---  Supprime les sélections dans les filtres

Et si on veut à nouveau voir les filtres :

Code :

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

1
2
            tableExcel.showAutoFilter = False 'Supprime les filtres sur le tableau de données
            tableExcel.showAutoFilter = True 'Recrée les filtres sur le tableau de données

A+

[Invité]

Bonsoir marot_r,

Merci pour tes explications qui confirme ce dont mon expérience lavai déjà mis en garde.

Je n'en suis vraiment pas ravis mais c'est le jeux!

Je travail d'habitude exclusivement zn Sql serveur , mais il m'arrive de développer quelques macros Excel plus transportable aux utilisateurs, dans ces cas là,j'utilise un base Access lier à Sql server.

Quand il s'agit de qualifier des données c'est dans access seule que je travail.

Faire de procédure stocker et tout le toutime pour un travail de coin de table!!!

Encore merci pour ton soutient.