【目次】

      • 1.処理説明
        2.詳細処理
        　2-1.パス取得
        　2-2.シートコピー
        　2-3.データ取り込み
        　2-4.件数取得
        　2-5.セル比較
        　2-6.終わりメッセージ出力
        3.まとめ

1.処理説明

こんにちは。キャスレーコンサルティングID(インテグレーション＆サービス)部の金です。
今回は、業務効率化に役立つVBA/マクロについて、コード例を交えて紹介していきます。

例えば、テーブルを更新した際に更新前後のデータを比較し、
正しく更新されたかを検証したい場合に、使うことが出来ます。

特に更新対象が大量にある場合、目で確認するのと比べ正確かつ効率的に検証が行えます。

TSVファイルのデータを比較したい場合、以下の処理が必要となります。

    • ①パス取得
      ②シートの形式を設定
      ③データ取り込み
      ④件数取得
      ⑤セル比較
    • ⑥終わりメッセージ出力

上記の一連の処理を、VBA/マクロにてツール化します。

ツール作成後、以下の手順を踏むと求める結果が得られます。
手順①：TSVファイルのパスを設定
手順②：「更新」ボタンを押下
結果：ファイル毎に、一致/不一致の結果が得られる

それでは、詳細な処理について説明していきます。
こちらが、作成したマクロ全体となります。

Sub 更新()
'
' 更新 Macro
'

'
    Dim Targe As String
    Dim TargetPass As String
    Dim maxrrow As String
    
'   開始画面へ
    Sheets("開始").Select
    
'   パス取得
    Target = Range("$B$1").Value
    
'   シートコピー
    Worksheets("style").Range("A:J").Copy Destination:=Worksheets("結果").Cells(1, 1)
    
'   結果画面へ
    Sheets("結果").Select
    TargetPass = Target + "￥データ.tsv"
    
    '外部データの取り込みでデータを取得
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TargetPass, Destination:=Range("$A$2"))
        .TextFileCommaDelimiter = False
'       テキストファイルの各列に適用されるデータ型を指定する定数を配列形式で設定。
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        .Delete
    End With
    
'   取り込んだデータの件数を取得
    maxrrow = Range("A2").End(xlDown).Row

'   FillDown メソッド
'   指定された範囲の上端に位置する 1 つまたは複数のセルを下方向に複写します。
'   複写される行にあるセルの内容と書式が範囲内の残りのセルに複写されます。
'   たとえば、大量のシートがあって、特定の範囲を下までコピーする必要が有る場合などは、マクロを利用すると圧倒的に早くコピーできます。

'   結果シートのセル（判定）の内容を取り込んだデータの件数分複写
    With Worksheets("結果").Range(Cells(3, 8), Cells(maxrrow, 10)).FillDown
    End With

'   開始画面へ
    Sheets("開始").Select
    
'   終わりメッセージ出力
    MsgBox ("終わりました！")
    
End Sub

2.詳細処理

以降の例は、2列分のデータを比較する際のものとなります。
シート(“style”)を変更すると、データ数が増えた場合も比較ができます。

2-1.パス取得

‘ファイルの場所（フルパス）→”B1”
セル(”B1”)の値を取得し、変数（Target）に代入する

このようにすることで、”B1”パス配下の複数のファイルを取得することができます。
取り込みたいファイルが1つの場合、直接ファイル名まで設定しても構いません。

'　パス取得
Target = Range("$B$1").Value

2-2.シートコピー

設定したシート(“style”)の形式を、データ取り込み先のシート(“結果”)にコピー

取り込み先のシートの形式を一貫するため、形式専用のシートを作成し、
毎回取り込む前に、シートをコピーする。
形式の編集が特に必要ではない場合は、省略可能です。

'　シートコピー
Worksheets("style").Range("A:J").Copy Destination:=Worksheets("結果").Cells(1, 1)

シート(“style”)の形式：

データ１の結果判定関数：
比較対象が想定結果と一致する場合と、一致しない場合の出力内容を関数にて設定する。

例えば、今回の内容ではセル”D3”と”F3”が一致する場合結果に”OK”を、
一致しない場合、結果に”NG”を出力するように設定しております。

最初に、セル”A3”（3行目のデータ）が存在するかの判定を行うのは、
以降の[2-5]の処理で、結果の配下の関数を全件設定するため、
対象のレコードが存在しない場合は、判定を行わない設定を追加しております。

データ２の結果判定関数：

データ１とデータ２の総合結果判定関数：
全件の一致或いは不一致の結果を判定する
データ１とデータ２の判定結果が両方”OK”の場合、
全件の判定結果に、”OK”を出力する内容を設定しております。

2-3.データ取り込み

[2-1]で取得したパス配下のTSVファイルを取り込む

'　結果画面へ
Sheets("結果").Select
TargetPass = Target + "¥データ.tsv"

'　外部データの取り込みでデータを取得
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TargetPass, Destination:=Range("$A$2"))
.TextFileCommaDelimiter = False
'　テキストファイルの各列に適用されるデータ型を指定する定数を配列形式で設定。
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
.Delete
End With

取り込み結果：

2-4.件数取得

[2-5]でセルを比較するため、取り込んだデータの件数を取得

'　取り込んだデータの件数を取得
maxrrow = Range("A2").End(xlDown).Row

2-5.セル比較

[2-3]で取り込んだデータに対して、比較対象のセル番地でセルを比較

'　結果シートのセル（判定）の内容を取り込んだデータの件数分複写
With Worksheets("結果").Range(Cells(3, 8), Cells(maxrrow, 10)).FillDown
End With

2-6.終わりメッセージ出力

開始画面（”開始”）へ戻り、終わりメッセージを出力
ここでは、メッセージ出力シートと出力内容を設定可能です。

'　開始画面へ
Sheets("開始").Select

'　終わりメッセージ出力
MsgBox ("終わりました！")

3.まとめ

「検証結果は以下の通り出力」

今回のツールは、ファイルの取り込みとセルの比較処理を分けて行ったため、
検証対象が大量の場合の処理時間を、短縮することができました。

以上となります。
最後までお読みいただき、ありがとうございます。

金　美善
CSVIT事業部 　ID(インテグレーション＆デザイン)部 金　美善
新卒2年目で、金融機関の与信稟議システムの開発に従事。休日は家のDIYをしています。