HOME > 即効テクニック > Excel VBA > ファイル操作関連のテクニック > ブックをOpenせずにデータを取得する−データベースクエリ

即効テクニック

ファイル操作関連のテクニック

ブックをOpenせずにデータを取得する−データベースクエリ

(Excel 97/2000)

エクセルブックを明示的にオープンすることなく、ブック内に含まれるデータにアクセスする手段として、データベースクエリがあります。
手動で行うには、メニューの”データ”−”新しいデータベースクエリ”から行います。

サンプルではクエリーテーブル(QueryTable)を作成し、取得データ範囲を限定、データ抽出を行うためにパラメータを追加します。

(前提)・対象ブックのデータ範囲に名前が定義してあること
    (ここでは”売上”という名で定義してあるものとします)
    ・一列目を項目行とし、”得意先,金額,日付”を含むこと
'===================================
'宣言セクション
'===================================
Dim Qt As QueryTable
Dim Para1 As Parameter, Para2 As Parameter

'===================================
'クエリの作成
'===================================
Sub AddQueryTable() 'クエリの作成

Dim Conn As String
Dim Dest As Range
Dim FName As String
'対象ブックの指定
FName = Application.GetOpenFilename("Excel(*.xls),*.xls")

Conn = "ODBC;DSN=Excel Files;DBQ=" & FName '接続文字列
Set Dest = ActiveSheet.Range("A1") '貼り付け先基準セル
'クエリの作成
Set Qt = ActiveSheet.QueryTables.Add(Conn, Dest)
Qt.Name = "URIAGE"
'パラメータの作成
Set Para1 = Qt.Parameters.Add("Date1", xlParamTypeDate)
Set Para2 = Qt.Parameters.Add("Date2", xlParamTypeDate)

End Sub

'===================================
'作成したクエリにSQL指定、データ取得
'===================================
Sub RefreshTable() 

Dim MyDate1 As String, MyDate2 As String
Dim MySQL As String

MyDate1 = Application.InputBox("開始日付", Type:=2)
MyDate2 = Application.InputBox("終了日付", Type:=2)

If MyDate1 = "" Or MyDate2 = "" Then Exit Sub
If Not IsDate(MyDate1) And Not IsDate(MyDate2) Then Exit Sub

’変数Qtに何も格納されていない(QueryTableが作成されていない)
’場合には上記プロシージャ呼び出し
If Qt Is Nothing Then Call AddQueryTable

’ここでは日付の絞込みを行うためBetween ? And ?でパラメータを指定
MySQL = "SELECT 得意先,金額,日付 FROM 売上 WHERE (日付 BETWEEN ? AND ?)" _
        & "ORDER BY 得意先"
Qt.SQL=MySQL

'2000の場合、CommandType,CommandTextの指定も可能
'Qt.CommandType = xlCmdSql
'Qt.CommandText = MySQL

'パラメータのセット
Para1.SetParam type:=xlConstant, value:=MyDate1
Para2.SetParam type:=xlConstant, value:=MyDate2
Qt.Refresh

End Sub
※クエリテーブルの指定・・・インデックス、もしくは名前
 ActiveSheet.QueryTables("URIAGE")・・・

※パラメータのセット
 SetParamメソッドの指定は上記サンプルのように指定の文字列(xlConstant)のほか、ダイアログボックス(xlPrompt)、セル(xlRange)を指定できます。

パラメータを指定するフィールドの変更は以下のようにDataTypeプロパティーとSQLを変更することで可能です。
MyParam = Application.InputBox("得意先指定", Type:=2)

MySQL = "SELECT 得意先,金額,日付 FROM 売上 WHERE (得意先 = ?)"

Qt.SQL = MySQL
'パラメータのセット
Para1.DataType = xlParamTypeVarChar
Para1.SetParam xlConstant, MyParam