[RESOLU] Requêtage SQL dans feuille Excel

Orodreth

XLDnaute Impliqué
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
 

Pièces jointes

  • DB_Personne_Ville.xls
    20 KB · Affichages: 85
Dernière édition:

chris

XLDnaute Barbatruc
Re : Requêtage SQL dans feuille Excel

Bonjour

En Excel pur qui plus est 2003, je ne pense pas que ce soit possible sauf peut-être en VBA en utilisant ADO...

Confrontée à ce type de problème j'avais utilisé une fois une solution assez capilotractée consistant à lier les onglets en tant que tables dans Access, à y construire la vue, et à l'exploiter dans une autre onglet du même classeur.
 

Orodreth

XLDnaute Impliqué
Re : Requêtage SQL dans feuille Excel

Re le fil, bonjour MJ13 et chris

Merci pour vos réponses.

Je viens de regarder du côté des filtres élaborés (je connais pas).
Le principe est sympa, mais j'en vois trop vite les limites pour ce dont j'ai besoin (nécessiter de copier dans la feuille active, donc dans la même feuille que les données sources, impossible de faire du multicritères sur plusieurs onglets, etc etc).
Je peux me tromper bien sûr, je n'ai que brièvement testé l'interface des filtres élaborés sous Excel.

Du coup, j'en reviens à mon idée première: SQL.

Je vais continuer à fouiller, si je trouve un truc, je reviens en parler.

Encore merci,
Cordialement,
Orodreth

(PS: je veux mon SQL Server ... :( :( :( )
 

Orodreth

XLDnaute Impliqué
Re : Requêtage SQL dans feuille Excel

Re,

Un truc que je pige pas. Sur la page FAQ d'Excel de developpez.com, on peut voir ça:
UPDATE [Feuil1] SET [Feuil1].[Nom# Champ] = .....
FAQ MS-Excel

Dans un des posts du forum consacré à Excel sur developpez.com, on trouve ça:
SELECT * FROM [Feuil1$];
Requête SQL pour récupérer un tableau Excel - Forum des professionnels en informatique

Ils les font comment ce genre de syntaxe ?
Et surtout, ils les font où ?

Merci d'avance,
Cordialement,
Orodreth
 

tototiti2008

XLDnaute Barbatruc
Re : Requêtage SQL dans feuille Excel

Bonjour Orodreth, Bonjour Michel, Bonjour Chris, :)

Ils les font comment ce genre de syntaxe ?
Et surtout, ils les font où ?

Ils les font en ADO comme te disais Chris, connexion en VBA et injection du SQL
Il semblerait cependant que ADO sur un même classeur entraine des lenteurs, donc pas vraiment conseillé

Sinon, tu peux nous donner le genre de SQL que tu veux faire, pour voir si on peut le faire en filtre élaboré ?
C'est plus puissant que tu n'as l'air de le croire

je veux mon SQL Server

Excel n'est toujours pas une base de données, même si depuis 2013 ils semblent permettre de faire des relations entre les tableaux
Pas encore 2013 chez moi, pour l'instant
 

chris

XLDnaute Barbatruc
Re : Requêtage SQL dans feuille Excel

Bonjour

...
Excel n'est toujours pas une base de données, même si depuis 2013 ils semblent permettre de faire des relations entre les tableaux
Pas encore 2013 chez moi, pour l'instant

T'affole pas sur 2013 car à part la version pro plus on ne peut pas faire grand chose vu l'intégration hyper limitée de Powerpivot dans toutes les autres versions.
 

Orodreth

XLDnaute Impliqué
Re : Requêtage SQL dans feuille Excel

Re le fil, salut tototiti2008,

Non, je sais qu'Excel n'est pas une base de données, même si on peut faire du SQL avec ADO justement.
Sauf que sur un même classeur, je n'y arrive pas, et de toutes façons, j'ai pas mes codes sur moi sur le sujet.
Je les récupère ce soir et je verrai.

Le truc étant que ... je n'ai pas le choix.
Il y a un mois et 1 semaine, j'ai averti l'entreprise qui venait de m'embaucher qu'ils allaient au devant de très fortes contraintes techniques (mono-utilisateur, limité en nombre d'enregistrements et de champs - 65536/256, classeur très lourd donc lent, etc etc ...), surtout vu leur problématique (multi-utilisateurs, données à stocker de manière stable, etc etc ...).

Je leur ai parlé de SGBDRs (il y en a des gratuits), mais ils ont dit non "Il faut qu'on puisse faire la maintenance nous-même quand tu seras parti" ... Euh ... moi je veux bien, mais ...

(D'où le "Je veux mon SQL Server", si je l'avais sous la main, et l'autorisation de m'en servir, le projet serait DEJA fini ...)

Bref ... Je dois faire avec Excel.
Je n'ai absolument rien contre au niveau applicatif, bien au contraire. Je me débrouille plutôt pas mal.
Au niveau stockage de données par contre ... surtout quand on essaye d'appliquer un modèle relationnel ... Ca foireaute un peu.

Après, j'ai conscience de royalement sous-estimé les filtres élaborés.

Pour répondre à ta question tototiti2008, je joins le modèle conceptuel que j'avais établi quand il m'avait détaillé leur problématique (pour info, de ce que j'en ai discuté avec le système informatique national, mon modèle est bon).
Egalement en fichier joint, un classeur Excel qui reprend la structure que je mets en place au niveau du stockage des données.

Mon objectif est qu'à partir de cette structure, dans laquelle sont stockées mes données, je puisse travailler sur une autre feuille (nommée "Travail" par exemple), qui reprend sous forme de tableaux des informations de plusieurs des tables du fichier en question.


Pour des requêtes SQL précises, je n'en ai pas encore en tête, mais je pense que je saurai m'adapter si on me montre pour les filtres élaborés.

Dernier point: les filtres élaborés, dans l'absolu, je n'ai rien contre, mais ça doit être transparent pour l'utilisateur.
Donc je vais aussi devoir monter un moteur de gestion des critères des filtres.

Là, je crois que j'ai à peu près fait le tour.

Encore merci à vous,
Cordialement,
Orodreth
 

Pièces jointes

  • Gest2Plann_MCD.pdf
    218.2 KB · Affichages: 75
  • DB_Metier.xls
    142.5 KB · Affichages: 67

_Thierry

XLDnaute Barbatruc
Repose en paix
Re : Requêtage SQL dans feuille Excel

Re le fil, salut tototiti2008,

.../...même si on peut faire du SQL avec ADO justement.../...
.../...non "Il faut qu'on puisse faire la maintenance nous-même quand tu seras parti" ...
Orodreth

Bonjour Chris, TotoTiti, Orodreth, the Forum

Humm juste un point par rapport à ceci dans ton Post, si tu leur colles du ADO (ActiveDataObject) dans du VBA, ce n'est pas vraiment remplir le cahier des charges pour eux au niveau maintenance... Je ne connais pas grand monde non-IT qui se dépatouille en ADO en plus du VBA...

Mais bon, c'est juste une remarque. J'en profite pour donner mon bonjour à Chris et TotoTiti en esperant que vous allez bien.
@+Thierry
 

tototiti2008

XLDnaute Barbatruc
Re : Requêtage SQL dans feuille Excel

Salut Thierry, :)
Re,

D'accord avec Thierry
Bon, en effet, là le filtre élaboré ne peut rien pour toi, dès qu'il entend le mot "relation" il s'en va, j'avais crû que c'était des critères complexes, mais sur une seule table
Et Access, il n'ont pas non plus ? Mois puissant que SQL Server mais tu peux taper du SQL, et c'est multi-utilisateur (entre 10 et 50 accès concurrents je dirais)
Franchement, si tu leur reconstruit Access dans Excel, côté maintenance ça va être coton aussi, j'imagine des macros qui écrivent des fonction RECHERCHEV, un vrai bonheur ;)
Et comment tu vas gérér le côté multi-utilisateur dans Excel ?
Il y a bien le partage de classeur mais c'est très incompatible avec les macros

Edit : pour info, les accès doivent être en lecture ou en lecture-écriture sur les "tables" ?
 
Dernière édition:

mromain

XLDnaute Barbatruc
Re : Requêtage SQL dans feuille Excel

Orodreth, MJ13, chris, Tototiti, _Thierry, le forum,


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).
En quoi est-ce gênant d'avoir une connection externe dans ton cas ?
Est-ce juste le fait que le fichier sur lequel tu te connectes est écrit en dur dans la chaine de connection ?

Si c'est le cas, il est envisageable de mettre à jour la/les chaines de connections (en VBA) à l'ouverture du classeur.

A+
Miki
 
Dernière édition:

Orodreth

XLDnaute Impliqué
Re : Requêtage SQL dans feuille Excel

Re le fil, le forum, bonjour _Thierry, mromain,

Pour reprendre dans l'ordre: non, Access ne permet pas vraiment du multi-utilisateur (testé et approuvé en BTS).
Qui plus est, ils ne sont pas fan de l'idée à cause des licences à acheter.
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é).
M'enfin, c'est pas le propos.

J'entends bien que partir sur une solution ADO complexifie les besoins, et qu'à ce niveau là, c'est moins maintenable.
A ceci près que j'applique un modèle relationnel, celui-ci étant expliqué dans une documentation.
Donc si je mets en place un mini-moteur de requête qui fonctionne sans bug, le but étant qu'ils aient un minimum de maintenance à faire pour faire évoluer ce dont ils ont besoin, et donc ne touchent pas aux moteurs sous-jacents (l'aspect système de l'application en gros, ils font ce qu'ils veulent de l'aspect métier mais pour le reste ...)

Pour répondre à mromain, non, le fait d'avoir une chaine en dur ne me dérange pas. D'autant que ce n'est guère compliqué à rendre dynamique.
C'est plus le principe de passer par une connexion externe pour revenir en interne.

Dans l'idée, c'est comme si je disais à ma soeur (2 pcs connectés en wifi local chez moi), de se connecter à un proxy sur le net pour revenir dans le wifi interne de la maison pour récupérer des fichiers sur mon pc.
Ca peut marcher ... mais ça allonge sacrément le chemin.

Après, ce qui m'intéresse dans l'aspect modèle relationnel, c'est que ça fiabilise le système et les données puisqu'elles sont correctement structurées. C'est plus facile de les comprendre.

Pour en revenir au moteur de DB ... Je peux faire sans, reprendre le principe qu'ils avaient avant, et optimiser le code.
Ceci dit, j'y perds un peu les notions de filtres qu'on m'a demandé de mettre en place.
Solution que je choisirais donc en dernier recours pour tenir mes délais si je n'ai pas avancé sur la question demain midi.

Merci à tous pour vos remarques et conseils, les pistes indiquées, je vais fouiller tout ça (et je fais un retour si je trouve une solution viable).

Encore merci,
Cordialement,
Orodreth
 

Discussions similaires