前回までのあらすじ
前回は、請求書出力自動化の案件をなんとかもぎ取り、僕はその仕事を受けることになった。テンプレートファイルの作成と環境整備までが終わったところである。
今回は、おおまかなスケジュールの目安と作業の方向性を確認する。前準備として、本ページに予定やこれまでの作業を記録する。
このサイト普通に公開しちゃっていいのか？？そんなのは知らん。というか後で消します。
とりあえずスケジュール案
全9回中のこり6回
- 8/26(月) 第4回 スケジュール確認、方向性の確認
- 8/27(火) 第5回 要件定義の確認、作業、チェック
- 9/3(火) 第6回 プロトタイプ完成、チェック
- 9/5(木) 第7回 テスト、納品
- 9/6(金) 第8回 なし
- 9/9(月) 第9回 なし
割と適当。適宜修正したまえ。僕の性質上締め切りには結構ギリギリになりがちなので、早めを意識している。
また、チェックとかテストとかはよく分からんけど書いてみただけ。
プログラムの方向性
大体のフローを箇条書きで書いていくよ。適宜修正。
前提として
とりあえずtemplateはローカルに置いておく。TEMPLATEディレクトリに入れるのは分かりやすくするため。ディレクトリ内のファイルが名前で並ぶならlistもディレクトリに入れた方が良いかもしれない。
使いにくそうなら変更するが、考えたディレクトリ構造は以下の通り。
|
|
|-- OUTPUT
| | |--aaa請求書.pdf
| | |--bbb請求書.pdf
| | |--ccc請求書.pdf
| | |--ddd請求書.pdf
| |
| |--EXCEL
| | |--aaa請求書.xlsx
| | |--bbb請求書.xlsx
| | |--ccc請求書.xlsx
| | |--ddd請求書.xlsx
|
|-- TEMPLATE
| |--template.xlsx
|
|-- program.xlsm(出力自動化ツール.xlsm)
|-- list1.xlsx
|-- list2.xlsx
|-- list3.xlsx
以下フロー。出来るだけ細かく書いてみる。
1. ファイルを用意する。構成は上の通り。
- 構成は後から変更するかもしれないが、設定したとおりの構成になっているか確認。
- program.xlsmからtemplate.xlsmとlist.xlsmを読み込む。
- templateとoutputについては別途ディレクトリを用意。
2. program.xlsxのボタンを押す。
- ボタンを押すとexplorerが開く。
- 表示されるファイルはxlsm、xlsxとかだけにする。
- ファイルは複数選択できる。選択したファイルは配列にして返す。
- 何も選択されなかったらエラーをmsgboxで表示して終了。
- TEMPLATE/template.xlsxが存在しなかったらエラーを出して終了。
- OUTPUTディレクトリはカレントディレクトリ内に存在しなかったら作成。
- OUTPUT/EXCEL,OUTPUT/PDFも、それぞれ存在しなかったら作成。
3. list1.xlsxを読み込む。
- 複数選択された場合には順次実行。
- list内のA列を2行目から走査し、空白になったら終わりとみなす。
- どの列にどの項目があるかはプログラム内に変数として指定。文字列検索はしない。
- 項目の位置の変数はボタンを使ってユーザが設定できるようにしても良いが、項目の内容やレイアウトが頻繁に変わるかどうかで決める。
4. template.xlsxを複製する。
- 複製したらOUTPUT/EXCELに会社名をファイル名にして保存。(例) 株式会社あいうえお請求書.xlsx
- 同じ名前のファイル名があったらエラーを出す。または会社名以外の標識を含める。情報が欠落するため、連番にはしたくない。
5. list1.xlsxの内容を複製したファイルに書き込んで保存。
6. excelからpdfを出力し、OUTPUT/PDFに保存。
- 保存してから書き込むか、書き込んでから保存するか。
変数の命名規則とか
使う変数とか決めとく
- 読み込む行と列、書き込む行と列は変数にしておく。
- list中の位置は^[a-zA-Z]*ColFrom(int型)で、書き込む先は^[a-zA-Z]*CellTo(string型)とする。^[a-zA-Z]*ColFromは数字で指定するが、^[a-zA-Z]*CellToはstringで"B2"のように指定する。
- string型でセルを指定しようとしたけどCellsが使えなかったからRangeを使った。よく分からん。
- program.xlsm中のstatusなどの一覧はlistRow,listColで書き込む位置を指定。
- list.xlsx内の読み込みはtargetRowと各コンテンツの行を表す変数dateColFromなどで指定。
- 8行あるcontentsの読み込み元の行はcontent[2][8]の二次元配列でとっておいた。
- 出力先のファイル名はoutputNameという変数を使う。これに拡張子を足す。outputNameは今のところ$(会社名)+"請求書"でやってる。
- 変数名はいずれもキャメルケースとする。(一部の一時変数を除く)
イメージ的には
なんとなくのイメージ図を載せておく。
本番ファイルがどんな感じだったかほとんど覚えていなかったため、僕の曖昧な記憶にあったなんとなくの構成で表を作る。
本番はもっと複雑な感じがあったので、まぁざっくりとしたイメージとしての図と思え。
ここでTEMPLATEがないとエラーになる。
| date | company | name | content1-1 | content2-1 | content3-1 | content1-2 | content2-2 | content3-2 | statement |
|---|---|---|---|---|---|---|---|---|---|
| 2022/11/11 | company1 | name1 | たわし | メロン | 合計 | 250 | 2000 | 10,000 | 輝く夜へようこそ！ |
| 2024/7/19 | company2 | name2 | すいか | 蛇口 | 合計 | 150 | 2400 | 10,000 | それでも地球は回っている。 |
| 2028/4/24 | company3 | name3 | とけい | ストロー | 合計 | 1050 | 1300 | 300,000 | お支払いはキャッシュで。 |
また、dateがおかしくなってることにも注目!
一回コード書いてみたよ
とりあえず書いてみた奴だけ。
Function selectfiles() As Variantファイルを選択させる関数 Dim flist As Variant flist = Application.GetOpenFilename(filefilter:="Excel Files, *.xlsx;*.xls;*.xlsm", MultiSelect:=True) If IsArray(flist) Then selectfiles = flist 選択されたファイルを配列で返す Else MsgBox "Error: Nothing Selected"なにも選択されなかったらエラー End End If End Function Sub main() メイン関数 Dim fileList As Variant Dim sheetName As String Dim listRow As Integer, listCol As Integer Dim contentColFrom(2, 8) As Integer Dim targetRow As Integer Dim FSO As Object Dim companyCellTo As String Dim dateCellTo As String Dim nameCellTo As String Dim priceCellTo As String Dim contentCellTo(2, 8) As String Dim statementCellTo As String sheetName = "Sheet1" listCol = 6 listRow = 1 dateColFrom = 1 companyColFrom = 2 nameColFrom = 3 contentColFrom(1, 1) = 4 リストファイル中の項目の位置を指定 contentColFrom(1, 2) = 5 contentColFrom(1, 3) = 6 contentColFrom(1, 4) = 7 contentColFrom(1, 5) = 8 contentColFrom(1, 6) = 9 contentColFrom(1, 7) = 10 contentColFrom(1, 8) = 11 contentColFrom(2, 1) = 12 contentColFrom(2, 2) = 13 contentColFrom(2, 3) = 14 contentColFrom(2, 4) = 15 contentColFrom(2, 5) = 16 contentColFrom(2, 6) = 17 contentColFrom(2, 7) = 18 contentColFrom(2, 8) = 19 statementColFrom = 20 companyCellTo = "B4"書き込む先の位置を指定 dateCellTo = "G2" nameCellTo = "C7" priceCellTo = "C8" statementCellTo = "B22" contentCellTo(1, 1) = "B12" contentCellTo(1, 2) = "B13" contentCellTo(1, 3) = "B14" contentCellTo(1, 4) = "B15" contentCellTo(1, 5) = "B16" contentCellTo(1, 6) = "B17" contentCellTo(1, 7) = "B18" contentCellTo(1, 8) = "B19" contentCellTo(2, 1) = "F12" contentCellTo(2, 2) = "F13" contentCellTo(2, 3) = "F14" contentCellTo(2, 4) = "F15" contentCellTo(2, 5) = "F16" contentCellTo(2, 6) = "F17" contentCellTo(2, 7) = "F18" contentCellTo(2, 8) = "F19" Set FSO = CreateObject("Scripting.FileSystemObject") fileList = selectfiles()各フォルダが存在していなければ作成 If Not FSO.folderexists("OUTPUT") Then MkDir ("OUTPUT") End If If Not FSO.folderexists("OUTPUT/EXCEL") Then MkDir ("OUTPUT/EXCEL") End If If Not FSO.folderexists("OUTPUT/PDF") Then MkDir ("OUTPUT/PDF") End If If Not FSO.FileExists("TEMPLATE/template.xlsx") Thentemplateが存在していなければエラー終了 MsgBox "template.xlsx doesn't exist." End End If While Cells(listRow, listCol) <> ""program.xlsx中の一覧の書き込み開始位置を決める listRow = listRow + 1 Wend For Each filePath In fileList選択されたファイルそれぞれに対して実行 Dim fn As String Dim wbFrom As Workbook Dim wbTo As Workbook Dim outputName As String fn = Dir(filePath) Set wbFrom = Workbooks.Open(filePath) Set wsFrom = wbFrom.Worksheets(sheetName) targetRow = 2 While wsFrom.Cells(targetRow, 1).Value <> "" outputName = wsFrom.Cells(targetRow, companyColFrom) & "請求書"生成ファイルの名前を決定 FileCopy "TEMPLATE/template.xlsx", "OUTPUT/EXCEL/" & outputName & ".xlsx"テンプレートを複製してOUTPUTに保存 Set wbTo = Workbooks.Open("OUTPUT/EXCEL/" & outputName & ".xlsx")書き込み先を指定 ThisWorkbook.Worksheets(1).Cells(listRow, listCol - 1) = wsFrom.Cells(targetRow, companyColFrom)program.xlsx内の一覧に書き込む ThisWorkbook.Worksheets(1).Cells(listRow, listCol - 2) = "listed" ThisWorkbook.Worksheets(1).Cells(listRow, listCol) = fn ThisWorkbook.Worksheets(1).Cells(listRow, listCol + 1) = outputName wbTo.Worksheets(1).Range(dateCellTo) = wsFrom.Cells(targetRow, dateColFrom)書き込む wbTo.Worksheets(1).Range(companyCellTo) = wsFrom.Cells(targetRow, companyColFrom) wbTo.Worksheets(1).Range(nameCellTo) = wsFrom.Cells(targetRow, nameColFrom) wbTo.Worksheets(1).Range(priceCellTo) = wsFrom.Cells(targetRow, contentColFrom(2, 8)) wbTo.Worksheets(1).Range(statementCellTo) = wsFrom.Cells(targetRow, statementColFrom) wbTo.Worksheets(1).Range(contentCellTo(1, 1)) = wsFrom.Cells(targetRow, contentColFrom(1, 1)) wbTo.Worksheets(1).Range(contentCellTo(1, 2)) = wsFrom.Cells(targetRow, contentColFrom(1, 2)) wbTo.Worksheets(1).Range(contentCellTo(1, 3)) = wsFrom.Cells(targetRow, contentColFrom(1, 3)) wbTo.Worksheets(1).Range(contentCellTo(1, 4)) = wsFrom.Cells(targetRow, contentColFrom(1, 4)) wbTo.Worksheets(1).Range(contentCellTo(1, 5)) = wsFrom.Cells(targetRow, contentColFrom(1, 5)) wbTo.Worksheets(1).Range(contentCellTo(1, 6)) = wsFrom.Cells(targetRow, contentColFrom(1, 6)) wbTo.Worksheets(1).Range(contentCellTo(1, 7)) = wsFrom.Cells(targetRow, contentColFrom(1, 7)) wbTo.Worksheets(1).Range(contentCellTo(1, 8)) = wsFrom.Cells(targetRow, contentColFrom(1, 8)) wbTo.Worksheets(1).Range(contentCellTo(2, 1)) = wsFrom.Cells(targetRow, contentColFrom(2, 1)) wbTo.Worksheets(1).Range(contentCellTo(2, 2)) = wsFrom.Cells(targetRow, contentColFrom(2, 2)) wbTo.Worksheets(1).Range(contentCellTo(2, 3)) = wsFrom.Cells(targetRow, contentColFrom(2, 3)) wbTo.Worksheets(1).Range(contentCellTo(2, 4)) = wsFrom.Cells(targetRow, contentColFrom(2, 4)) wbTo.Worksheets(1).Range(contentCellTo(2, 5)) = wsFrom.Cells(targetRow, contentColFrom(2, 5)) wbTo.Worksheets(1).Range(contentCellTo(2, 6)) = wsFrom.Cells(targetRow, contentColFrom(2, 6)) wbTo.Worksheets(1).Range(contentCellTo(2, 7)) = wsFrom.Cells(targetRow, contentColFrom(2, 7)) wbTo.Worksheets(1).Range(contentCellTo(2, 8)) = wsFrom.Cells(targetRow, contentColFrom(2, 8)) wbTo.ExportAsFixedFormat Type:=xlTypePDF, Filename:="OUTPUT/PDF/" & outputName & ".pdf"PDFで保存する targetRow = targetRow + 1 ThisWorkbook.Worksheets(1).Cells(listRow, listCol) = fn Call wbTo.Close(savechanges:=True) ThisWorkbook.Worksheets(1).Cells(listRow, listCol - 2) = "done" listRow = listRow + 1 Wend Call wbFrom.Close(savechanges:=False) Next Set wsFrom = Nothing Set wbFrom = Nothing MsgBox "ALL DONE. HAVE A GOOD DAY!!" End Sub
参考文献
VBAについて調べたときのサイトをメモ程度に書いておくよ
- 【VBA入門】Cellsでセルを指定(変数などRangeとの使い分けも解説)
- Excel VBAでファイルを開く
- 他のexcelファイルを開く
- フォルダの存在をチェックする
- 【Excel VBA】ファイルの存在チェック方法(FileSystemObject FileExists)
- VBAでワークブックのコピーを保存する
- ファイルをコピーするFileCopyステートメント
- VBAでPDFで保存する方法。見た目を調整するコツを解説!
- VBA Concatenate Text Strings Together
考えるべきこと
- テンプレートファイルの位置。インターネットに置くのは良くないかもしれない。結局プログラムファイルと同梱するなら同ディレクトリ内に配置でも変わらない。
- ディレクトリの構成。リストファイルがどのように与えられるかを調べる。
- リスト読み込みをどうするか。前にやった業務では表をプログラムxlsm内に貼り付けてボタンを押す形だったが、コピペは普通に面倒。
- excelファイルを出力するか。pdfの方で間違いが見つかっても修正しやすい。無料ユーザはpdf編集やりづらいはず。知らんけど
- [解決したい問題]他ファイルから値を読み込むときわざわざ開く。どうにかならんかね。普通にopen/closeの時間が無駄な気がする。
- msgboxの文字の大きさやウィンドウサイズを変えられない。自作UIを書けば行けるかもしれないが、そこまでやるべきか不明。
- 処理がすべて終わった後にexplorerでOUTPUT/PDFを自動で開くかどうか。好みの問題?
- 請求金額の欄はcontent欄の最後の合計がそのまま入ると思うが、合計が最終行にくる保証がない。現時点では請求金額はlist.xlsxのcontent8-2を参照しているが、8行埋まらないならoutputファイルのcontent欄を上から走査して最後の行から取ってくるみたいなことをしないといけない。
- contentが8行を超える時どうするか。
- [未解決問題]日付をセルに書き込むときなんか変になる。
- 日付の形式がYYYY/MM/DDなのかYYYY年MM月DD日なのか。また、list.xlsxの書式とoutputの書式が異なるか。
- [要解決問題]同じ社名があったときにファイル名が衝突し、正しく保存されない。エラーが出なくても、上書きされる可能性。
次回の予定
特に考えてない。なんか思いついたら書いとく。
続きです。9/7
】の後に改行を入れる。変数などの定義は適宜調整。
Dim insertIndex As Integer
Dim result As String
Dim inputString As String
insertIndex = InStr(wsFrom.Cells(targetRow, nameColFrom), "】")
inputString = wsFrom.Cells(targetRow, nameColFrom)
result = Left(inputString, insertIndex) & vbLf & Right(inputString, Len(inputString) - insertIndex)
wbTo.Worksheets(1).Range(nameCellTo) = result
↑のコードは、】の後に改行を入れるコード。】がない場合はエラーになるので、エラー処理を入れる。入れなくてもいい
改行はvbLfを使う。他にもvbCr、vbCrLfがある。vbLfは改行、vbCrはキャリッジリターン、vbCrLfは改行とキャリッジリターンの組み合わせ。