読者です 読者をやめる 読者になる 読者になる

一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

AS400からのデータ転送を行うExcelVBA

データ転送TTO

 AS400からのデータ転送権限を持っている人は、次のような画面にお世話になっていると思います。

W.TTO

f:id:a_habakiri:20161024135711p:plainf:id:a_habakiri:20161024135803p:plain

 これは拡張子が.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ファイルをフロントエンドに用意したらどうでしょうか。

f:id:a_habakiri:20161024170341p:plain

  1. セルB2にデータ種別をドロップダウンにて設定するとVlookup関数でセルC2にはAS400のライブラリ名が入ります。
  2. セルB4に支店名をドロップダウンにて設定するとVlookup関数でセルC4には支店コードが入ります。
  3. セル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データ転送ではヘッダを取得するのは難しいため、あらかじめローカルにヘッダを保管してある設定にしています。

f:id:a_habakiri:20161024171614p:plain

もちろん、このコードをそのまま実行しても意味がありません。自分の環境に合わせて書き換えが必要です。

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を書き換えています。
 今回の記事のVBAExcelシートもそのままでは役に立ちません。コンセプトを部下に伝えたいと思います。
 ポイント
1. Excel シートをフロントエンドに使うことで入力を容易にする
2. ただし「本社」などの可読性のある形で指定してもらったものを、Vlookupで支店コードの10に置き換えるか、VBAで置き換えるかしてユーザーフレンドリー環境のフォローをします
3. 入力内容に応じてデータ転送定義ファイルttoを動的に書き換えます
4. 作成されたttoをバッチで実行します。 

a-habakiri.hateblo.jp

広告を非表示にする