CSVデータ連動サンプル


Excel VBAあらかると

 メインフレーム、サーバーといったホスト系コンピュータのトランザクション(取引データなど)をエクスポートしてパソコンでデータを扱う場合は、CSV(Comma Separated Value)形式がよく用いられます。
CSV形式とは、行(レコード)を改行で区切り、列(項目)をコンマ(comma)で区切ったファイルでテキスト表現の1つです。
CSV形式は、Excelでダイレクトにインポートすることが可能でそのままExcel上で表として扱うことができます。
ここでは、サーバー側から特定条件で出力されたCSV形式のデータを基にExcel側で無条件に読み込み、条件変換しながら別ブックのシートへリストを作成する仕組みを紹介します。
いつもながらの、独断と偏見でのアプローチとなりますので理解の上でテースティングをお願いします。また、不明な点はホームのメールからお問い合わせください。


 連動シナリオ
外部システムから重宝きちょうくん(複式簿記)へ仕訳連動を前提としたサンプルです。このサンプルは実際使われています。
資材購入した請求額は、毎月普通預金から一括自動振替される。その一括振替額には複数の費目が含まれており、月次毎請求書から取引明細を単位とした費目へ振分する労力負担が非常に大きい。
そこで、購入先から年度末に会計期間内に購入した明細のデータ還元があることを知り、その還元データを用いて年次一括費目振替を行うこととした。その方向から毎月の振替金額については、雑資産勘定を使って一時的に経理処理することにした。その仕組みが今回のシナリオである。

  1. CSVファイルの項目   ファイル名:rendo.csv     (17項目)
    取扱支店、利用者コード、取引日、品目コード、品名、決済コード、数量、数量符号、単価、金額、金額符号、消費税、消費税符号、取引金額(金額+消費税)、取引金額符号、消費税区分、形態

    CSV形式サンプリングデータ


  2. 連動ブック側の項目と変換条件
    ブック側 CSV側
    A列 取引日 取引日
    取引日の月
    費目 きちょうくんの勘定科目  (入力、費目毎の連動仕訳額の基準となる)
    費目名 きちょうくんの勘定科目名称 (費目が検索値、VLOOKUP関数を定義)
    E、F 品目CD、品名 品目コード、品名
    決済 決済コード
    ※取引計 金額+消費税
    ※消費税額 消費税  消費税区分:35(税込)の場合は取引計*5/105の計算値
    ※消費税対価額 金額 (消費税区分:35(税込)の場合は取引計−消費税額の計算値)
    ※数量 数量 (数量は百倍されているので /100の値 (小数点以下の関連))
    単価 単価 (単価は百倍されているので /100の値 (小数点以下の関連))
    ※金額 金額
    ※消費税 消費税
    消費税区分 消費税区分
    形態 0:当用 1:予約
     ※印の項目は各々のCSV側の符号を判断(整数の時+、負数の時−)し正負の符号計算を行う。

 Excel連動メインメニュー
@連動は、「連動データ作成」ボタンクリックによる。また、連動するCSVファイルは、FD装置: A:rendo.csv に記録されている。
A消費税処理区分は入力となり、税抜経理方式なのか税込経理方式かは判別するために用意している。
B連動するブック名は「EX連動.xls」シート名は「取引振分」となる。


 マクロコード
 標準モジュールへ記述
Sub j01_rendo()

  Dim lin As Integer
  Dim i As Integer

'画面更新を抑制する
  Application.ScreenUpdating = False

'シート「取引振分」を選択
  Sheets("取引振分").Select
'シート「取引振分」の最終行を取得
  lin = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'シート「取引振分」の明細行の初期化 (1行目はタイトル行となるのでセルA2から開始)
'()内の P" & lin は最終セル列の最終行を意味する
  If lin > 1 Then ActiveSheet.Range("A2:P" & lin).Clear

'CSV形式データの読み込み  ここではExcel読みしているがOpenステートメントで読む事もできる ※1
  Workbooks.Open Filename:="A:rendo.csv"

'連動データの最終行を取得
  lin = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'メニューの件数セルに最終行(連動件数)を代入
  Workbooks("EX連動.xls").Sheets("menu").Cells(9, 7) = lin

'CSV連動ファイルからシート「取引振分」各項目への代入  
  For i = 1 To lin
    With Workbooks("EX連動.xls").Sheets("取引振分")
        .Cells(i + 1, 1) = Cells(i, 3)
        .Cells(i + 1, 5) = Cells(i, 4)
        .Cells(i + 1, 6) = Cells(i, 5)
        .Cells(i + 1, 7) = Cells(i, 6)
        .Cells(i + 1, 12) = Cells(i, 9) / 100
        .Cells(i + 1, 7) = Cells(i, 6)
        .Cells(i + 1, 15) = Cells(i, 16)

        If Cells(i, 8) = "+" Then
          .Cells(i + 1, 11) = Cells(i, 7) / 100
          .Cells(i + 1, 13) = Cells(i, 10)
          .Cells(i + 1, 8) = Cells(i, 14)
          .Cells(i + 1, 9) = Cells(i, 12)
          .Cells(i + 1, 14) = Cells(i, 12)
        Else
          .Cells(i + 1, 11) = Cells(i, 7) / 100 * -1
          .Cells(i + 1, 13) = Cells(i, 10) / 100 * -1
          .Cells(i + 1, 8) = Cells(i, 14) * -1
          .Cells(i + 1, 9) = Cells(i, 12) * -1
          .Cells(i + 1, 14) = Cells(i, 12) * -1
        End If

        If Cells(i, 17) = 0 Then
          .Cells(i + 1, 16) = "当用"
        Else
          .Cells(i + 1, 16) = "予約"
        End If

        If Cells(i, 16) = 35 Then
          .Cells(i + 1, 9) = Int(Cells(i, 14) * 5 / 105)
        End If

        .Cells(i + 1, 10) = .Cells(i + 1, 8) - .Cells(i + 1, 9)

    End With

  Next i

  ActiveWindow.Close

'費目名(セルD列)と月(セルB列)を求める関数式をシートに埋め込む
'VBAで求めることができるがデータ追加を許しているので関数式で対応する 最終行に+10している理由は追加余力である
  Range("D2").Select
  ActiveCell.FormulaR1C1 = _
   "=IF(ISERROR(VLOOKUP(RC[-1],勘定科目!R1C1:R35C2,2,FALSE)),"""",VLOOKUP(RC[-1],勘定科目    !R1C1:R35C2,2,FALSE))"
  Selection.AutoFill Destination:=Range("D2:D" & lin + 10)

  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=MID(RC[-1],5,2)"
  Selection.AutoFill Destination:=Range("B2:B" & lin + 10)

  Sheets("menu").Select

'画面更新抑制を解除
  Application.ScreenUpdating = True

End Sub

※1 Open命令を使った場合のサンプル  (WebMambowがよく使うロジックです)
'レコードバッファの定義
Dim fbuf(17) As String
’「開く」ダイヤログを表示して一覧からCSVファイルを選択する
Flopen = Application _
.GetOpenFilename("CSV(カンマ区切り)(*.csv), *.csv")
'選択値の判断
If Flopen = False Then
  MsgBox "未エントリー "
  Exit Sub
Else
  MsgBox "選択したCSVファイル : " & Flopen
End If

'指定したCSVファイルを開く
Open Flopen For Input As #1

'CSV最終レコード( EOF(1) )まで切り返す
Do Until EOF(1)
  Input #1, fbuf(1), fbuf(2), fbuf(3), fbuf(4), fbuf(5), _
  fbuf(6), fbuf(7), fbuf(8), fbuf(9), fbuf(10), _
  fbuf(11), fbuf(12), fbuf(13), fbuf(14), fbuf(15), _
  fbuf(16), fbuf(17)
    ・
    ・   代入文が挿入される
    ・
    ・

Loop

'CSVファイルを閉じる
Close #1

このロジックにエラー処理を加えるとさらに良くなります。


 仕訳連動システムに組み込まれている関数式の一部の紹介
 データ連動したリストから費目毎に集計するシートです。図の中に「03」と表示されているセルがあります。ここは月を入力するセルです。月を入力するとその値を基準に費目毎に集計するように仕組んでいます。
合理性、見栄えの観点からすれば列を月にした表を作れば一目で把握することができますが、関数あらかると「配列」シリーズで記述の通り再計算に非常に時間を要し運用困難な状態に陥ります。
(VBAで構築すれば問題は回避できますが.........)

下記がその関数式です。税抜仕訳と税込仕訳を判断し費目毎集計結果を表示します。

=IF(menu!$F$15=9,SUMPRODUCT((取引振分!$B$2:$B$28=$F$6)*(取引振分!$C$2:$C$28=A7)*(取引振分!$H$2:$H$28)),SUMPRODUCT((取引振分!$B$2:$B$28=$F$6)*(取引振分!$C$2:$C$28=A7)*(取引振分!$J$2:$J$28)))

※ menu!$F$15=9  9:税込仕訳です。  その他の説明は省略します。

以上