# QueryTable02.rb.txt: ruby script (encoding: Windows-31J)
  # ExcelのQueryTable操作  data01.mdb → data01.xls の変換の後、
  # data01.xls を素材にして z_query02.xls を出力
  # AccessデータベースをまるごとExcelファイルに変換(data01.mdb → data01.xls)
  # data01.xlsを外部データとしてクエリテーブルで読み込む(z_query02.xlsの生成)
  # クエリテーブルを保存し、後で再利用する
  # data01.xlsに２人分のデータを追加。それがz_query02.xlsに反映されるのを確認
require "exlap"
require "yado"

mdb_file = "data01.mdb"  # 素材データのAccessファイル
data_file = "data01.xls"  # 'data01.mdb' をこれにまるごとコピーする
if test(?e, data_file)  # data01.xlsが既に存在するなら削除
  File.unlink(data_file)
end
db = Yado.new(mdb_file)  # Accessデータベースを開く
db.pav(data_file)  # データベースの中身をまるごと data01.xls に書き出す
db.close  # データベースを閉じる
filename = "z_query02.xls"  # 新たに作るワークブック
if test(?e, filename)  # z_query02.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=Excel Files;DBQ=#{fullname}"
tblname = "身長と誕生日"  # データベースにあるテーブルの名前

xl = Exlap.new  # Excelの起動
xl.opens(filename) do |wb|  # ワークブックを開く
  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 = true  # 「クエリテーブルをワークブックと一緒に保存」する
  qt.RefreshPeriod = 0  # 「定期的更新」の間隔(0で無効)
  qt.BackgroundQuery = false  # バックグラウンド処理をしない
  qt.Refresh  # クエリテーブルの更新
  ss.range_autofit  # セル幅を自動調整
  wb.save  # ワークブックを保存
end  # ここでワークブックが閉じられる

    # data01.xls に２人分のデータを追加する
append_data = [%w(6 安部 190.5 1989/5/18),
       %w(7 伊藤 158.9 1968/3/28)]
xl.opens(data_file) do |wb|
  ss = wb.ss(1)
  y,x = ss.range_last  # 書き込みのある領域の最後の番地
  ss.from_a(y+1,1, append_data)  # ２人分のデータを追加
  ss.UsedRange.Name = tblname  # 追加分を含め「名前付き範囲」を設定
  wb.save
end

    # z_query02.xlsの設定済みのクエリテーブルを更新
xl.opens(filename) do |wb|
  ss = wb.ss("身長順のシート")
  qt = ss.QueryTables("身長順")
  qt.Refresh
  wb.save
end
xl.quit  # Excelの終了

__END__
＊クエリテーブルの存在確認と選択
　変数 ss にExcelのワークシートが代入されている場合、
    count = ss.QueryTables.Count
とすれば、クエリテーブルが何個あるか、その個数が変数 count に代入される。
　count が 0 なら、クエリテーブルが存在しないことになる。
　ワークシートに含まれているクエリテーブルの名前をすべて出力するには、次のようにする。
    ss.QueryTables.each do |qt|
      puts qt.Name
    end
　１つのクエリテーブルをターゲットに選びたい時は
    qt = ss.QueryTables(1)  # １番目のクエリテーブルを選ぶ
    qt = ss.QueryTables("身長順")  # 「身長順」のクエリテーブルを選ぶ