会計ソフトの1つに弥生会計というものがあります。その会員（正式にはPAP会員というもの）になると会員専用のページから「帳簿テンプレート」というExcelデータをダウンロードすることができます。
この「帳簿テンプレート」に組み込まれているマクロの内容を解読してみたので記事にしておきます。
弥生PAP会員が使える「帳簿テンプレート」とは
「帳簿テンプレート」とは弥生会計が提供しているExcelファイルで、指定した列に必要な経理データを入力することで会計データを作成することができるようになるものです。
できあがった会計データについては弥生会計にCSV形式でインポートすることができるので
- Excelはあるけど会計ソフトを持っていない人
- Excelにデータ入力するのは抵抗ないけど会計ソフトにデータを入力するのに抵抗がある人
などに利用してもらうといいかもしれません。
帳簿テンプレートは弥生PAP会員の専用ページにログイン後、下記の箇所からダウンロード可能です。
ここをクリックして進んだ先のページの真ん中あたりにデータをダウンロードする箇所があります↓
で、ダウンロードしてファイルを開いた入力画面はこんな感じのものになります↓
Excelのテーブル機能が使われている「表」が標準で作成されていて、テーブル内の黄色の列やオレンジの列の箇所にデータを入力することでAD列に弥生会計のCSVインポートに必要なデータが作成される仕組みになっているようです。
グループ化されているデータを表示するとAD列にこんな感じでデータが作成されています。
AD列のセルに入力されている関数はこんな感じです。CSV（カンマで区切られた値）にするためにやたら「カンマ」が入力されているので見ていると頭がクラクラしてきます。
見やすいかと言われるとお世辞にも見やすいとは言えません。。。。。
使われているExcel関数はセルの内容を結合させるCONCATENATE関数、数値を文字列に変換するTEXT関数くらいなのでシンプルです。
で、この帳簿テンプレートのつくりとして、会計ソフトへインポートする「データの作成」に関してはExcel内で関数などを使用して作成し、その作成したExcel「データの外部へ出力」に関してExcelマクロを使用している仕組み（デスクトップにテキストファイルが作成される）になっているようです。
そしてExcelの画面上部にある「ファイル出力」と記載されたボタンをクリックするとマクロが実行されます↓
帳簿テンプレートのざっくりとした全体の内容はこんな感じでしょうか。
弥生PAP会員が使える「帳簿テンプレート」のマクロ
弥生PAP会員が使える「帳簿テンプレート」に使われているマクロですが内容を確認したところ、下記のようになっていました↓
Sub ファイル出力()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim datFile As String
Dim Path As String
Dim WSH As Variant
Set WSH = CreateObject("WScript.Shell")
Path = WSH.SpecialFolders("Desktop") & "￥"
datFile = Path & "現金出納帳" & Format(Date, "yyyymmdd") & ".txt"
Open datFile For Output As #1
Dim i As Long
i = 6
Do While ws.Cells(i, 30).Value <> ""
Print #1, ws.Cells(i, 30).Value
i = i + 1
Loop
Close #1
MsgBox "仕訳ファイルを出力しました"
End Sub
マクロをざっくり解読した感じとして、４つの処理に分類されているように感じます。具体的には
- 変数宣言
- デスクトップにテキストファイルを作成させる処理
- デスクトップに作成した空のテキストファイルにExcelのデータを書き込む処理
- 処理がすべて完了したらメッセージボックスで「仕訳ファイルを出力しました」と表示させる処理
に分けられると思います。
以下、自分なりの説明になります。
変数宣言
まずは変数の宣言です。
これは特に難しいものではありませんが、下記のハイライト部分で変数の宣言を行っています。
Sub ファイル出力()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim datFile As String
Dim Path As String
Dim WSH As Variant
Set WSH = CreateObject("WScript.Shell")
Path = WSH.SpecialFolders("Desktop") & "\"
datFile = Path & "現金出納帳" & Format(Date, "yyyymmdd") & ".txt"
Open datFile For Output As #1
Dim i As Long
i = 6
Do While ws.Cells(i, 30).Value <> ""
Print #1, ws.Cells(i, 30).Value
i = i + 1
Loop
Close #1
MsgBox "仕訳ファイルを出力しました"
End Sub
まず最初に変数の「WS」にアクティブシート、つまり「ファイル出力」ボタンがあるExcelシートを格納しています。
このシートのことです↓
次に変数「WSH」「Path」「datfile」を使って、「ファイル出力」ボタンがクリックされたときにデスクトップに作成するテキストファイルの名前を定義しています。
変数「i」に関してはおなじみの繰り返しの処理の際によく使われるヤツです。Excelの6行目以降のデータをインポート用のデータとして認識させるために最初に「６」が設定されています。
デスクトップにテキストファイルを作成させる処理
変数宣言の次の箇所でデスクトップにテキストファイルを作成させる処理を行っています。
下記のハイライト部分がそれになります↓
Sub ファイル出力()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim datFile As String
Dim Path As String
Dim WSH As Variant
Set WSH = CreateObject("WScript.Shell")
Path = WSH.SpecialFolders("Desktop") & "\"
datFile = Path & "現金出納帳" & Format(Date, "yyyymmdd") & ".txt"
Open datFile For Output As #1
Dim i As Long
i = 6
Do While ws.Cells(i, 30).Value <> ""
Print #1, ws.Cells(i, 30).Value
i = i + 1
Loop
Close #1
MsgBox "仕訳ファイルを出力しました"
End Sub
今回この帳簿テンプレートのマクロをみて初めて見たのがこの部分です。
CreateObject(“Wscript.Shell”).SpecialFolders(“Desktop”)
ネットで検索してみたところ、この書き方をすることでパソコンのデスクトップフォルダを取得することができるようになるようです。
上のマクロでは変数「WSH」と変数「Path」を組み合わせることでデスクトップまでのパスを定義しています。
最後に変数「datfile」でデスクトップに作成するテキストファイルの「ファイル名」を指定しています。
この書き方をすることで、帳簿テンプレートから仕訳ファイルを出力した日がファイル名に使用されることになります。
具体的には2021年12月25日にこの帳簿テンプレートを使って仕訳ファイルを出力した場合にはデスクトップ上に「現金出納帳20211225」というテキストファイルが作成されます。
もし2022年1月1日にこの帳簿テンプレートを使って仕訳ファイルを出力した場合にはデスクトップ上に「現金出納帳20220101」というテキストファイルが作成されます。
デスクトップに作成した空のテキストファイルにExcelのデータを書き込む処理 Do～Loopを使用
次にデスクトップに作成したテキストファイルを開き、その開いたテキストファイルにExcelのデータを追加していく処理がされています。
テキストファイルを開くには
Open ファイル名 for output as #番号
とするとテキストファイルを開いてそこにデータを書き込むことができるようになります。
15行目のハイライト部分がその部分です。
そして、テキストファイルにデータを書き込む時には
Print #番号, ”書き込みたいデータ”
とするとデータを書き込むことができます。今回のマクロの場合にはExcelのAD列の6行目以降に入力されたデータがテキストファイルに書き込まれることになります。
あとはこの作業を繰り返す必要があります。
今回のマクロでは繰り返し作業をDo～Loopステートメントを使用しています。
この部分がそれになります↓
Sub ファイル出力()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim datFile As String
Dim Path As String
Dim WSH As Variant
Set WSH = CreateObject("WScript.Shell")
Path = WSH.SpecialFolders("Desktop") & "\"
datFile = Path & "現金出納帳" & Format(Date, "yyyymmdd") & ".txt"
Open datFile For Output As #1
Dim i As Long
i = 6
Do While ws.Cells(i, 30).Value <> ""
Print #1, ws.Cells(i, 30).Value
i = i + 1
Loop
Close #1
MsgBox "仕訳ファイルを出力しました"
End Sub
ハイライト行の20行目から23行目の部分ですが、変数「i」は最初「6」が格納されているので、cells(6,30)。すなわちAD6セルに記載されたデータをデスクトップに作成したテキストファイルに追加しています。
ちなみにAD6セルは下記の黄色のセルになります↓
その後、変数「i」に１を足して再定義し、AD列のセルが空欄になるまでテキストファイルにデータを繰り返し追加しつづけます。
具体的には
- AD7セルを調べて空欄でなければAD7セルのデータをテキストファイルに追加して
- AD8セルを調べて空欄でなければAD8セルのデータをテキストファイルに追加して
- AD9セルを調べて空欄でなければAD9セルのデータをテキストファイルに追加して
- AD10セルを〃・・・・・・・
ってな感じでAD列が空欄になるまでテキストファイルにデータを追加し続けます。
もしAD列が空欄になったらそのタイミングで繰り返しの処理が終了します。Do～Loopステートメントから抜けるというイメージでしょうか。
処理がすべて完了したらメッセージボックスで「仕訳ファイルを出力しました」と表示させる処理
最後にメッセージボックスで「仕訳ファイルを出力しました」というメッセージを表示させています。
この部分です。これは特に難しいものではありません。
まとめ
弥生PAP会員が使える「帳簿テンプレート」に使われているマクロの内容を自分なりに解読してみました。
テキストファイルにデータを書き込むやりかたや、デスクトップのパスを取得するやりかたなど、初めてみるものがいくつかありましたが、ネットで検索して理解することができました。
特にデスクトップのパスを取得するやりかたは今後ほかにも応用できそうなので今回記事にしてみて良かったです。
他人が作ったマクロを見て解読するもの勉強になると実感しました。
【本日の近況報告】
スマートスピーカーのgoogle nest miniが2,000円オフのセールになっていたので購入。
4年前くらいにも買っていたのですがその時はgoogle home miniという名前だったと思うのですが、名前変わったんですかね？
現在時刻や天気、今日のスケジュールを確認したり、スケジュールを追加したり、エバーノートへメモをとったり、カップ麺の3分を測ったり等々。なんだかんだと毎日使っているので今では生活に必要なスマートスピーカーです。
こちらの方が低音の音を拾いやすくなったそうでそんなに違いがあるのか検証してみようと思います。
【本日の１曲】
jimmy eat world/last christmas
アサヒスーパードライのCMで使われていたsweetnessという曲が日本でも大ヒットしたバンド、ジミーイートワールド。そのジミーイートワールドがwham！のラストクリスマスをカバーしたのがこちら。アップテンポな打ち込みのアレンジがされたナイスカバー。
1年に一度、このクリスマスの時期にしか、かけることができない１曲。
