# QueryTable01.rb.txt: ruby script (encoding: Windows-31J)
  # ExcelのQueryTable操作  data01.mdb を素材にして z_query01.xls を出力
  # Accessデータベースを読み込む。「ID,氏名,身長,誕生日」からなるデータ
  # データベースのsql実行結果をワークシートに読み込んで保存
  # 第１シートには素直な読込み結果、第２シートには身長順の読込み結果を記録
require "exlap"

data_file = "data01.mdb"  # 素材データのAccessファイル
filename = "z_query01.xls"  # 新たに作るワークブック
if test(?e, filename)  # z_query01.xlsが既に存在するなら削除
  File.unlink(filename)
end
fullname = Exl::fullpath(data_file)  # data_fileのフルパス名
unless test(?e, fullname)  # data_fileがみつからない
  STDERR.puts "'#{fullname}' がみつかりません．"
  exit
end
cnn = "ODBC;DSN=MS Access Database;DBQ=#{fullname}"
tblname = "身長と誕生日"  # データベースにあるテーブルの名前

xl = Exlap.new  # Excelの起動
xl.opens(filename) do |wb|  # ワークブックを開く
  ss = wb.fes  # 空のシートを選択
  ss.Name = "素直に読み込んだ結果"
  sql = "select * from #{tblname};"
  qt = ss.QueryTables.Add(
    'Connection'=>cnn,
    'Destination'=>ss.Range("A1"),
    'Sql'=>sql)
  qt.Name = "素直な読込み"  # クエリテーブルの名前
  qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
  qt.RefreshPeriod = 0  # 「定期的更新」の間隔(0で無効, 5なら5分)
  qt.BackgroundQuery = false  # バックグラウンド処理をしない
  qt.Refresh  # クエリテーブルの更新(実際にデータを読み込む)
  ss.range_autofit  # セル幅を自動調整
  qt.Delete  # クエリテーブル(その定義)を削除。読込み結果は消えない

      # 身長順に並べ替える
  ss = wb.fes  # 空のシートを選択
  ss.Name = "身長順"
  ss.Range("A1").Value = "身長順に並べ替えた結果"
  sql = "select 身長,氏名,ID,誕生日 from #{tblname} order by 身長 desc;"
  qt = ss.QueryTables.Add(
    'Connection'=>cnn,
    'Destination'=>ss.Range("A2"),
    'Sql'=>sql)
  qt.Name = "身長順"  # クエリテーブルの名前
  qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
  qt.RefreshPeriod = 0  # 「定期的更新」の間隔(0で無効)
  qt.BackgroundQuery = false  # バックグラウンド処理をしない
  qt.Refresh  # クエリテーブルの更新
  ss.range_autofit  # セル幅を自動調整
  qt.Delete  # クエリテーブル(その定義)を削除

  wb.ss(1).Activate  # 第１シートに焦点を当てる
  wb.save  # ワークブックを保存
end  # ここでワークブックが閉じられる
xl.quit  # Excelの終了

__END__
＊クエリテーブルの主なプロパティ
・qt.Name  クエリテーブルの名前
　もし後で再利用するなら指定しておく。再利用しないなら指定しなくてもかまわない。無指定の時は適当な名前が割り当てられる。
・qt.SaveData  「クエリテーブルをワークブックと一緒に保存」の有無
　これを true にすると、クエリテーブル(その定義)がワークブックと一緒に保存される。
　保存しておくと、Excelをいったん終了し、再度ワークブックを開いた時に、そのクエリテーブルを再利用できる。
・qt.RefreshPeriod  定期的更新の時間間隔(分単位)
　これを 0 にすると、定期的更新は行われない。
　これを例えば 5 とすれば、5分ごとに更新が行われる。
・qt.BackgroundQuery  バックグラウンド処理の有無
　これを true にすると、バックグラウンド処理で取込みを行うようになる。つまり、一定時間ごとに(定期的に)背後タスクで取込みを行う。時間の指定は先述の RefreshPeriod プロパティで指定。
　BackgroundQuery が false だと、データの更新が終了するまで待機する。
・qt.Refresh  更新
　データを実際に取り込む。あるいは、バックグラウンド処理が指定されていれば、取込みの状態を継続する(定期的に取り込む)。
・qt.Delete  クエリテーブルの削除
　QueryTables.Add でクエリテーブルを追加したわけだが、それを削除する。
　先の Refresh の BackgroundQuery が true だと、クエリテーブルが働いている状態が続くので、普通は Delete しようとするとエラーになる。