Excel のクエリ テーブルで動的レポートを作成する
| Microsoft Office 2000/Visual Basic プログラマーズ ガイド | .gif) |
CopyFromRecordset および TransferSpreadsheet メソッドは、頻繁には変更されない外部データのインポートまたはエクスポートの場合は問題ありません。ただし、Excel でレポートを作成し、そのデータを頻繁に更新して表示する必要がある場合は、クエリ テーブルを作成した方が便利です。クエリ テーブルは、Excel ワークシートにあり、SQL サーバー データベース、Microsoft Access データベース、Web ページ、テキスト ファイルなど、外部データソースにリンクされたテーブルです。ユーザーは、クエリ テーブルを更新して最新のデータを取得できます。
データベース クエリでクエリ テーブルを作成する
Excel では、新しいデータベース クエリを作成し、データをそのワークシートに返した際、ワークシート内にクエリ テーブルが作成されます。Excel ユーザー インターフェイスからデータベース クエリを作成するには、[ツール] メニューから [外部データの取り込み]、次に [新しいデータベース クエリ] をクリックし、[データ ソースの選択] ダイアログ ボックスから、既存のデータ ソースを選択、または新しいデータ ソースを作成します。そして、クエリ ウィザードまたは Microsoft Query グリッドを使用して、データベース クエリを作成し、データをワークシートに返します。データの取得後、[ツール] メニューの [更新] をクリックして、クエリ テーブルを更新します。
VBA で新しいクエリ テーブルを作成するには、QueryTables コレクションにある Add メソッドを使用します。QueryTables コレクションは、Worksheet オブジェクトに属し、ワークシートで使用する QueryTable オブジェクトのすべてが含まれています。新しいクエリ テーブルの作成後は、QueryTable オブジェクトの Refresh メソッドを使用して、クエリ テーブルでデータを表示します。Refresh メソッドを使用しない場合、クエリ テーブルにはデータが一切表示されません。次の例では、新しいワークシートにクエリ テーブルを作成します。
Function CreateQueryTable(strConnect As String, _
strSQL As String) As Boolean
' 外部データ ソースからクエリ テーブルを作成します。
' 有効な ADO 接続文字列および
' 有効な SQL SELECT ステートメントを取得します。
Dim cnnConnect As ADODB.Connection
Dim rstData As ADODB.Recordset
Dim qtbData As Excel.QueryTable
Dim wksNew As Excel.Worksheet
On Error GoTo CreateQueryTable_Err
' データ ソース上で接続を開きます。
Set cnnConnect = New ADODB.Connection
cnnConnect.Open strConnect
' 接続上で Recordset オブジェクトを開きます。
Set rstData = New ADODB.Recordset
rstData.Open strSQL, cnnConnect, adOpenForwardOnly
' 新しいワークシートを追加します。
Set wksNew = ThisWorkbook.Worksheets.Add
' 新しいワークシート内にクエリ テーブルを作成します。
Set qtbData = _
wksNew.QueryTables.Add(rstData, wksNew.Range("A1"))
' クエリ テーブルを更新してデータを表示します。
qtbData.Refresh
CreateQueryTable = True
CreateQueryTable_End:
On Error Resume Next
rstData.Close
Set rstData = Nothing
Exit Function
CreateQueryTable_Err:
CreateQueryTable = False
MsgBox "エラー : " & Err.Number & vbCrLf & Err.Description
Resume End_CreateQueryTable
End Function
CreateQueryTable プロシージャは、Office 2000 Developer CD-ROM の ODETools\V9\Samples\OPG\Samples\CH15 サブフォルダに含まれる Northwind.xls サンプル ファイルの modQueryTables モジュールにあります。
Access に基づいたソリューションで、Excel を使用して動的なレポートを作成する場合は、オートメーションにより、Access で実行中のコードからクエリ テーブルまたはピボットテーブル レポートを作成できます。オートメーションを使用すると、データベースから Excel へのデータ転送プロセスの管理が容易になります。また、レポートの書式設定、データのフィルタ、グラフの作成などが可能で、ユーザーは Access のボタンをクリックするだけで、Excel で書式設定されたレポートを呼び出すことができます。
Web クエリでクエリ テーブルを作成する
クエリ テーブルを Web ページ上のデータから作成することもできます。この場合、QueryTables コレクションにある Add メソッドの Connection 引数用に、Web ページ アドレス、または保存した Web クエリ (.iqy) のパス名とファイル名を指定します。次のコードでは、Microsoft Investor Web サイト から株式相場を取り出す保存したクエリを使用して、クエリ テーブルを作成します。
' 新規ワークシートを追加します。
Set wksNew = ThisWorkbook.Worksheets.Add
' 保存した Web クエリからクエリ テーブルを作成します。
Set qtbQuote = wksNew.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft " _
& "Office\Office\Queries\Microsoft " _
& "Investor Stock Quotes.iqy", Destination:=Range("A1"))
' クエリ テーブルのプロパティを設定し、データを取得します。
.Name = "Microsoft Investor Stock Quotes_1"
.FieldNames = True
.PreserveFormatting = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.Refresh BackgroundQuery:=False
End With
このコードは、Microsoft ‘Office 2000 Developer CD-ROM の ODETools\V9\Samples\OPG\Samples\CH15 サブフォルダ中の StockQuotes.xls サンプル ファイル内、modStockQuotes モジュールにある RetrieveStockQuotes プロシージャから一部引用したものです。