AS400からのデータ転送を行うExcelVBA
データ転送TTO
AS400からのデータ転送権限を持っている人は、次のような画面にお世話になっていると思います。
W.TTO
これは拡張子が.ttoのデータ転送定義ファイルです。どこからどのような条件でデータを抜き出すかを設定し、「IBMiからデータ転送」ボタンをクリックすると、AS400からデータが取得されます。
データ転送TTOをコマンドラインで実行する
「IBMiからデータ転送」ボタンをクリックするのではなく、コマンドでデータ転送を実行することもできます。w.TTOを用いて転送する場合はDOSプロントで次のように実行します。
>rtopcb w.TTO
RTOPCB はAS400からPCとへとデータ転送するプログラム本体です。①RTOPCB.EXEにパスが通っているか、
②または、拡張子ttoにRTOPCB.EXEを対応させるか
③あるいはカレントフォルダにRTOPCB.EXEが必要です。
実際の利用場面では、次のようなバッチファイルを作成して実行すればデータを取得できます。
サンプル.BATファイル
@ECHO OFF
c:
cd c:\TTOwrk\
del w.csv
rtopcb w.TTO
copy Header01.csv+w.csv 返品.csv
ただ、これだけの内容なら手動で「IBMiからデータ転送」ボタンをクリックする方が速いです。
データ転送TTOをマクロ・プログラムから呼び出す
データ転送TTOはテキストファイル
TTOファイルはテキストファイルなのでテキストエディタで覗くと…W.TTOファイル
TRTOPC FROM BDAT01 SELECT B001,B002,B003,SUM(B004) WHERE B005=20160912 ORDER BY 3 c:\wk\wrk\w.CSV <311 13211 661 22 JOIN BY GROUP BY HAVING SYSTEM AS400 OPTIONS 2[[.HMSYMDN11 ? WINSPOOL 1 10 6 0 SQLSEL HTML 000 2 2 1 1 1 10000000000100000000100001000003006160010 HCSET x-sjis HTITLE HCTEXT PROPS 000110
このようになっています。赤字の部分がSQLや保存先情報などです。 ということは赤字の部分を書き換えれば自由にデータを取得できることになります。
Excelに必要項目を入力してVBAによりttoを作成実行する
「AS400のファイル名は知らないや」という人でも使えるように
次のようなExcelファイルをフロントエンドに用意したらどうでしょうか。
- セルB2にデータ種別をドロップダウンにて設定するとVlookup関数でセルC2にはAS400のライブラリ名が入ります。
- セルB4に支店名をドロップダウンにて設定するとVlookup関数でセルC4には支店コードが入ります。
- セルB3には取得したい日付を設定します。
※ 実用上は支店マスタは別シートにするなどして見せないようにすることが多いと思います。
「データ取得」ボタンをクリックすると下記コードが実行されます。
Private Sub DoTTO_Click()
Dim Ftext, Wtext, Wtext2 As String
Dim Fname As String
ThisWorkbook.Worksheets("Sheet1").Select
With ActiveSheet
' 保存ファイル名
Fname = .Range("B2").Value & ".csv"
' 取得先ASファイル名
Ftext = .Range("C2").Value
' Where条件(支店コード)
Wtext = "B005=" & .Range("C4").Value
' Where条件(全社なら支店指定クリア)
Wtext2 = .Range("C4").Value
If Wtext2 = 0 Then Wtext = ""
End With
Open "c:\wk\wrk\w.csv" For Output As #1
Print #1, "TRTOPC"
Print #1, ""
Print #1, "FROM " & Ftext ' 抽出元AS
Print #1, "SELECT B001,B002,B003,SUM(B004)"' 取得項目
Print #1, "WHERE " & Wtext ' 抽出条件
Print #1, "Order BY B001,B002"' ソート条件
Print #1, "3"
Print #1, "c:\wk\wrk\w.CSV" ' 保存先ファイル
Print #1, "<311"
Print #1, "13211 661"
Print #1, ""
Print #1, "22"
Print #1, "Join BY" ' 横結合時条件
Print #1, "Group BY B002" ' 集計項目指定
Print #1, "HAVING" ' 集計項目の条件
Print #1, "SYSTEM AS400"
Print #1, "OPTIONS 2[[.HMSYMDN11"
Print #1, ""
Print #1, "Print"
Print #1, ""
Print #1, "WINSPOOL"
Print #1, ""
Print #1, "1"
Print #1, "10"
Print #1, "6"
Print #1, "0"
Print #1, "SQLSEL"
Print #1, "HTML 000 2 2 1 1 1 10000000000100000000100001000003006160010"
Print #1, "HCSET x - sjis"
Print #1, "HTITLE"
Print #1, "HCTEXT"
Print #1, "PROPS 110"
Close #1
Open "c:\wk\wrk\w.bat" For Output As #2
Print #2, "@ECHO OFF"
Print #2, "c:"
Print #2, "cd c:\TTOwrk\"
Print #2, "del w.csv"
Print #2, "rtopcb w.TTO"
Print #2, "copy Header01.csv+w.csv 返品.csv"
Print #2, "返品.csv"
Close #2
Shell "c:\wk\wrk\w.bat", vbHide
End Sub
これにより
1. フロントエンドとして Excel にユーザーがほしいデータの条件を入力してもらい
2. Vlookup関数やVBAで取得先・取得条件に変換し
3. データ転送定義ファイルの必要部分に当てはめてW.ttoを作成保存
4. あらかじめ用意されたヘッダと合体するようバッチファイルを作成
5. バッチファイルを実行
6. ヘッダ付きのCSVファイルとなります
※ AS400データ転送ではヘッダを取得するのは難しいため、あらかじめローカルにヘッダを保管してある設定にしています。
もちろん、このコードをそのまま実行しても意味がありません。自分の環境に合わせて書き換えが必要です。
Excel VBAによりttoを作成実行するポイント
自分のやりたいことに合わせて書き換えるポイントは二つです。
ひとつはttoのSQL文法を理解すること。
もう一つはExcelで選択したユーザーの希望に応じてttoを書き換えることです。
| SQL | 内容 | 説明 |
| Select | 抽出する項目を指定 | ここでは支店・店舗コード・店舗名・返品数 GROUP BYと組み合わせてSUM()などの集計関数も使える |
| From | データ取得先 | Excelシートで「返品」を選択→Vlookupでマスタから返品データBDAT1選択→tto書き換え
As400ではABC.XYZのようにピリオド使用 |
| Where | 抽出条件 | Excelシートで期間を指定→tto書き換え 「全社」を選択→Where条件なし 「全社以外」を選択→Vlookupでマスタから店舗コード選択→tto書き換え |
| Group by | 集計時にグループ化する対象項目を指定 | ここでは店舗コード単位 |
| Having | Group byで指定した項目がどんな条件の時にデータを取得するか | |
| Join by | 横結合時の結合条件 | |
| Order by | ソートする項目 | ここでは店舗コード順 |
ここでの例では、どのデータ種別を選択しても店舗コード単位でデータを集計するのは同じなので Group by,Order by はttoにあらかじめ書き込んであります。
一方、取得したいデータの種別や対象期間、対象店舗はExcelでの選択に応じてttoを書き換えています。
今回の記事のVBAもExcelシートもそのままでは役に立ちません。コンセプトを部下に伝えたいと思います。
ポイント
1. Excel シートをフロントエンドに使うことで入力を容易にする
2. ただし「本社」などの可読性のある形で指定してもらったものを、Vlookupで支店コードの10に置き換えるか、VBAで置き換えるかしてユーザーフレンドリー環境のフォローをします
3. 入力内容に応じてデータ転送定義ファイルttoを動的に書き換えます
4. 作成されたttoをバッチで実行します。
