【誰でも簡単VBA】Excelで請求書自動作成システムを自分で作ろう！初心者向けチュートリアル

Sub CreateInvoice()
    Dim ledgerSheet As Worksheet
    Dim invoiceSheet As Worksheet
    Dim lastRow As Long
    Dim invoiceNo As String
    Dim invoiceDate As String
    Dim subject As String
    Dim dueDate As String
    Dim payee As String
    Dim companyName As String
    Dim postalCode As String
    Dim address As String
    Dim phoneNumber As String
    Dim contactPerson As String
    Dim lastCell As String
    
    ' 台帳シートを設定
    Set ledgerSheet = ThisWorkbook.Sheets("台帳")
    
    ' 台帳シートの最後の行番号を取得
    lastRow = ledgerSheet.Cells(Rows.Count, 3).End(xlUp).Row
    lastCell = ledgerSheet.Range("B" & lastRow).Value
    
    ' 請求書フォーマットのシートをコピーして作成
    ThisWorkbook.Sheets("請求書フォーマット").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set invoiceSheet = ActiveSheet
    
    ' 請求書のシート名をNoに変更
    invoiceNo = "No" & lastCell
    invoiceSheet.Name = invoiceNo
    
    ' 台帳シートから必要な情報を取得
    invoiceDate = ledgerSheet.Range("C" & lastRow).Value
    subject = ledgerSheet.Range("D" & lastRow).Value
    dueDate = ledgerSheet.Range("E" & lastRow).Value
    payee = ledgerSheet.Range("F" & lastRow).Value
    companyName = ledgerSheet.Range("G" & lastRow).Value
    postalCode = ledgerSheet.Range("H" & lastRow).Value
    address = ledgerSheet.Range("I" & lastRow).Value
    phoneNumber = ledgerSheet.Range("J" & lastRow).Value
    contactPerson = ledgerSheet.Range("K" & lastRow).Value
    
    ' 請求書に情報を入力
    invoiceSheet.Range("G1").Value = invoiceNo
    invoiceSheet.Range("G3").Value = invoiceDate
    invoiceSheet.Range("B6").Value = subject
    invoiceSheet.Range("B7").Value = dueDate
    invoiceSheet.Range("B8").Value = payee
    invoiceSheet.Range("B2").Value = companyName
    invoiceSheet.Range("F6").Value = postalCode
    invoiceSheet.Range("F7").Value = address
    invoiceSheet.Range("F9").Value = phoneNumber
    invoiceSheet.Range("F10").Value = contactPerson
    
    ' 台帳シートの作成日に請求書の作成日を設定
 '   ledgerSheet.Range("C" & lastRow + 1).Value = Date
    
    ' メッセージを表示
    MsgBox "請求書が作成されました。"
End Sub

Sub SaveInvoiceAsPDF()
    Dim invoiceSheet As Worksheet
    Dim invoiceDate As Date
    Dim yearFolder As String
    Dim monthFolder As String
    Dim savePath As String
    Dim pdfFileName As String
    Dim ledgerSheet As Worksheet
    Dim lastRow As Long
    
    ' 請求書のシートを設定
    Set invoiceSheet = ThisWorkbook.ActiveSheet
    
    ' 請求書の作成日を取得
    invoiceDate = invoiceSheet.Range("G3").Value
    
    ' 保存先のパスを作成
    yearFolder = Format(invoiceDate, "yyyy")
    monthFolder = Format(invoiceDate, "mm")
    savePath = ThisWorkbook.Path & "\" & yearFolder & "\" & monthFolder
    
    ' 保存先のフォルダが存在しない場合は作成
    If Not FolderExists(savePath) Then
        On Error Resume Next
        MkDir savePath
        On Error GoTo 0
    End If
    ' PDFファイル名を作成
    pdfFileName = savePath & "\" & invoiceSheet.Name & ".pdf"
    
    ' 上書き確認のメッセージを表示
    If MsgBox("Excelファイルを上書き保存しますか？", vbQuestion + vbYesNo) = vbYes Then
        ' PDFファイルとして保存
        invoiceSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard
        MsgBox "PDFファイルが保存されました。"
        
        ' 台帳シートにPDFファイルのリンクを記録
        Set ledgerSheet = ThisWorkbook.Sheets("台帳")
        lastRow = ledgerSheet.Cells(Rows.Count, 3).End(xlUp).Row
  '      ledgerSheet.Range("L" & lastRow).Value = "●"
        ledgerSheet.Hyperlinks.Add Anchor:=ledgerSheet.Range("L" & lastRow), address:=pdfFileName, TextToDisplay:="●"
        Sheets("台帳").Select
    End If
End Sub

Function FolderExists(folderPath As String) As Boolean
    Dim folder As Object
    On Error Resume Next
    Set folder = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath)
    On Error GoTo 0
    FolderExists = Not folder Is Nothing
End Function