第2回:「請求書入力フォームで入力したデータをPDF出力し、請求書DBに転記する」
第2回のコード
Option Explicit Sub MakeInvoicePdf() '変数定義------------------------------------------------------------ Dim company As String, staff As String, subject As String Dim demandNo As Integer, demandDate As Date Dim sumOfMoney As Currency, dueDate As Date Dim productNameInDetail As Variant Dim ws As Worksheet, fileName As String, bookNameDB As String '変数定義終了________________________________________________________ Set ws = Worksheets("請求書") '変数取得処理-------------------------------------------------------- company = ws.Range("A4").MergeArea.Item(1) '会社名 staff = ws.Range("B6").MergeArea.Item(1) '担当者 subject = ws.Range("B9").MergeArea.Item(1) '件名 demandNo = ws.Range("G4").Value '請求No demandDate = ws.Range("G5").Value '請求日 sumOfMoney = ws.Range("B17").MergeArea.Item(1) '合計金額 dueDate = ws.Range("G17").Value '支払期限 productNameInDetail = ws.Range("A20:G30").Value '品名詳細 '____________________________________________________________________ 'PDF印刷処理--------------------------------------------------------- fileName = ThisWorkbook.Path & "\印刷PDF\請求番号_" & demandNo ws.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName '____________________________________________________________________ 'DB書込み処理-------------------------------------------------------- bookNameDB = ThisWorkbook.Path & "\請求データDB.xlsx" Workbooks.Open bookNameDB Dim wbDB As Workbook: Set wbDB = ActiveWorkbook Dim wsWriteDB As Worksheet: Set wsWriteDB = ActiveWorkbook.Sheets("請求書DB") Dim i As Byte Dim write_IX As Long '最終行の取得処理 write_IX = wsWriteDB.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To 11 If productNameInDetail(i, 2) <> "" Then '書込み処理 write_IX = write_IX + 1 wsWriteDB.Cells(write_IX, 1) = company '会社 wsWriteDB.Cells(write_IX, 2) = staff '担当者 wsWriteDB.Cells(write_IX, 3) = subject '件名 wsWriteDB.Cells(write_IX, 4) = demandNo '請求No wsWriteDB.Cells(write_IX, 5) = demandDate '請求日 wsWriteDB.Cells(write_IX, 6) = dueDate '支払期限 wsWriteDB.Cells(write_IX, 7) = productNameInDetail(i, 1) '品名No wsWriteDB.Cells(write_IX, 8) = productNameInDetail(i, 2) '品名 wsWriteDB.Cells(write_IX, 9) = productNameInDetail(i, 5) '数量 wsWriteDB.Cells(write_IX, 10) = productNameInDetail(i, 6) '単価 wsWriteDB.Cells(write_IX, 11) = productNameInDetail(i, 7) '金額 wsWriteDB.Cells(write_IX, 12) = sumOfMoney '合計金額 End If Next '請求データDBを保存して閉じる wbDB.Save wbDB.Close '____________________________________________________________________ MsgBox "請求No_" & demandNo & "、PDF作成完了。" & Chr(10) & "DB登録完了" End Sub
処理イメージ
処理イメージの動画です。 https://t.co/Y66uSnsPHB pic.twitter.com/3akINn68Ol
— かずやん_VBAerLv.5 (@y8bV4ty1wbkTjPd) 2021年1月6日