[RESOLU] Requêtage SQL dans feuille Excel

[Orodreth]

Bonjour le forum,

Je connais le principe des SQL.REQUEST, MS.QUERY, Données => Données Externes => Requêtes (etc ...) et autres, j'en ai déjà fait, pas de soucis.
Très bien quand il s'agit d'interroger un ersatz de base de données stockée dans un classeur excel autre que le classeur applicatif.

Une petite question cependant, quand il s'agit d'interroger une table stockée dans une feuille du classeur applicatif, faut-il également passer par Données => Données externes, ou existe-t-il une méthode pour travailler en interne ?

Je m'explique, j'ai, pour l'exemple, une feuille de données nommée "Personne" (nom VBA = sh_Personne), dans laquelle je référence un ID, un NOM, un PRENOM, un AGE, un SEXE (H/F), et une foreign key ID_VILLE (pour faire le lien sur la table Ville).

Une deuxième feuille, du même classeur, nommée "Ville" (nom VBA = sh_Ville) me sert à stocker les villes.

Une troisième feuille, toujours du même classeur, nommée "Resultats" (nom VBA = sh_Resultats) me sert à afficher mes résultats pour travailler dessus.

Hors, j'aimerais pouvoir filtrer ma table Personne pour ne garder que les enregistrements qui correspondent à mes critères, tout en gardant la jointure avec la table Ville (d'autres critères possibles à ce niveau).

Et pour faire ça, j'aimerais vraiment passer par SQL (le classeur fournit n'est qu'un exemple de test, pour que je m'essaie au SQL sur Excel, mon projet actuel est beaucoup beaucoup plus gros, mais le principe après reste identique).

En exemple de requête SQL, je pourrais dire quelque chose comme ça:
"SELECT
Personne.ID AS ID_P, Personne.Nom, Personne.Prenom, Personne.Age, Personne.Sexe, Personne.FK_ID_VILLE,
Ville.ID AS ID_V, Ville.Ville, Ville.CP
FROM
Personne
INNER JOIN Ville ON Personne.FK_ID_VILLE = Ville.ID
WHERE
Personne.Nom LIKE "*VI*"
AND Ville.Ville LIKE "*a*"
"
Ou, si la méthode INNER JOIN n'est pas faisable, à l'ancienne:
"SELECT
Personne.ID AS ID_P, Personne.Nom, Personne.Prenom, Personne.Age, Personne.Sexe, Personne.FK_ID_VILLE,
Ville.ID AS ID_V, Ville.Ville, Ville.CP
FROM
Personne, Ville
WHERE
Personne.FK_ID_VILLE = Ville.ID
AND Personne.Nom LIKE "*VI*"
AND Ville.Ville LIKE "*a*"
"

Pour les deux requêtes, les résultats retournés sont les suivants:
1 - LAVIRE - Thierry - 26 - H - 1 - 1 -Bordeaux - 33000
2 - LAVIRE - Marie - 22 - F - 2 - 2 - Talence - 33400
7 - LEVIEU - Caroline - 20 - F - 2 - 2 - Talence - 33400

Mon objectif est de pouvoir interroger mes tables en SQL pour générer une "vue", une table préformatée, qui est construite à partir d'une requête SQL, et sur laquelle je viens travailler avec VBA, de sorte de n'être jamais en direct sur mes données base.
Autre intérêt des requêtes SQL: ça m'éviterait les boucles et conditions de recherches et de filtres, ce qui serait un gain de temps très appréciable.

Le problème étant qu'à part MS.QUERY, je ne sais pas comment faire du SQL sur Excel, et MS.QUERY a le défaut de passer par une connexion ODBC (qui est très bien en soi, mais qui implique une connexion externe, alors que je cherche précisément à travailler en interne).

De fait, ne sachant pas vraiment pas où commencer cet aspect SQL, je n'ai pas produit de code VBA dans ce sens.

Désolé pour le pavé,
Merci à tous/toutes qui auront lu ce texte,
Merci d'avance à ceux et celles qui répondront,
Cordialement,
Orodreth

 

[BOISGONTIER]

Re : Requêtage SQL dans feuille Excel

Bonjour,

>Pour reprendre dans l'ordre: non, Access ne permet pas vraiment du multi-utilisateur (testé et approuvé en BTS).
Sans compter que j'ai moi-même des réticences à utiliser Access depuis que j'ai mis à plat le moteur de requête (il a tourné pendant 24h sur une requête - toute simple en soi - et il a planté).

N'importe quoi! J'ai développé plusieurs applications Access multi-utilisateurs complexes. Je n'ai jamais eu de problème.

JB

 

[Orodreth]

Re : Requêtage SQL dans feuille Excel

Re le fil, bonjour BOISGONTIER

Hum ... ok, noté, merci pour l'info.

 

[Orodreth]

Re : Requêtage SQL dans feuille Excel

Re,

Je reviens aux nouvelles, en bidouillant ADO, j'ai réussi à interroger le classeur sur lequel je travaille, en générant des jointures, et en envoyant les données sur un onglet différent.

Ca résout mon problème, j'ai plus qu'à dynamiser le système, et ça roule.

Pour info, si quelqu'un se retrouve devant la même problématique que moi, le lien suivant aide beaucoup:
Lire et crire dans les classeurs Excel ferms

Dernier point, une copie de mon code, sachant que je ne travaille pas sur un classeur fermé mais sur le classeur courant actuellement ouvert:

Public Sub Test_Lecture_DB()
    Sheets("Feuil1").Range("A2:IV65536").Clear
    
    Dim Cn As ADODB.Connection
    Dim Fichier As String
    Dim texte_SQL As String
    Dim Rst As ADODB.Recordset
    
    'Définit le classeur fermé servant de base de données
    Fichier = ThisWorkbook.FullName
    
    Set Cn = New ADODB.Connection
    
    Dim str_Cnx As String
    str_Cnx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier _
    & ";Extended Properties=""Excel 8.0;HDR=YES;"""
    
    Cn.Open str_Cnx
    
    'Définit la requête.
    '/!\ Attention: ne pas oublier le symbole $ après le nom de la feuille.
	'/!\ Attention: le nom des feuilles doit être entre [ ], les jointures se font à l'aide de INNER JOIN
    texte_SQL = "SELECT ... FROM ... WHERE"
    
    Set Rst = New ADODB.Recordset
    Set Rst = Cn.Execute(texte_SQL)
    
    'Ecrit le résultat de la requête dans la cellule A1
    Sheets("Feuil1").Range("A2").CopyFromRecordset Rst
    
    'Fermeture du recordset
    Rst.Close
    Set Rst = Nothing
    
    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing

End Sub

Encore merci à tous,
Cordialement,
Orodreth

 

[_Thierry]

Re : Requêtage SQL dans feuille Excel

Bonjour le Fil

Attention Microsoft.Jet.OLEDB.4.0 devient obsolete, lui préférer Microsoft.ACE.OLEDB.12.0

Voir ce Fil https://www.excel-downloads.com/threads/vba-macro-copier-coller-plage-non-vide.41309/

Bien à vous
@+Thierry

 

[tototiti2008]

Re : Requêtage SQL dans feuille Excel

Bonjour Orodreth, Bonjour Thierry, Bonjour à tous,

Attention, j'ai déjà constaté des comportements bizarres en utilisant ADO sur le classeur ouvert :

voir par là :
[Toutes versions] Recordset ADODB dégrade les performances VBA ! - Forum des professionnels en informatique
et puis là :
BOGUE : fuite de mémoire se produit lorsque vous interrogez une feuille de calcul Excel ouverte en utilisant ADO (ActiveX Data Objects)

 

[Orodreth]

Re : [RESOLU] Requêtage SQL dans feuille Excel

Je note les gros ATTENTION, je verrai bien, et j'essayerai de régler les problèmes quand ils surviendront.

Cordialement,

 

[Guest]

Re : [RESOLU] Requêtage SQL dans feuille Excel

Bonjour Orodreth, _Thierry,, Tototiti, tous,

Ne pas oublier d'enregistrer le fichier à chaque requête. Excel travail sur un fichier temporaire mais ADO sur la version enregistrée!

[Edit] si mes souvenirs son bons

A+

 

[Orodreth]

Re : [RESOLU] Requêtage SQL dans feuille Excel

Re,

Merci pour la précision Hasco, c'est bon à savoir.

Ca me sera cependant peu utile puisque ADO ne va me servir qu'à établir des filtres avant de collationner les données sur un onglet.
A partir de là, ce qui m'attend, c'est du traitement séquentiel, donc je travaillerai en VBA sur l'onglet en question.

Mais je vais faire gaffe à ce qu'il prenne le bon fichier

Encore merci,
Cordialement,

 

[Guest]

Re : [RESOLU] Requêtage SQL dans feuille Excel

Re,

Je ne sais pas si tu peux requêter sur le fichier temporaire. C'est pour cela que pour avoir des données à jour en interrogeant le fichier, il faut l'enregistrer.

A+

 

[Orodreth]

Re : [RESOLU] Requêtage SQL dans feuille Excel

J'avais pas vu ça comme ça.

Merci pour l'info

 

[_Thierry]

Re : [RESOLU] Requêtage SQL dans feuille Excel

Je note les gros ATTENTION, je verrai bien, et j'essayerai de régler les problèmes quand ils surviendront.

Cordialement,

Bonsoir le Fil, le Forum

Si ton client travaille sur des stations équipées d'Office 2010, j'ai bien peur que "quand ils surviendront" peut être conjugué au présent avec Jet.OLEDB.4.0

Alors que le Microsoft.ACE.OLEDB.12.0 semble rétro-compatible avec Office 2003 (pas plus ancien)

Bonne soirée
@+Thierry

 

[Orodreth]

Re : [RESOLU] Requêtage SQL dans feuille Excel

Bonjour,

Pour ce point au moins, je ne me fais pas de soucis: ils sont sur office 2003.

Cordialement,
Orodreth