Comment ajouter des paramètres à une requête de données externes dans Excel qui ne peuvent pas être affichés graphiquement?

j'utilise souvent MS Excel Get External Data pour créer des rapports simples-exécuter des requêtes contre des bases de données et afficher joliment dans Excel. Les grandes fonctionnalités d'Excel comme le filtrage et les tables pivotantes et l'interface familière pour les utilisateurs le rendent assez bon pour cela. Cependant, une limite avec Microsoft Query est que vous ne pouvez pas ajouter de paramètres à des requêtes qui ne peuvent pas être affichées graphiquement, ce qui limite considérablement le SQL que vous pouvez écrire.

Est-il une solution pour l'erreur "paramètres ne sont pas autorisé dans les requêtes qui ne peuvent pas être affichées graphiquement"?

33
demandé sur Rory 2010-06-22 13:37:48

4 réponses

L'interface D'Excel pour les requêtes SQL Server ne vous permettra pas d'avoir des paramètres personnalisés.  Une façon de contourner cela est de créer une requête générique de Microsoft, puis d'ajouter des paramètres, puis de coller votre requête paramétrée dans les propriétés de la connexion.  Voici les étapes détaillées pour Excel 2010:

  1. Ouvrir Excel
  2. Goto onglet
  3. À Partir D'Autres Sources bouton choisir À Partir De Microsoft Query
  4. la fenêtre" Choisir la Source de données " apparaîtra.  Choisir une source de données et cliquez sur OK.
  5. La Requête Qizard
    1. choisir la colonne: la fenêtre apparaîtra.  L'objectif est de créer une requête générique. Je recommande de choisir une colonne d'une petite table.
    2. données du filtre: cliquez simplement sur
    3. ordre de tri: il suffit de cliquer
    4. terminer: cliquez simplement Terminer.
  6. la fenêtre" Importer des données " apparaîtra:
    1. cliquez sur le Propriétés... bouton.
      1. choisissez le Définition onglet
      2. dans la section " Commande texte:" ajouter une clause WHERE Cela inclut les paramètres Excel.  Il est important d'ajouter tous les paramètres que vous voulez maintenant.  Par exemple, si je veux deux paramètres, je pourrais ajouter ceci:

        où 1=? et 2 = ?
      3. Cliquez sur OK pour revenir à la fenêtre" Importer des données"
    2. Choisir Rapport Pivotable
    3. Cliquez sur OK
  7. on vous demandera d'entrer la valeur des paramètres pour chaque paramètre.
  8. une Fois que vous avez entrez les paramètres de votre tableau croisé dynamique
  9. Aller batck à l' onglet et cliquez sur les connexions Propriétés bouton
    1. cliquez sur le Définition onglet
    2. dans la section " Commande texte:", coller dans la requête SQL réelle que vous voulez avec le même nombre de paramètres que vous avez définis précédemment.
    3. cliquez sur le Paramètres... bouton
      1. saisissez les valeurs d'Invite pour chaque paramètre
      2. Cliquez sur OK
    4. Cliquez sur OK pour fermer la fenêtre de propriétés
  10. Félicitations, vous avez maintenant les paramètres.
45
répondu behonji 2012-08-21 13:30:52

Simple solution de Contournement (pas de VBA requis)

  1. table de clic droit, agrandir" Table "contexte manu, sélectionner " Propriétés des données externes"
  2. cliquez sur le bouton "Propriétés de connexion" (étiqueté dans tooltip seulement)
  3. aller-à L'onglet "Définition"

a partir de là, éditer le SQL directement en ajoutant '?"partout où vous voulez un paramètre. Ça marche comme avant, sauf que tu ne te fais pas harceler.

23
répondu Aaron DeMille 2012-07-04 18:29:25

si vous avez Excel 2007, vous pouvez écrire VBA pour modifier les connexions (c.-à-d. les requêtes de données externes) dans un classeur et mettre à jour la propriété CommandText. Si vous ajoutez simplement ? où vous voulez un paramètre, puis la prochaine fois que vous rafraîchissez les données, il vous demandera les valeurs pour les connexions! magie. Lorsque vous regardez les propriétés de la connexion, le bouton Paramètres sera maintenant actif et utilisable comme d'habitude.

Sub UpdateQuery
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection

            ' If you do have multiple connections you would want to modify  
            ' the line below each time you run through the loop.
            odbcCn.CommandText = "select blah from someTable where blah like ?"

        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.CommandText = "select blah from someTable where blah like ?" 
        End If
    Next
End Sub
5
répondu Rory 2014-03-25 14:39:27

OUI - solution consiste à enregistrer un classeur dans un fichier XML (par exemple. 'XML Spreadsheet 2003') et éditez ce fichier sous forme de texte dans le bloc-notes! utilisez la fonction" recherche " de notepad pour trouver le texte d'interrogation et changer vos données en "?".

enregistrer et ouvrir dans excel, essayer les données de rafraîchissement et excel sera le suivi des paramètres.

-1
répondu gabrys 2013-07-01 01:26:24