Interroger une base MySql depuis Excel (VBA) - 2/2

Suite de l'article sur le couplage Excel<=> Base MySql =>

•  http://tatiak.canalblog.com/archives/2014/04/06/29605283.html

Et pour répondre à "Dorian" 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 :

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 :

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 :

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 :

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

Si cet article vous est utile, laissez moi un commentaire!
Pierre

Comments

[LaventureEstiven]

Donc je dois racheter Microsoft 360 ? Parce que j'ai désinstaller Microsoft office et Excel est toujours là

[LaventureEstiven]

Comment on fait pour installer excel 32 bit sur un ordi 64 bit ?

[LaventureEstiven]

Salut Takiak, j'ai un ordi 64 bit donc surement un xl 64, mais je ne vois pas comment utiliser le dsn proposer lorsque que je l'emploie un msg d'erreur d'incompatibilité s'affiche. Est ce à cause de la blibiothèque vba ou autre chose ?

Comment se co et manipuler une base de donnée sql depuis xl ?

[kiroule]

Merci Tatiak :)

[kiroul]

En fait, j'ai consulter des dizaines de sites qui décrivent cette fonctions. Parfois, une solution est proposée mais à chaque fois, il y a une réponse affirmant que ça ne fonctionne pas chez eux :( C'est râlant, une appli *.php fait ca très bien. Peut-être peut-on demander à VBA de réaliser une requête http avec un GET qui transmets les données.