【VBA ほぼ変更不要】売上明細からPDF請求書を自動作成

2018年2月14日

エクセルで売上明細や請求明細を管理しているとき、自動で請求書を作れると非常に楽ですよね？

そこで、今回は、請求明細から請求書を全自動で作る方法を紹介します。

VBAソースの修正は最小限で済む作りなので、VBA初心者に特におすすめです。

請求書を作ろうとすると、どうしても関数だけでは実現できないので、必要に応じてVBAを使います。

請求書を作る全体的な流れ

この記事では、以下の流れで請求書を作っていきます。

Step1：Noに連動して、請求書を自動作成

請求No、取引先Noなどの「No」を固定したときに、その「No」に該当する請求書を自動で作れる仕組みを作る

Step2：Noを変化させPDF出力

「No」を次々と変更し、変更するたびにPDF出力　を繰り返す仕組みを作る

以下、順番に解説していきます。

Step1：Noに連動して、請求書を自動作成

最初に、Noを１つ固定し、そのNoに対する請求書を自動作成していきます。
請求書レイアウトの変更に柔軟に対応できるように、関数を使って自動作成をする仕組みを作ります。

イメージ的には、次の図のような感じです。
No欄に「１」と入力すると、そのNoに対応する取引先名と請求明細が表示されるようにします。

取引先名はvlookup関数を使って入力

取引先名は、vlookup関数を使うと簡単に入力できます。

具体的には、次のような数式を入力します。

請求明細はvlookup関数では転記できない

次に請求明細です。

請求明細は、（取引先名と同じように）vlookup関数を使って転記をすることはできません。
なぜなら「1つ」のNoに対して「複数」の請求明細があるからです。

vlookup関数を使うと、最初の1行しか転記することができないので、今回のような転記には使えないのです。

ユーザー定義関数arrayvlookup関数を使って転記

条件に合う複数行の転記を行う方法には様々な方法があります。
今回はarrayvlookup関数というユーザ定義関数を使います。

この関数を使うと、vlookup関数と同じような感覚で、複数行の転記をすることができます。

同じように、内容だけでなく、納品日、数量、単価もユーザー定義関数であるarrayvlookup関数を使って入力します。

これで、B1セルの「No」を変えることで、そのNoに対応する請求書を表示できるようになりました。

Step2：Noを変化させPDF出力

Step1では、「No」を入力すれば、そのNoに対応する請求書を作れるようになりました。

この状態でも、先ほどの動画のように

と繰り返していけば、請求書を次々とPDF化することができます。

でも、せっかくですから、この作業もVBAを使って自動化してしまいましょう。

自動化すると、冒頭の動画のようにマクロを実行するだけで、請求書のPDFを自動作成することができるようになります。

VBAのソースコード

今回の、請求書PDFの自動作成マクロのソースコードは次のとおりです。

下記ソースコードを、標準モジュールに貼り付けてください。

Sub SeikyuusyoSakusei()
  Dim iSheet As Worksheet
  Set iSheet = ThisWorkbook.Worksheets("請求明細")
  
  Dim iNameCol As Long
  iNameCol = 2

  Dim oCell As Range
  Set oCell = ThisWorkbook.Worksheets("請求書ひな形").Range("B1")
  oCell.Value = ""
  
  Dim tmplSheet As Worksheet
  Set tmplSheet = ThisWorkbook.Worksheets("請求書ひな形")
  
  Dim iY As Long
  iY = 2
  
  Dim oFilename As String
      
  While iSheet.Cells(iY, 1).Value <> ""
    If oCell.Value <> iSheet.Cells(iY, 1).Value Then
      oCell.Value = iSheet.Cells(iY, 1).Value
      oFilename = ThisWorkbook.Path & "¥" & oCell.Value & _
                  "_" & iSheet.Cells(iY, iNameCol).Value & _
                  "_" & Format(Date, "yyyymmdd") & "_請求書.pdf"
      tmplSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=oFilename
    End If
    iY = iY + 1
  Wend
End Sub

'以下arrayvlookup関数の定義です。使わない場合にはここから下はコピペ不要です
Function ARRAYVLOOKUP(Key As Variant, R As Range, C As Long, _
                      Optional T As Boolean = True) As Variant
'検索の型はTRUEを指定しても動作は変わらないことに注意！
  Dim ret As Variant

  Dim maxretY As Long

  If TypeName(Application.Caller) = "Range" Then
    maxretY = Application.Caller.Rows.Count
  Else
    maxretY = 1
  End If
  
  ReDim ret(1 To maxretY, 1 To 1) As Variant
  
  Dim targetR As Range
  Set targetR = Intersect(R, R.Worksheet.UsedRange)

  Dim Data As Variant
  Data = targetR
  
  Dim y As Long
  Dim retY As Long
  retY = 1
 
  For y = LBound(Data) To UBound(Data)
    If Data(y, 1) Like Key Then
      ret(retY, 1) = Data(y, C)
      retY = retY + 1
      If retY > maxretY Then
        Exit For
      End If
    End If
  Next
    
  If retY > 1 Then
    For y = retY To maxretY
      ret(y, 1) = ""
    Next
  Else
    ret(1, 1) = CVErr(xlErrNA)
  End If
  
  ARRAYVLOOKUP = ret
End Function

33行目以降（Function～EndFunction）は、このページの前半で紹介したarrayvlookup関数を使えるようにするためのソースコードです。

詳細は、複数行の値を返すvlookup関数をVBAのユーザー定義関数で作るをご覧ください。

プログラムの解説

今回、新たに作成したのは上半分（Sub～End Sub）の部分のみです。
以下、その部分について解説していきます。

プログラムで使う変数の定義

  Dim iSheet As Worksheet
  Set iSheet = ThisWorkbook.Worksheets("請求明細")
  
  Dim iNameCol As Long
  iNameCol = 2

  Dim oCell As Range
  Set oCell = ThisWorkbook.Worksheets("請求書ひな形").Range("B1")
  oCell.Value = ""
  
  Dim tmplSheet As Worksheet
  Set tmplSheet = ThisWorkbook.Worksheets("請求書ひな形")
  
  Dim iY As Long
  iY = 2

今回のプログラムで使う変数を定義しています。
それぞれの変数が指す位置を図解すると次のようになります。

変数の命名規則は適当な部分もありますが、

• 入力関連：最初に「i」をつける
• 出力関連：最初に「o」をつける

を意識して名前をつけています。

繰り返し処理

  While iSheet.Cells(iY, 1).Value <> ""



　　　　（メインロジック）



    iY = iY + 1
  Wend

今回のVBAマクロのメインロジックです。

（元々変数iYには「2」が格納されているので）最初にiSheet（請求明細シート）の2行目、1列目のセルが空欄かどうかを調べます。

空欄でなければ「（メインロジック）」の部分の処理を行います。
その後、iYに1を足して、同じ処理を繰り返します。

メインロジック

  If oCell.Value <> iSheet.Cells(iY, 1).Value Then


　（PDF出力処理）


  End If

次にメインロジックです。

「iSheet.Cells(iY,1).value」で、現在処理中の行の1列目の値（＝No）が得られます。
それを「oCell.Value」（＝請求書ひな形シートのB1セルの値）と比較します。

そして、両者が一致しない場合のみPDF出力処理に進みます。

逆に、両者が一致するということは、すでに、その「No」は処理済みということです。ですから、何も処理せずに次に進みます。

PDF出力処理

最後にPDF出力処理です。

  oCell.Value = iSheet.Cells(iY, 1).Value

まず、「このNoまで処理したよ！」という目印にするため、「oCell」（＝請求書ひな形シートのB1セル）に「iSheet.Cells(iY,1).value」（現在処理中の行のNo）を格納します。

      oFilename = ThisWorkbook.Path & "¥" & oCell.Value & _
                  "_" & iSheet.Cells(iY, iNameCol).Value & _
                  "_" & Format(Date, "yyyymmdd") & "_請求書.pdf"

次に「oFilename」に、PDF出力する先のファイル名を格納します。

今回は、下記のデータを、つなげたものをファイル名としています。

ソース内の記述	意味
ThisWorkbook.Path	このエクセルブックがあるパス
"¥"	※「¥」マークです。画面上は「斜め線」に見えているかもしれませんが、お手元のPCにコピペすると「¥」マークに変わります。
oCell.Value	請求書No（あるいは取引先No)
"_"
iSheet.Cells(iY, iNameCol).Value	取引先名
"_"
Format(Date, "yyyymmdd")	今日の日付を「年4桁」「月2桁」「日2桁」形式で表したもの
"_請求書.pdf"

実際には
「C:¥My Documents¥1_株式会社アクティ_20180214_請求書.pdf」
「C:¥My Documents¥2_大中産業株式会社_20180214_請求書.pdf」
という感じのファイル名になります。

  tmplSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=oFilename

tmplSheet（「請求書ひな形」シート）の内容を使って、oFilename（指定したファイル名）という名前のPDFファイルを作成します。

補足：Noが請求書に印刷されて邪魔な場合

現段階では見やすいように「請求書ひな形」シートの上に「No」の入力欄を作っています。そのため、このままの状態で請求書を印刷すると、左上に「No」が表示されてしまいます。

もし「No」を表示させたくない場合には、「No」入力欄を別シートに移動させます。
たとえば「作業用シート」という名前のシートを新たに作って、そのシートに「No」入力欄を移動させてください。

また、VBAのソース中、下記の部分も、移動先に合わせて変更してください。

  Set oCell = ThisWorkbook.Worksheets("請求書ひな形").Range("B1")

こうすることで、PDFファイルに「No」が表示されなくなります。

まとめ

請求書ひな形への転記作業をすべて（ユーザー定義関数を含む）関数化することで、PDF出力処理のVBAが、かなりシンプルになりました。

また、今回紹介した、VBAのソースは、請求書のレイアウトが変わったり、全然別の帳票を作る場合でも、ほとんど変更せずに使い回すことができます。

ぜひ、ご活用ください！