webdevqa.jp.net

グラフィカルに表示できないExcelの外部データクエリにパラメーターを追加する方法

私はよくMS ExcelのGet External Data簡単なレポートを作成します-データベースに対してクエリを実行し、Excelでうまく表示します。フィルタリングテーブルやピボットテーブルなどのExcelの優れた機能と、ユーザーにとって使い慣れたインターフェイスは、この点で非常に優れています。ただし、Microsoft Queryの1つの制限は、グラフィカルに表示できないクエリにパラメーターを追加できないことです。これにより、作成できるSQLが大幅に制限されます。

「グラフィカルに表示できないクエリではパラメータを使用できません」というエラーの解決策はありますか?

36
Rory

Excel 2007を使用している場合、VBAを記述して、ブックの接続(外部データクエリ)を変更し、CommandTextプロパティを更新できます。パラメーターが必要な場所に?を追加するだけで、次にデータを更新するときに、接続の値の入力を求められます。魔法。接続のプロパティを見ると、[パラメータ]ボタンがアクティブになり、通常どおり使用できるようになります。

例えば。マクロを記述し、デバッガーでステップ実行し、CommandTextを適切に設定します。これが完了したら、マクロを削除できます-これはクエリを更新するための手段にすぎません。

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
Rory

SQL Serverクエリ用のExcelのインターフェイスでは、カスタムパラメータを使用できません。これを回避する方法は、ジェネリックMicrosoftクエリを作成し、パラメーターを追加してから、パラメーター化されたクエリを接続のプロパティに貼り付けることです。 Excel 2010の詳細な手順は次のとおりです。

  1. Excelを開く
  2. ジャンプデータタブ
  3. 他のソースからボタンからMicrosoft Queryからを選択します
  4. [データソースの選択]ウィンドウが表示されます。 データソースを選択をクリックし、[〜#〜] ok [〜#〜]をクリックします。
  5. クエリキザード
    1. 列の選択:ウィンドウが表示されます。目標は、汎用クエリを作成することです。小さなテーブルから1つの列を選択することをお勧めします。
    2. データのフィルター:次へをクリックするだけです
    3. ソート順:次へをクリックするだけ
    4. 完了:完了をクリックするだけです。
  6. [データのインポート]ウィンドウが表示されます。
    1. Properties ...ボタンをクリックします。
      1. Definitionタブを選択します
      2. 「コマンドテキスト:」セクションでaddWHERE節を追加 Excelパラメーターを含みます。現在必要なすべてのパラメーターを追加することが重要です。たとえば、2つのパラメーターが必要な場合、これを追加できます。
        WHERE 1 =?and 2 =?
      3. [〜#〜] ok [〜#〜]をクリックして、「データのインポート」ウィンドウに戻ります。
    2. ピボットテーブルレポートを選択します
    3. [〜#〜] ok [〜#〜]をクリックします
  7. 各パラメーターのパラメーター値を入力するよう求められます。
  8. パラメータを入力すると、ピボットテーブルに移動します
  9. Dataタブに行き、接続をクリックしますPropertiesボタン
    1. Definitionタブをクリックします
    2. [コマンドテキスト:]セクションで、実際のSQLクエリに貼り付け前に定義したのと同じ数のパラメーターを使用します。
    3. Parameters ...ボタンをクリックします
      1. プロンプト値を入力各パラメーター
      2. [〜#〜] ok [〜#〜]をクリックします
    4. [〜#〜] ok [〜#〜]をクリックして、プロパティウィンドウを閉じます。
  10. おめでとうございます、パラメータができました。
50
behonji

簡単な回避策(VBAは不要)

  1. [テーブル]を右クリックし、[テーブル]コンテキストmanuを展開して、[外部データプロパティ]を選択します
  2. [接続プロパティ]ボタンをクリックします(ツールチップにのみラベルが付いています)
  3. 「移動」タブ「定義」

ここから、「?」を追加してSQLを直接編集しますパラメータが必要な場所。あなたがナグされないことを除いて、以前と同じように働きます。

24
Aaron DeMille