Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
En fait j’ai un gros soucis. J’ai ce devoir où on me demande de coupler un SGBD avec Excel. Les données doivent transiter de la base de données vers Excel et vice versa. Le tout devra être possible grâce aux formulaires créés en VBA.
Je ne sais même par où commencer. Merci d’avance
Bonsoir
Tout d’abord je tiens à te remercier pour cette développement très précis. Mais j’ai une question si je me trompe pas ça c’est pour enregistrer les données de la feuille vers la base de données mais maintenant comment faire pour recharger les données depuis la base de données ?
Je suis entrain de travailler sur une base de donnée et je connecte excel avec access :
- Je réalise dans access des requête et ces même requête sont générer en langage SQL (avec cette application access)
Je réutilise l'écriture SQL que je dois adapter au VBA.
Pour la connection j'utilise le mode ADO bien plus puissant.
Dim cnx As new ADODB.Connection
Set cmd = New ADODB.Command
Dim mRst As New ADODB.Recordset
en fait j'utilise la base de données access (mais le mode ADO est compatible toutes base)
donc je connecte a la base access (cnx)
je créer la commande (cmd)
il me faut donc la requête que je réalise dans access pour générer la phrase sql)
cette même phrase est ensuite envoyer a la commande (cmd).
qui l’exécute en VBA (Mémoire) via le Recordset (mrst)
Pour simplifié je fait une copie du recordset dans une variable tableau (que je transpose pour l'avoir sous
le format excel en tableau 2D)
ce que j'aimerais essayer de faire c'est de générer une requête sans passé par access et les écrires directement
pour cela j'ai l'idée d'utilisé des expression régulière des REGEX qui sont aussi très puissante.
C'est un peux complexe mais c'est vraiment super intéressant
J'ai pas le code sous la main mais je vais ajouter à se poste dans que je peux cela m'interresse.
A la réflexion pourquoi attendre, voici quelques notions à connaitre pour rédiger les requêtes au "format VBA".
Pour l'exemple, imaginons une base simple avec une seule table et les quelques champs suivants:
* id : nombre entier
* nom du client : texte
* date de visite : date
* quote part : nombre réel
1/ Les guillemets :
Pour une requête classique, les noms des champs doivent être entourés de guillemets-du-7 (Alt-Gr 7 2fois puis backspace) soit par exemple pour un Select :
en Sql => SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable
en VBA => requete = "SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable"
(avec Dim requete As string)
Pour ajouter une clause WHERE avec une variable var, c'est selon le type de champs :
* champs numérique :
en Sql => SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable WHERE `Id` = var
en VBA => requete = "SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable WHERE `Id` = " & var
(avec Dim var As Integer)
* champs texte :
en Sql => SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable WHERE `nom du client` = var
en VBA => requete = "SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable WHERE `nom du client` = " & "'" & var & "'"
(avec Dim var As String) ici la valeur texte var est entourée de guillemets-simples-du-4
* Cas particulier de la variable contenant elle même un guillemets-simples-du-4 (ou apostrophe) exemple (du navigateur) var="Armel Le Cleac'h" :
Dans ce cas il sera nécessaire de doubler ce guillemets-simples-du-4 soit var="Armel Le Cleac''h" (2 guillemets-du-4, on parle ici de caractère d'échappement)
Pour systématiser le traitement, autant 'échapper' à la volée les valeurs textes avec une fonction simple (et qui ajoute aussi des ' avant et après):
exemple :
VB:
Function Esc(S As String) As String
Esc = "'" & Replace(S, "'", "''") & "'"
End Function
ce qui conduit à écrire la requête :
en VBA => requete = "SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable WHERE `nom du client` = " & Esc(var)
Ok, donc pas de guillemet pour les valeurs numériques, c'est cool! ... oui, mais ...
2/ Les valeurs numériques (non entières) :
Le format français courant de la marque de décimale est une virgule par exemple `quote part`= 0,25
Mais pour le MySql cette valeur est stockée avec un point `quote part`= 0.25
Il est donc nécessaire de modifier à la volée la virgule par un point, par exemple avec une autre fonction simple :
VB:
Function Esc_num(S As String) As String
If S = "" Then S = "0"
Esc_num = Replace(S, ",", ".")
End Function
Un exemple d'appel (var étant par exemple var=sheets("Feuil1").range("A1").text) :
en VBA => requete = "SELECT `Id`, `nom du client`, `date de visite`, `quote part` FROM LaTable WHERE `quote part` = " & Esc_num(var)
3/ Les valeurs dates :
MySql stocke les valeurs date au format Iso, soit 2019-06-30 pour le 30 juin 2019
Ici encore, il est donc nécéssaire de reformater la chaine 30/06/2019 par une tite fonction simplette :
VB:
Function datefr_vers_iso(datefr As String) As String
Dim Dt As Variant
Dt = Split(datefr, "/")
datefr_vers_iso = "'" & Dt(2) & "-" & Dt(1) & "-" & Dt(0) & "'"
End Function
Un exemple d'appel (var étant par exemple var=sheets("Feuil1").range("A1").text contenant une date) :
en VBA => requete = "UPDATE LaTable SET `date de visite` = " & datefr_vers_iso(var) & " WHERE `Id` =" & 2
(Ce qui veux dire qu'à la lecture, il faudra une fonction inverse : Function iso_vers_datefr)
Pour finir, un p'tit rappel de quelques conventions d'usage :
* les mot clé du Sql sont à écrire en majuscule SELECT FROM WHERE DISTINCT, ...
* utiliser plutôt plusieurs lignes (pour la lisibilité), exemple :
VB:
Requete = "SELECT `P.id`, `P.Date`, `P.Titre`, `P.Nom`, `P.Prenom` , `R.Recette` FROM Publics AS P " & _
" INNER JOIN Ecritures AS R ON `P.Id`=`R.Id`" & _
" WHERE LEFT(`P.Nom`,1)='" & Critere & "' " & _
" ORDER BY `P.Titre` ASC, `P.Nom` ASC"
/!\ ATTENTION : dans cette dernière requête la fonction LEFT est celle du SQL et non celle du VBA!!
(=> toutes les fonctions du VBA ne sont pas reconnues par le Sql, se référer à sql.sh)
Voilà, avec ça on peut commencer à rédiger sérieusement ses requêtes.
Pierre
Bonjour,
J imagine que l'utilisation des regex se fera après récupération du recordset.
Si c'est pour extraire des chaînes de caractères c'est top.
Si c'est pour le faire directement dans la clause where il y a regexpmatch mais ça risques de pas fonctionner.
Après le like reste assez puissant
VB:
SELECT t.Field1
FROM ATable t
WHERE t.Field1 Like "SM[0-9][0-9][_][0-9][0-9][0-9][a-z]?"
Could the WHERE clause in a SQL statement be evaluated as a regex string defined by the Pattern property of a VBScript RegExp object in an Access 2007 VBA module, where the SQL statement is the Source
Comme promis j'ai fait le code qui permet de faire une connexion depuis Excel à la base de données Access.
La base Access (Très simple) = Exemple Acces.mdb
Le fichier excel avec les macros (Complexe) = Transert Excel Via Acces.xlsm
Nota pour la Macro VBA activé dans les options
Activé les librairies : dans le module option EXCEL
microsoft activex data objects 6.1 library
microsoft ADO ext 6.0 for ddl and security
Changer le chemin ou sera ranger le fichier access !
POUR INFO : Modifier le chemin ou sera ranger le fichier dans votre Ordinateur (Fichier Exemple Acces.mdb)
' Etablir la connexion à la source de données !
m_Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=C:\Users\Arborescence\Nom du dossier\Exemple Acces.mdb;"
Le principe est le suivant : Détail des variables de la classe ci-dessous
J’ai créé un module de classe qui sera le point d’entrée de la récupération des informations depuis la base de données (Pour le fait se sera une base Access)
Ps : J’ai commenté tous le module de classe du mieux que j’ai pu est je réponds à toutes questions.
Donc cette classe contients
Que des Variables de type privés
Une fonction interne à la classe Initialisation (Class_Initialize) et destruction (Class_Terminate)
Une Property Get qui va lire le résultat de la requête stocké dans un tableau, pour le renvoyer dans le Module standard (Nom = TransfertTabConvVersExcel)
Ce qu’il faut savoir c’est que ce tableau a était transposé par une fonction pour que les données soit exportable sous le bon format Excel.
Ne pas oublier que le résultat de la requête Access sera stocké sous forme de tableau différent et que les positions du tableau commencent à la ligne 0 et colonne 0
Une Property Let qui va écrire le résultat de la requête dans la variable tableau (Nom RequeteRs) :
Deux variables une qui est Boolean (Vrais ou Faux)
Et l’autre le résultat de la requête
Je m’explique :
Par default si on veut récupérer une table Access sans définir de critères pour cette requête (ont récupère une table prédéfinis au paravent ce qui sera par défaut = aucune Requête à écrire et a renseigner) se sera toujours le même résultat qui sera renvoyé et donc figé (dans le code VBA du module de classe et se sera au programmeur de le substitué pour un autre résultat.
Donc l’envoi sera de type
Première variable = False
Deuxième Variable = vide
Dans le cas contraire nous avons une requête qui sera identifié (pour un résultat) à obtenir et donc la il y aura création de la requête SQL et donc
Première variable = True
Deuxième Variable = (il faut écrire la requête SQL)
Suite à cela le module de classe se débrouille tous seul
Il récupère le resultat de la requête
Stock se résultat dans une variable tableau (au format access)
Il faut donc transposer le resultat (j’ai choisi une fonction car la fonction transpose est limité à 65 000 lignes)
Donc la variable tableau est envoyé a cette fonction qui transpose le résultat dans la variable tableau
Et maintenant c’est bon le résultat est stocké dans la variable tableau.
La fameuse Fonction qui transpose : Private Function m_TransposeTabDim
NB /
Il est important de bien ouvrir c’est connexion (J’utilise donc Class_Initialize)
Puis pour être sûr de refermer proprement la base Access à la fin de la procédure (j’utilise Class_Terminate)
Pour ce dernier et pour info
Quand j’ai plus besoin d’utilisé cette classe (je décharge la variable de classe avec Nothing depuis le module standard)
Cette action fait en sorte de rentrer par défaut dans le module de classe qui est toujours Class_Terminate
Et chaque élément sera décharger proprement (Voir mon exemple)
Pas toujours simple à comprendre alors voila
Dans le module STANDAR je crée une variable de la classe (Les variable sont ci-dessous)
' Variable Module de classe.
Public ResExcelViaAccess As LiaisonAccesVsExcel
' Initialisation de la Variable Module de classe.
Set ResExcelViaAccess = New LiaisonAccesVsExcel
Maintenant j’utilise cette classe via cette variable
Quand j’ai plus besoins des éléments stocké dans le module de classe je décharge cette variable par : (a écrire dans le module STANDARD cela)
' Decharge la variable
Set ResExcelViaAccess = Nothing
Ce qui envoie directement dans le module de classe à l’endroit (Class_Terminate) pour effectuer les instructions dans ce module.
Le Module STANDARD qui va envoyer les informations dans le module de Classe ou en seront stocké les informations :
Voici comment est composé se module STANDARD.
Deux variables
TxtSQL (Le texte = La requête SQL) / Donc : Dim TxtSQL As String
Choix (Vrais ou Faux) / Donc : Dim Choix As Boolean
Pour le choix OUI = Nous savons la requête SQL à établir pour le résultat à obtenir et donc :
Ecriture de cette requête SQL (Copier le résultat et la coller dans la boite imputbox)
Action qui se fera seule avec ce choix Oui dans le module standard (Pour envois dans le module de classe) de la fonction d’écriture :
Property Let RequeteRs(ByVal test As Boolean, ByVal TxtSQL As String)
' Variable Module de classe
Choix = True
ResExcelViaAccess.RequeteRs(Choix) = TxtSQL
Car il faut bien renseigner les deux variables :
Test (qui sera = à Vrais)
TxtSQL = qui sera le texte de la requête SQL
Pour le choix NON = Nous savons la requête SQL à établir pour le résultat à obtenir et donc :
Il n’y aura aucune requête SQL à écrire car elle sera déjà écrite est stocké dans le module de classe
Le choix est par défaut à faux
Donc la variable Choix est = à False (Faux) par défaut
Pour le choix ANNULER = Il n’y a aucune action à établir et donc une sortie de la procédure.
Ps : Excel n’est déjà pas très simple et donc cumulé avec Access un peux plus… si avec un module de classe cela se complique d’avantage, j’aurais fait en sorte d’expliquer ce code est procédure pour que cela servent au plus grand nombre.
J’ai détaillé tous le code VBA comme j’ai pu le faire (c’est indications complémentaire pourrons aider)
La suite que j’ai déjà développé (les expressions régulières) :
Je cherche à les intégrer pour en faire des requêtes et générer des requêtes SQL
Je m’en sers aussi pour nettoyer le texte pour des recherches :
Exemple : dans un long texte (Majuscule, Minuscule, accents) je passe tous en minuscule sans accents (pour cette longue phrase) et pour la recherche dans cette phrase (le mot saisie) sera aussi transformer (ce qui donnera plus de résultat et plus s’occuper des tous un tas de cas particulier que l’on aura plus a gérer par la suite)
Ps : Je n'ai pas la possibilité de posté le fichier access : Exemple Acces.mdb / C'est non autorisé par l'administrateur
J'espères avoir documenté un maximum. Pour reproduire le fichier Access qui n'a qu'une seule table, j'ai joint l'image a Default du fichier
non autorisé (très simple et rapide a créer)
J'ai envie de poursuivre avec vous sur le sujet :
- Mon idée est de créer des requêtes personnalisé et de les construire avec des variables en utilisant (des tableaux et REGEX)
exemple ici sur la table ont recherche uniquement 'Dupont'
Mais ont Pourrais aussi demander 'DURAND' et 'MARINA' en plus
ce qui ferait que la construction se ferais en détectant les 3 Variables et créer la Requête à décroître avec les REGEX
Re :
Le détail de mon code VBA : Liaison Excel Base de données Access
' ***************************************************************************************************
' Le Module Standard : Cnn_ExcelVsAccess
VB:
Option Explicit
' Variable Module de classe.
Public ResExcelViaAccess As LiaisonAccesVsExcel
Sub ConnexionForExtrationVersExcel()
' Suuprime la précedente sélection
Range(Cells(1, 1), Cells(8, 7)).Clear
Dim TxtSQL As String
' Par default le choix est faut (C'est a dire qu'il n'y a pas de requête pré établie et que l'ensemble de la table Acces sera renvoyer vers Excel)
Dim Choix As Boolean ' Choix est False par défaut (C'est a dire faut)
' Initialisation de la Variable Module de classe.
Set ResExcelViaAccess = New LiaisonAccesVsExcel
' Récupére le resultat dans une variable Tableau stocké dans acces !
' Envoie de la requête pour extraire les données dans une variable tableau.
' Nb : Le résultat de la requête sera stocké dans une variable tableau au format Acces :
' - Il serat nécéssaire de transposé le resultat obtenu pour qu'il soit compatible avec excel
' - Se serat pris en compte dans la fonction (inclus dans le module de classe par default).
' Deux choix : (Faut par défault)
' - Premier choix = Faut (Aucune Requête SQL à envoyer : Le résultat prendra l'esemble de la table Acces renvera tous le resutlat dans Excel)
' - Ou
' - Deuxiéme choix = Vrai ( Le résultat de la requête sera conforme à la requête SQL envoyer pour obtenir le resultat escompté dans Excel)
' - Il faut créer écrire une requête à renvoyer dans le module de classe et Passer la variable Choix a vrai (Choix = True)
' Choix avec MSGBOX pour stocké le Numéro qui correspond a Oui / Non ou Annuler (Cf ci-dessous).
' Oui = 6
' Non = 7
' Annuler = 2
Dim Rep As Integer
Rep = MsgBox("Oui : Choix de la Requête SQL à écrire pour le resultat Escompté" & vbCrLf _
& "Non : Resultat par default Table compléte Acces vers Excel" & vbCrLf _
& "Annuler : Sortie de procédure", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Extration de la table Acces Vers Excel en fonction du choix")
If Rep = 6 Then
' Exemple :
'TxtSQL = "SELECT Client.Nom_client, Client.Ville, Client.salaire, Client.DateNaiss, Client.CodePostal, Client.N°, Client.Commentaire" & _
"FROM Client " & _
"WHERE (((Client.Nom_client)='dupont'));"
'TxtSQL = "SELECT Client.Nom_client, Client.Ville, Client.salaire, Client.DateNaiss, Client.CodePostal, Client.N°, Client.Commentaire FROM Client WHERE (((Client.Nom_client)='dupont'));"
TxtSQL = InputBox("Ecrire la requête SQL à l'emplacement reservé" & vbCrLf & "Suite au choix = Oui", "Ecrire la requête SQL au Format VBA")
' Variable Module de classe
Choix = True
'MsgBox TxtSQL
ResExcelViaAccess.RequeteRs(Choix) = TxtSQL
ElseIf Rep = 7 Then
MsgBox "Extraction de toutes la table Acces Vers Excel"
' Variable Module de classe
ResExcelViaAccess.RequeteRs(Choix) = ""
ElseIf Rep = 2 Then
' Decharge la variable de classe
Set ResExcelViaAccess = Nothing
' Sortie de Procédure.
Exit Sub
' 307 = annunulé
'MsgBox("", vbYesNoCancel + vbExclamation + vbDefaultButton2, "") = vbNo
End If
' Colle le resultat de la requête vers la feuille excel :
' Choix de la cellule : Pour exemple en cellule A1
' Le resultat sera copier sous fomre de tableau = Puis tous le tableau obtenu
' Module de classe
Worksheets("Resultat de la reqête").Cells(1, 1).Resize(UBound(ResExcelViaAccess.TransfertTabConvVersExcel, 1) + 1, UBound(ResExcelViaAccess.TransfertTabConvVersExcel, 2) + 1) = ResExcelViaAccess.TransfertTabConvVersExcel
' Destruction de la classe
'ResExcelViaAccess.Class_Terminate
' Decharge la variable
Set ResExcelViaAccess = Nothing
End Sub
' ***************************************************************************************************
' Le Module de classe : LiaisonAccesVsExcel
VB:
Option Explicit
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' Connection Base Prix TestPrixU.mdb "Acces"
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' Etape 1 : Etablir une connexion à une base de données.
' ****************************************************************
Private m_Cnn As New ADODB.Connection
' Etape 2 : Créer une commande permettant d'envoyer une requête SQL
Private m_Cmd As New ADODB.Command
' Etape 3 : Créer l'objet qui va permettre d'exploité le Recordset.
' ****************************************************************
Private m_Rs As New ADODB.Recordset
' ****************************************************************
' Stock la requête SQL dans une variable primaire.
Private m_TxtSQL As String
' ****************************************************************
' Tableaux (Stock le résultat de la requête dans une variable tableau)
' ****************************************************************
Private m_TabConv() As Variant
' Lecture du resultat de la vaiable tableau à renvoyer dans le moduloe standard pour resultat vers excel
Property Get TransfertTabConvVersExcel() As Variant(): TransfertTabConvVersExcel = m_TabConv: End Property
Private Sub Class_Initialize()
' Etablir la connexion à la source de données !
m_Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=C:\Users\Remi\Non du dossier\Exemple Acces.mdb;"
' Créer la commande :
' Connexion.
Set m_Cmd.ActiveConnection = m_Cnn
' ****************************************************************
End Sub
Property Let RequeteRs(ByVal test As Boolean, ByRef TxtSQL As String)
' choix de l'envoie dans la commande pour la requete !
m_Cmd.CommandText = adCmdText
' si test = fault (donc variable initialisé à false) : Le resultat de la requête copie toute la table
' Test si fault (Toute la table) si vrais choix de la requête précedement écrite !
If test = False Then
m_Cmd.CommandText = "SELECT * FROM Client;"
' Debug.Print m_TxtSQL
' ***********************************************************************************
Else
m_TxtSQL = Replace(TxtSQL, """", "")
Debug.Print m_TxtSQL
m_Cmd.CommandText = m_TxtSQL
End If
' Exécute la commande.
m_Rs.CursorLocation = adUseClient
m_Rs.Open m_Cmd, , adOpenKeyset, adLockOptimistic
' Transfert de la requête dans la variable tableau.
m_TabConv = m_Rs.GetRows(m_Rs.Clone.RecordCount)
' Fonction qui transpose le resultat de la requête acces au format Acces.
m_TabConv = m_TransposeTabDim(m_TabConv)
'Erase m_Tab
'm_Tab = m_TabConv
' Ferme la connexion à la requête.
m_Rs.Close
' Décharge la variable
m_TxtSQL = Empty
End Property
Private Function m_TransposeTabDim(m_TabConv() As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
Dim X As Long
Dim Y As Long
Dim Xupper As Long ' Nombre de lignes dans le tableau
Dim Yupper As Long ' Nombre de Colonnes dans le tableau
Dim tempArray As Variant ' Tableau temporaire pour le transfert
Xupper = UBound(m_TabConv, 2)
Yupper = UBound(m_TabConv, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = m_TabConv(Y, X)
Next Y
Next X
' Renvoie le tableau transposé
Erase m_TabConv
m_TransposeTabDim = tempArray
Erase tempArray
End Function
Private Sub Class_Terminate()
' Site explication : http://www.translatetheweb.com/?from=&to=fr&dl=fr&a=http%3A%2F%2Fwww.jsonforoffice.com%2Ftutorial%2Fvba-class-how-to-destroy%2F
' Fermeture de la connection ADO
'-------------------------------
' Etape 1 : Etablir une connexion à une base de données.
m_Cnn.Close
Set m_Cnn = Nothing
' Etape 2 : Créer une commande permettant d'envoyer une requête SQL
Set m_Cmd = Nothing
' Etape 3 : Créer l'obje qui va permettre d'exploité le Recordset.
Set m_Rs = Nothing
MsgBox "Confirmation : Les connexions à la base acces sont fermé !"
'
End Sub
Ps : C'est bien sur un exemple, mais l'essentiel est présent.
Je peux agrémenté avec des variables typé
des REGEX
il n'y a pas trop de limite de construction
il faut juste une base Access (Modèle avec des données écoles fictives) et créer de modèle de requêtes pour récupérer, stocké et récupérer comme ont veux dans le module Standard.
Au Plaisir de développer cela ensemble et de partager des idées
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
