またまた、銀行のインターネットバンキングのデータ加工についての記事になります。
銀行のインターネットバンキングのデータは各銀行ごとに出力形式が異なることが多く、今までは顧問先が契約・利用しているインターネットバンキングの出力形式に合わせる形でマクロを作っていました。
が、自分の場合は、「日付」、「取引金額」、「摘要」の３つを使って会計ソフトにインポートするデータを作るので、どんな出力形式のインターネットバンキングのデータであれ、会計ソフトへインポートする際に使用するデータはこの３つだけと決まっています。
そこで銀行のインターネットバンキングの形式がバラバラでもこの３つの項目を取り込むように今回はユーザーフォームを作ってみることにしました。
ユーザーフォームを作って、銀行のインターネットバンキングのデータから会計ソフトのインポートに必要なデータだけを抽出する。
例えばA銀行のインターネットバンキングのデータ形式はこんな感じだったとします↓
１行目に項目欄があって2行目以降に取引データが記載されているパターンです。
摘要欄は「Ｅ列」に記載されています。
で、B銀行のインターネットバンキングのデータ形式はこんな感じだったとします↓
1行目には取引データが記載されておらず、2行目以降に項目データや取引データが記載されるパターン。
摘要欄は「I列」に記載されています。
こんな感じで銀行のインターネットバンキングのデータってCSV形式とかで出力した際にデータ項目の数とか並び順が違うんですよね。
これを今回ユーザーフォームを作ってどんな形式のインターネットバンキングのデータであっても「日付」「取引金額」「摘要」を引っ張れるようにしてみました。
ということで、今回はこんな感じのユーザーフォームを作成しました。
このユーザーフォームの項目欄に日付のデータが記載されている列（A列とかB列とか）やら取引金額が記載されている列やら摘要が記載されている列やらを入力することで会計ソフトのインポートに必要なデータだけひっぱることができます。
例えばさきほどのA銀行のインターネットバンキングのデータの場合は
こんな感じでユーザーフォームに入力して「データの取得開始」ボタンを押すと別シートに「日付」「金額」「摘要」データのみを引っ張る（コピペ）します。
「データの取得開始」ボタン実行後↓
でB銀行のデータの場合にはこんな感じで入力すると
さきほどのA銀行と同じように必要な項目のみコピペしてきます。
「データの取得開始」ボタン実行後↓
こんな感じです。（セルに入力している関数の都合上、D列の摘要の表示内容が変わっていますが無視してください。。。）
あると少しは便利かなと思いまして作ってみました。
一応動画も載せておきます。
「加工」シートに設置したボタンをクリックするとユーザーフォームが立ち上がり、「オリジナル」シートにある銀行のデータからユーザーフォームに入力した行、列にある、「日付」「取引金額」「摘要」データのみを「加工」シートへコピペします。
銀行のインターネットバンキングのデータから会計ソフトのインポートに必要なデータだけを抽出するユーザーフォームの作成とマクロの記述
まずはマクロの作成の前に、ユーザーフォームを作成します。
この箇所からユーザーフォームが作成できます↓
クリックすると空の？白紙の？ユーザーフォームが表示されると思いますのでここに
ツールボックスから「テキストボックス」と「ラベル」を追加して上記の画像のようなユーザーフォームを作っています。
ツールボックスは下記の箇所から表示させます。（大体自動で表示されますが。）
今回はこんな感じで各テキストボックスやボタンを管理・把握しています。
ユーザーフォームの作成が終わったらあとはマクロを割り当てるだけです。
「データの取得開始」ボタンをダブルクリックするとマクロを記載する箇所に画面が切り替わるので下記のコードを記述しています。
Private Sub CommandButton1_Click()
'■「データの取得開始」がクリックされたときに実行するマクロ
Dim data1 As Long '取引開始行
Dim data2 As Long '日付の列
Dim data3 As Long '入金の列
Dim data4 As Long '出金の列
Dim data5 As Long '摘要の列
Dim lastrow As Long
'最終行の取得
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
'ユーザーフォームに入力されたデータを変数に格納する
data1 = meUserForm1.meTextBox1.Value '取引開始行
data2 = Range(meUserForm1.meTextBox2 & "1").Column '日付の列
data3 = Range(meUserForm1.meTextBox3 & "1").Column '入金の列
data4 = Range(meUserForm1.meTextBox4 & "1").Column '出金の列
data5 = Range(meUserForm1.meTextBox5 & "1").Column '摘要の列
With Sheets("加工シート")
'日付のデータをコピー＆ペースト
Range(Cells(data1, data2), Cells(lastrow, data2)).Copy .Range("a1")
'入金額のデータをコピー＆ペースト
Range(Cells(data1, data3), Cells(lastrow, data3)).Copy .Range("b1")
'出金額のデータをコピー＆ペースト
Range(Cells(data1, data4), Cells(lastrow, data4)).Copy .Range("c1")
'摘要のデータを取得
Range(Cells(data1, data5), Cells(lastrow, data5)).Copy .Range("d1")
.Activate
End With
MsgBox "データの取得完了", vbInformation
Unload Me
End Sub
そして同様に「閉じる」ボタンをダブルクリックしてマクロを記載する箇所に画面が切り替えて下記のコードを記述しています。
Private Sub CommandButton2_Click()
'■「閉じる」がクリックされたときに実行するマクロ
Unload Me
End Sub
これで今回のマクロは作成完了になります。
まとめ
顧問先のインターネットバンキングのデータを会計ソフトにインポートする際に毎回、Excelファイルを作っていたんですが、行き当たりばったりで作るせいか、顧問先ごとに作り方がバラバラでメンテナンスもしにくいと感じていました。
で、マニュアルというか統一したやり方を1つ、パターンとして持っておくと今後自分の役にたつかもしれないとようやく思いつき、手始めに今回の記事のユーザーフォームを作ってみた次第です。
ユーザーフォームのいいところはエクセルやマクロの知識がない人でも使うことができるというところだと個人的には思っています。
補足事項
今回作成したマクロはエラーが発生した時の対処がされていません。
例えば、「列」を入力する箇所に「数字」を入力した場合にはエラーが発生してマクロがうまく動かなくなります。
他にも、データを入力せずに空欄のまま「データの取得開始」ボタンを押すとエラーになったりします。
と、こんな感じで不完全なものにはなりますが、これはエラーが発生したらどんどん付け足していけばいいのではないかなと思います。（言い訳）
【本日の近況報告】
「脱マウス 最速仕事術」という本を読んでみました。
仕事でもよくショートカットを使うので、この本を読んで、まだ知らないショートカットを知れたらいいな。くらいの気持ちでした。
読んでみたところ紹介されているショートカットキーのうち半分くらいはすでに知っていて、実際に仕事でも使っているものでしたが、それでもいくつかは知らなかったものもあり、かつ仕事でも使えそうなショートカットキーを知れたので良かったです。
Excelファイルとかをデスクトップに保存するときに今までマウスで場所指定してたんですが、今後はマウスを使わずに保存しようと思います。
この手の本は自分の中では読んでみて何か1つでも自分のプラスになればOKみたいな感じで考えているので割と抵抗なく購入してしまいます。
【本日の１曲】
the snuts / always
2020年以降のUKロックの中では個人的に気に入っているバンド、the snuts(ザ・スナッツ）。
これまで、シングルは結構な枚数のリリースをしていたようですが、今回初めてアルバムが発売されたようです。
その中からシングルでもリリースされていたのが今回の曲で、こういった感じの曲を聴くと自分的にはこれぞUK ROCK。って感じを受けます。
曲から伝わる湿った感じというか、土の匂いというか。天気でいうと「どんよりとした曇り」のような。
アルバム全体としてイイ感じなのでこれは売れますね。（いや、もう売れているか。）
久しぶりにアナログレコードで欲しいと思ったバンドです。
来年あたりフジロックとかのフェスに出そうな感じがします。（もうすでに出てるかもしれませんが。。。）
