none
Requêtes SQL Server dans Excel 2016 avec des paramètres RRS feed

  • Question

  • Bonjour,

    Je recherche désespérément depuis des jours la méthode qui me permettrait de récupérer dans Excel 2016 des données issues d'une base de données SQL Server 2012 en utilisant dans ma clause WHERE des paramètres qui sont saisis dans une case de mon tableau Excel.

    Un dessin valant mieux qu'un long discours voici une copie d'écran de ce que je voudrais obtenir :

    Concrètement, à l'ouverture du tableau Excel, l'utilisateur verrait s'afficher automatiquement les données qui seraient extraites de la base de donnée SQL Server en tenant compte du paramètre saisi dans la case "Année".

    Je sais comment requêter dans ma base SQL Server, ça ne présente aucune difficulté particulière, je peux le faire par "Récupérer et Transformer" le nouvel onglet de la version 2016 d'Excel, ou par Microsoft Query (ancienne méthode).

    Mon problème est de rendre ma commande SQL "dynamique", c'est-à-dire que je voudrais que lorsque l'utilisateur modifie le contenu de la case "Année", le tableau des données extraites se mette à jour automatiquement.

    Ce besoin, a priori trivial, semble impossible à satisfaire.

    En fait dans mon code SQL je cherche la syntaxe à utiliser pour placer le contenu de ma case "Année" dans ma clause WHERE. J'imagine quelque chose comme :

    select Designation, Date_Ouverture from Affaire where year(Date_Ouverture) = $Année

    Et surtout sans employer de programmation genre VBA...

    Si quelqu'un a la réponse à cette question...

    Merci.

    Pascal.


    Pascal.

    mardi 13 mars 2018 17:29

Toutes les réponses

  • Bonjour,

    Je me permets de vous demander si vous avez trouvé l'astuce que vous cherchiez. Je suis confrontée au même problème que vous.

    Merci de votre retour,

    Cordialement

    lundi 6 janvier 2020 12:50
    • Marqué comme réponse PascalB41 lundi 6 janvier 2020 13:43
    lundi 6 janvier 2020 13:28
  • Bonjour,

    Après maintes recherches, je me suis résolu à écrire un bout de code en VBA pour le faire parce que tout ce que j'ai pu essayer ne marchait pas (Au passage merci à Microsoft de concevoir des outils logiciels si mal conçus...).

    Dans le principe, j'ai créé une requête classique , non paramétrable, dont je modifie la chaîne de commande SQL dans un bout de code VBA, en ajoutant le contenu de la case C2 qui contient les paramètres dans la clause Where. C'est un peu compliqué car je concatène des valeurs multiples séparées par un '+'. Cette concaténation est réalisée au début de la fonction par le calcul de la variable de type string 'Affaires'

    Ce code est livré tel quel, il est sans doute optimisable mais je n'ai pas eu le temps de me pencher sur la question, j'avais perdu assez de temps sur ce sujet a priori trivial.

    Voici le code en question

    Sub MajSQL()
    '
    ' Mise à jour de la requête SQL de la feuille
    '
        Dim CaseAffaires() As String
        Dim Affaires As String
        
        Affaires = ""
        CaseAffaires() = Split(Range("C2").Value, "+")
        For i = LBound(CaseAffaires()) To UBound(CaseAffaires())
          Affaires = Affaires & "'" & CaseAffaires(i) & "',"
        Next i
        Affaires = Mid(Affaires, 1, Len(Affaires) - 1)
        
        
        With ActiveWorkbook.Connections("Lancer la requête à partir de MABASESQL").ODBCConnection
            .BackgroundQuery = True
            
            .CommandText = "SELECT FOURNISSEUR=EC.NOM_FOURNISSEUR, CDE=EC.NOM_CDE_FOUR, REFERENCE=CC.REF, DESIGNATION=CC.DESIGNATION, ""QTE CDE""=CC.QUANTITE,"
            .CommandText = .CommandText & """QTE REC""=(SELECT ISNULL(SUM(CB.QTE),0) FROM SS_BR CB WHERE (CB.N_SCD_FOUR = CC.N_SCD_FOUR) AND (CB.N_AFFAIRE = A.N_AFFAIRE)),"
            .CommandText = .CommandText & """RAR""=CC.QUANTITE - (SELECT ISNULL(SUM(CB.QTE),0) FROM SS_BR CB WHERE (CB.N_SCD_FOUR = CC.N_SCD_FOUR) AND (CB.N_AFFAIRE = A.N_AFFAIRE)),"
            .CommandText = .CommandText & "PU=round(CC.PRIX_HT_EURO,2), ""P TOT""=round(CC.TOTAL_EURO,2), AFFAIRE=A.DESIGNATION, ""DATE CDE""=convert(varchar, EC.DATE_COM, 103),"
            .CommandText = .CommandText & """DATE LIV""=isnull(convert(varchar, CC.date1, 103),''), REPORT=isnull(convert(varchar, CC.date2, 103),''), ""BUTOIR""=ISNULL(CC.libre3,''),"
            .CommandText = .CommandText & "POSTE=isnull(CC.Libre2,'<SANS>'), RUB=CC.Rubrique, ""IMPRIME""=ISNULL(EC.Champ3,'Non') "
            .CommandText = .CommandText & "FROM CDE_FOUR EC JOIN SCD_FOUR CC ON (CC.N_CDE_FOUR = EC.N_CDE_FOUR) JOIN AFFAIRE A ON (A.N_AFFAIRE = CC.N_AFFAIRE) LEFT OUTER JOIN PRODUIT P ON (P.N_PRODUIT = CC.N_PROD) "
            .CommandText = .CommandText & "WHERE (0 < EC.PARENT) AND (ISNULL(CC.REF,'') <> '') AND (A.DESIGNATION in (" & Affaires & ")) "
            .CommandText = .CommandText & "UNION "
            
            .CommandText = .CommandText & "SELECT FOURNISSEUR='STOCK', CDE='STOCK', REFERENCE=P.REF_CONSTRUCTEUR, DESIGNATION=P.NOM_PRODUIT, ""QTE CDE""=-MS.QTE_STOCK, ""QTE REC""=-MS.QTE_STOCK, ""RAR""=0,"
            .CommandText = .CommandText & "PU=round(ED.PU_Euro,2), ""P TOT""=round(-MS.QTE_STOCK * ED.PU_Euro,2), AFFAIRE=A.DESIGNATION, ""DATE CDE""=convert(varchar, ES.DATE_ENTREE_SORTIE,3),"
            .CommandText = .CommandText & """DATE LIV""='STOCK', REPORT='STOCK', BUTOIR='STOCK', POSTE='STOCK', RUB=AC.Code_Secteur, IMPRIME='STOCK' "
            .CommandText = .CommandText & "FROM ENTREE_SORTIE ES "
            .CommandText = .CommandText & "JOIN MVTS_STOCK MS ON (MS.N_DOCUMENT = ES.N_ENTREE_SORTIE) AND (MS.ORIGINE = 'ES') "
            .CommandText = .CommandText & "JOIN ENTREE_SORTIE_DETAIL ED ON (ED.N_Entree_Sortie = ES.N_Entree_Sortie) and (ED.N_Produit = MS.N_Produit) "
            .CommandText = .CommandText & "JOIN PRODUIT P ON (P.N_PRODUIT = MS.N_PRODUIT) "
            .CommandText = .CommandText & "JOIN Activite AC on (AC.N_Activites = P.CodeRubrique) "
            .CommandText = .CommandText & "JOIN AFFAIRE A ON ((A.N_AFFAIRE = ES.N_AFFAIRE_DEST) OR (A.N_AFFAIRE = ES.N_AFFAIRE_SOURCE)) "
            .CommandText = .CommandText & "WHERE (A.DESIGNATION in (" & Affaires & ")) "
            .CommandType = xlCmdSql
            .Connection = "ODBC;DSN=MABASESQL;UID=xxxxxx;PWD=yyyyyy;APP=Microsoft Office 2016;WSID=PASCAL;DATABASE=MABASESQL;QuotedId=No"
            .RefreshOnFileOpen = True
            .SavePassword = True
            .SourceConnectionFile = ""
            .SourceDataFile = ""
            .ServerCredentialsMethod = xlCredentialsMethodIntegrated
            .AlwaysUseConnectionFile = False
        End With
        
        With ActiveWorkbook.Connections("Lancer la requête à partir de MABASESQL")
            .Name = "Lancer la requête à partir de MABASESQL"
            .Description = ""
        End With
            
        ActiveWorkbook.Connections("Lancer la requête à partir de MABASESQL").Refresh
        
        GoTo Boucle
        
        With Selection.ListObject.QueryTable
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = True
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
        End With
    Boucle:
    End Sub

    Au passage, plus tard j'ai aussi été confronté à une autre problématique qui nécessitait de récupérer dans Excel le résultat d'une procédure stockée. J'ai trouvé une méthode qui permet de le réaliser, ça utilise Powerpivot / Powerquery (je sais plus comment l'appeler vu que MS s'amuse à changer le nom de ces outils avec les versions d'Excel...). Je vous conseille de vous documenter sur ces outils (bon courage....) ils sont puissants et permettent de faire des choses intéressantes lorsque la récupération des données d'un serveur SQL dans Excel est compliquée et nécessite plusieurs requêtes successives.

    Bon courage...


    Pascal.

    lundi 6 janvier 2020 13:30
  • Ca c'est ce que j'ai trouvé ensuite pour créer des récupérations de données plus complexes.

    Mais c'est une usine à gaz bien compliquée à faire fonctionner...

    Je pense que ça nécessiterait une formation.


    Pascal.

    lundi 6 janvier 2020 13:34
  • Merci.

    Pascal.

    lundi 6 janvier 2020 13:43