Comment passer des paramètres à la requête en SQL (Excel)

ce post est assez vieux que cette réponse sera probablement peu d'utilité à L'OP, mais j'ai passé l'éternité à essayer de répondre à cette même question, donc j'ai pensé que je voudrais le mettre à jour avec mes conclusions.

cette réponse suppose que vous avez déjà une requête SQL en place dans votre document Excel. Il ya beaucoup de tutoriels pour vous montrer comment faire cela sur le web, et beaucoup qui expliquent comment ajouter une requête paramétrée pour un, sauf qu'aucun ne semble à travailler pour un existing, OLE DB requête.

donc, si vous, comme moi, avez reçu un document Excel legacy avec une requête de travail, mais l'utilisateur veut être en mesure de filtrer les résultats basés sur l'un des champs de base de données, et si vous, comme moi, ne sont ni un Excel ni un gourou SQL, cela pourrait être en mesure de vous aider.

La plupart des réponses web à cette question semblent indiquer que vous devriez ajouter un "?"dans votre requête pour obtenir Excel pour vous demander un paramètre personnalisé, ou placer l'invite ou la référence de la cellule dans [parenthèses] où le paramètre doit être. Cela peut fonctionner pour une requête ODBC, mais il ne semble pas fonctionner pour un OLE DB, en retournant "Aucune valeur donnée pour un ou plusieurs paramètres requis" dans le premier cas, et "nom de colonne invalide ‘xxxx’" ou "objet inconnu ‘xxxx’" dans les deux derniers. De même, l'utilisation des boutons mythiques "paramètres..." ou "éditer la requête..." n'est pas non plus une option car ils semblent être définitivement grisés dans cette instance. (Pour référence, J'utilise Excel 2010, mais avec un classeur Excel 97-2003 (*.xls))

ce que nous pouvons faire, cependant, est d'ajouter une cellule de paramètre et un bouton avec une routine simple pour mettre à jour programmatiquement notre texte de requête.

tout D'abord, ajoutez une ligne au-dessus de votre table de données externe (ou n'importe où) où vous pouvez mettre une invite de paramètre à côté d'une cellule vide et d'un bouton (développeur->Insert – > bouton (Contrôle de forme) - vous pouvez avoir besoin d'activer L'onglet Développeur, mais vous pouvez trouver comment faire cela ailleurs), comme donc:

Ensuite, sélectionnez une cellule dans la zone de données externes (bleu), puis ouvrez Data->Refresh All (dropdown)->propriétés de connexion... pour regarder votre requête. Le code dans la section suivante suppose que vous avez déjà un paramètre dans votre requête (propriétés de connexion->Definition->texte de commande) sous la forme "WHERE (DB_TABLE_NAME.Nom_champ = ‘valeur par Défaut du Paramètre de Requête')" (y compris les parenthèses). Clairement " DB_TABLE_NAME.Field_Name " et " Default Le paramètre de requête " devra être différent dans votre code, basé sur le nom de la table de base de données, le nom du champ de valeur de la base de données (colonne), et une certaine valeur par défaut pour rechercher quand le document est ouvert (si vous avez auto-refresh set). Notez le " DB_TABLE_NAME.Field_Name " valeur que vous aurez besoin dans la section suivante, avec le "nom de connexion" de votre requête, qui peut être trouvé en haut de la boîte de dialogue.

fermez les propriétés de connexion, et appuyez sur Alt+F11 pour ouvrir l'éditeur VBA. Si vous n'y êtes pas déjà, faites un clic droit sur le nom de la feuille contenant votre bouton dans la fenêtre "Projet", et sélectionnez "Voir le Code". Coller le code suivant dans la fenêtre de code (Copier est recommandé, car les guillemets simples/doubles sont difficiles et nécessaires).

Sub RefreshQuery()
 Dim queryPreText As String
 Dim queryPostText As String
 Dim valueToFilter As String
 Dim paramPosition As Integer
 valueToFilter = "DB_TABLE_NAME.Field_Name ="

 With ActiveWorkbook.Connections("Connection name").OLEDBConnection
     queryPreText = .CommandText
     paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
     queryPreText = Left(queryPreText, paramPosition)
     queryPostText = .CommandText
     queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
     queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
     .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText
 End With
 ActiveWorkbook.Connections("Connection name").Refresh
End Sub

remplacer " DB_TABLE_NAME.Field_Name" et "nom de connexion" (à deux endroits) avec vos valeurs (les guillemets doubles et le signe espace et égale doivent être inclus).

remplacer " référence de la cellule" avec la cellule où votre paramètre (la cellule vide depuis le début) - la mienne était la deuxième cellule de la première ligne, j'ai donc mis "B1" (les guillemets sont nécessaires).

enregistrer et fermer l'éditeur VBA.

Entrez votre paramètre dans la cellule appropriée.

faites un clic droit sur votre bouton pour affecter le sous rafraîchir comme macro, puis cliquez sur votre bouton. La requête doit mettre à jour et afficher les données!

Notes: En utilisant la totalité nom du paramètre du filtre ("DB_TABLE_NAME.Field_Name =") n'est nécessaire que si vous avez des jointures ou d'autres occurrences de signes égaux dans votre requête, sinon juste un signe égal serait suffisant, et le calcul de Len () serait superflu. Si votre paramètre est contenu dans un champ qui est également utilisé pour joindre des tables, vous devrez changer la ligne "paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1" dans le code en "paramPosition = InStr(Right(.CommandText, Len.(CommandText) - InStrRev(.CommandText, "WHERE")), valueToFilter) + Len(valueToFilter) - 1 + InStr(.CommandText, "où") " de sorte qu'il ne cherche que la valeur de filter après Le "où".

cette réponse a été créée à l'aide des "BaconBits" de datapig où j'ai trouvé le code de base pour la mise à jour de la requête.