Excel VBA を使用して SQL Server データベースに問い合わせる

SQL Server データベースから情報を引き出す外部データ セットを Excel で作成することはできますが、基盤となる SQL Server データベースから直接値を確認したり参照したりしたい場合があります。

VBA を使用してこれを行うことができます！

Excel 式から顧客の総収入値を調べることができる例を見てみましょう。

SQL Server データベースに問い合わせる VBA

次の VBA 関数は Excel VBA プロジェクトから使用することができます。 

Public Function LookupAWCustomerRevenue(intID As Long) As Currency Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnString As String If intID = 0 Then LookupAWCustomerRevenue = 0 Else strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open strConnString Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID) If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0 Else LookupAWCustomerRevenue = rs.Fields("CustRev").Value rs.Close End If End If End Function

この例では、ADO 接続を使用して SQL Server インスタンス (DBSRVSQL2014) に接続します。

 strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"
  • プロバイダーパラメーターは OLDEB 接続が確立されることを示し、データソースパラメーターは SQL Server インスタンスを指します。
  • 初期カタログパラメーターは、問い合わせるデータベースを特定します（AdventureWorks2014）
  • 統合セキュリティパラメーターは、SQL Serverとの認証にWindows Authemticationが使用されることを指定します。

SELECTステートメントからレコードセットを作成するためにRecordSetオブジェクト（rs）を使用します。

 Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

SELECTステートメントはリテラル文字列と関数が呼び出されたときに渡される変数intIDの値から構築されます。

冒頭のIf文はintIDの値が0であるかどうかをチェックします。整数変数は初期化されていない場合（言い換えれば、関数が呼び出されたときに値が提供されていない場合）、デフォルトでは0という値になります。 

 If intID = 0 Then LookupAWCustomerRevenue = 0

2番目のIf文は、SELECT文によって返される非数値のためにテストされます。 関数に渡された customerID が有効であり、注文がない場合、SUM(TotalDue) 式は NULL 値を返します。

If 文を含む VBA コードの行にブレークポイントを置き、ローカル ウィンドウを開いて、実行のその時点ですべての変数値を見ることができるようにしました。

VBA エディターのイミディエイト ウィンドウから関数を実行してテストしました。

 ?LookupAWCustomerRevenue(1)

ブレークポイントを設定すると、コードの実行は自動的にマークした行で停止し、その実行時点での環境を確認できます。

上のスクリーン ショットのローカル ウィンドウは、レコードセット オブジェクト変数 rs、特に rs からの最初のフィールド “CustRev” に対する値を示しています。 それはNullに設定されていることがわかります。 これは、CustomerID 値 1 の顧客は注文をしておらず、したがって結果の収益値もないためです。

次のスクリーン ショットは、SQL Server インスタンスで直接実行すると、クエリが NULL を返すことを示しています：

関数に正しい CustomerID を渡し、SELECT 文で非 NULL 値が返ってくると仮定すると、この関数によって顧客の総売上高が通貨値として返ります。

 LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Excel 式から VBA 関数を呼び出す

Excel 式から VBA 関数を呼び出すのは簡単なことです。 Excel 数式をセルに入力して作成すると、インテリセンスにより、内蔵関数だけでなく一致する VBA 関数も表示されます。 次のスクリーンショットはこれを示しています：

上記のリストにはLoで始まる2つのVBA関数があることがわかります。 LookupAWCustomerRevenueとLookupPersonNameです。

次の例では、LookupAWCustomerRevemue 関数をセルで使用して、ワークシートの A 列で分離された CustomerID 値の総収入を計算しています：

概要

この記事で、VBA コーディング、いくつかの VBA エラー処理とデバッグ技術、および Excel 式から VBA 関数をコールできる方法を見てきました。 この記事に関するご質問は、[email protected] までお気軽にお問い合わせください。

この記事で紹介したテクニックやVBAプログラミングについてもっと知りたい方は、ExcelとAccessのVBAトレーニングコースをご覧ください。
SQLサーバーデータベースでの作業についてもっと知りたい方は、SQLサーバートレーニングコースをご覧ください。

By Comment on Excel VBA を使用して SQL Server データベースに問い合わせる

コメントを残す

メールアドレスが公開されることはありません。