返品依頼テンプレートへのデータ流し込みをエクセルVBAで行う方法

henpitempdataeyecatcha

こんにちは、じゅんぱ店長(@junpa33)です。

今回は,

エクセルBOOK「返品了解申請.xlsm」に転送した返品書誌情報から

作成したテンプレートを使って了解申請書作成を、VBAでプログラム作りを行っていきます。

その前に、

前回の記事の確認をするにはこちら↓からお読みいただけます。

返品了解書の情報入力VBA。利用後データのクリアコードの組み立て方

返品了解書作成に関連の記事はこちらです。

「返品了解書作成」の記事一覧を開く

返品依頼テンプレートへのデータ流し込みの進め方

先回、エクセルBOOK「返品了解申請.xlsm」に返品依頼テンプレートを作成しました。

今回は、そのテンプレートをVBAを使って実際に使えるものにしていきます。

①.返品依頼テンプレートを出版社別に複製していきます。

②.前回作業で「返品本依頼」シートに流し込んだデータを出版社別のテンプレートに配置していきます。

③.送信先出版社の電話番号・FAX番号・メールアドレスを電話帳より検索し、送信する用紙に表示します。

出版社別の返品依頼テンプレートはその都度、返品対象の出版社分を作成しますので、その時々で発行枚数が異なります。

返品理由については、空欄にその都度手書きか手打ちで書き込むようになります。

同時にFAX番号も表示させます。(ただし、事前に電話帳登録が必要です。)

流し込みのコード作成

初めに、いつものように標準モジュールを挿入します。

(挿入の方法はこちら↓で確認できます。)

VBE(ビジュアルベーシックエディター)を起動する

エクセルVBA 始めての起動。VBEの立ち上げ、保存と終了

テンプレートの複製コードの作成

出版社別に返品依頼シートを、テンプレートの複製で作っていきます。

シート名は、「ページNO.」で表示するようにします。

Subプロシージャー名を「テンプレコピー」とします。

Sub テンプレコピー()
End Sub

このプロシージャーでの変数はこのようになります。

「返品本分類のデータ行数」・・・・・BRow
「複製するシートのページNO.」・・SN
「ループ処理のループ回数」・・・・・i

A列の値(ページ番号)を上から順番に上下段2行づつ比較して、

値が同じならばスルー、

値が違えばその値をシート名としてテンプレートを複製します。

これをループしていきます。

関連記事

Gotoステートメントの利用法を最速に理解。毒コードと薬コード


Sub テンプレコピー()

Dim SN As Variant
Dim BRow As Long
Dim i As Long

 Worksheets("返品本分類").Select
 BRow = Cells(Rows.Count, 1).End(xlUp).Row

 If BRow <= 1 Then
  MsgBox "作成するテンプレートのデータはありません。"
  Exit Sub
 End If

 For i = 1 To BRow
  SN = Range("A" & i).Value
   If i = 1 Then GoTo step1
   If SN = Range("A" & i - 1).Value Then GoTo step2

step1:
   Worksheets("返品依頼テンプレート").Select
   Worksheets("返品依頼テンプレート").Copy after:=Worksheets(Worksheets.Count)
   ActiveSheet.Name = SN

step2:
   Worksheets("返品本分類").Select
 Next i

End Sub

データの適所配置のコードの作成

つぎに「返品本分類」シートにあるそれぞれのデータを、

出版社ごと(ページ番号)ごとに複製したテンプレートにはめ込んでいきます。

Subプロシージャー名を「データ移動」とします。

Sub データ移動()
End Sub

このプロシージャーでの変数はこのようにします。

「返品本分類のデータ行数」・・・・・BRow
「複製するシートのページNO.」・・SN
「ループ処理のループ回数」・・・・・p、s
「ISBN」・・・・・・・・・・・・ISBN
「書名」・・・・・・・・・・・・・・SHO
「出版社名」・・・・・・・・・・・・SYU
「著者名」・・・・・・・・・・・・・TYO
「本体価格」・・・・・・・・・・・・PRI
「返品冊数」・・・・・・・・・・・・HSA
「客注名」・・・・・・・・・・・・・KYA

「返品本分類」シートでは、出版社別リストごとに項目名行を挟み込んでいますので、

この「返品本分類」の一覧リストからデータを順番に収集するときに項目行をスルーする必要があります。

ループ処理でこの「項目名」を拾ったときは「GoTo Step」で処理をスルーして次のループに移るようにします。

テンプレート内には、

「著者名」と「本体価格」の表示する部分を作っていませんが、欄外に表示するようにしています。

「本体価格」表示列の下部行に返品金額の本体合計を計算できるようにしています。

(ただしこれは、「返品本分類」シート上で、本体価格が数値か金額で表示されている必要があります。)

プリントエリアを設定しています。

この欄外の表示部分はプリントされない仕様です。返品事由記入の参考項目としています。

関連記事

エクセルVBA!For~Nextの入れ子ループを最速に理解する

MEMO

入力セルの最終行を取得する方法はこちらを参考にしてください。

エクセルVBA データ入力済セルの最終行番号を取得する方法

Sum関数の使い方についてはこちらの記事を参考にしてください。

エクセルVBA SUM関数で合計計算!実務で使えるコード作成

Sub データ移動()

Dim SN As Variant
Dim BRow As Long, HSA As Long, p As Long, s As Long
Dim ISBN As Variant, PRI As Variant, KYA As Variant
Dim SHO As String, SYU As String, TYO As String

 Worksheets("返品本分類").Select
 BRow = Cells(Rows.Count, 1).End(xlUp).Row

For s = 1 To BRow
  Worksheets("返品本分類").Select
  SN = Range("A" & s)

  If Range("B" & s) = "ISBN" Then GoTo step3

   ISBN = Range("B" & s).Value
   SHO = Range("C" & s).Value
   SYU = Range("D" & s).Value
   TYO = Range("E" & s).Value
   PRI = Range("F" & s).Value
   HSA = Range("G" & s).Value
   KYA = Range("H" & s).Value

   Worksheets(SN).Select
   Range("B2").Value = SYU
   Range("C40").Value = SYU
   Range("C66").Value = SYU

   For p = 1 To 13
     If Range("C" & 10 + p) = "" Then
      Range("B" & 10 + p).Value = ISBN
      Range("C" & 10 + p).Value = SHO
      Range("D" & 10 + p).Value = HSA
      Range("B" & 44 + p).Value = ISBN
      Range("C" & 44 + p).Value = SHO
      Range("D" & 44 + p).Value = HSA
      Range("E" & 44 + p).Value = KYA
      Range("G" & 44 + p).Value = TYO
      Range("H" & 44 + p).Value = PRI
      Range("B" & 70 + p).Value = ISBN
      Range("C" & 70 + p).Value = SHO
      Range("D" & 70 + p).Value = HSA
      Range("E" & 70 + p).Value = KYA
      Range("G" & 70 + p).Value = TYO
      Range("H" & 70 + p).Value = PRI
      Exit For
     End If
   Next p
 Range("H58").Value = "本体合計¥" & WorksheetFunction.Sum(Range("H45:H57"))
 Range("H84").Value = "本体合計¥" & WorksheetFunction.Sum(Range("H71:H83"))

 Range("B2").Font.Size = 16
 Range("B45:E57").Font.Size = 10
 Range("B71:E83").Font.Size = 10
 Worksheets(SN).PageSetup.PrintArea = "A1:F86"
 Columns("G:H").AutoFit
step3:
Next s
End Sub

送信先電話番号の表示

出版社から返品了解を取る業務の中で、返品依頼を作成する作業は結構大変ですが、

その中で地味に面倒なのが、送信先の電話番号を調べるという作業です。

ですので、これも自動化してしまいましょう。

汎用性のある電話帳をエクセルで作成します。ここにはVBAを埋め込みませんので、

「FAX電話帳.xlsx」、使用するシート名を「電話番号」としてください。

A、B,C,D列の項目はこの通りです。

henpinsinsaku001a

準備が出来ましたら、

エクセルBOOK「返品了解申請.xlsm」で、先ほどに続いてVBA」を組み立てていきます。

電話帳からデータを収拾するために「返品了解申請.xlsm」から、VBAで「FAX電話帳.xlsx」を開きます。

すでに開いている場合は、この作業はスルーされます。

Subプロシージャー名を「FAX電話OPEN」とします。

Sub FAX電話OPEN()

Dim flag As Boolean
Dim Wb As Workbook
Dim FAX As String

  ChDir ThisWorkbook.Path
  FAX = ThisWorkbook.Path & "\FAX電話帳.xlsx"
  flag = False

  For Each Wb In Workbooks
    If Wb.FullName = FAX Then
      flag = True
      Exit For
    End If
  Next Wb
  If flag = False Then
    Workbooks.Open FAX, UpdateLinks:=0
  End If

End Sub

「FAX電話帳.xlsx」を開いたところで、調べたい出版社名の電話番号を探しに行きます。

方法としては、

「返品了解申請.xlsm」の出版社別に作成したシートの宛先から出版社名を拾います。

それと、「FAX電話帳.xlsx」の「電話番号」シート内のデータと比較するということにします。

ワークシート関数の「Match」を使いますが照合できないとエラーが出ますので、そのエラーを無視やスルーする処理コードが必要となります。

MEMO

Match関数について詳しくはこちらになります。

エクセルVBAで使うMatch関数 活用度アップでテッパン関数に!

InStr関数の使い方についてはこちらの記事を参考にしてください。

エクセルVBA Instr関数・InstrRev関数で文字列を検索する

Callステートメントの使い方についてはこちらの記事を参考にしてください。

Callステートメントはプロシージャーの部品化に必須項目 エクセルVBA最速理解

Subプロシージャー名を「電話番号検索」とします。


Sub 電話番号検索()

Dim i As Long
Dim ASN As Variant
Dim SYU As Variant
Dim DR As Long
Dim tela As Variant, telb As Variant, telc As Variant

Workbooks("返品了解申請.xlsm").Activate

  For i = 1 To Worksheets.Count
    ASN = Worksheets(i).Name

    If InStr(ASN, "P") > 0 Then
      Worksheets(ASN).Select
      SYU = Range("B2").Value

      FAX電話OPEN

      Workbooks("FAX電話帳.xlsx").Activate
      Worksheets("電話番号").Select
      On Error Resume Next
      DR = WorksheetFunction.Match(SYU, Worksheets("電話番号").Columns("A"), 0)
      On Error GoTo 0
      If DR <> 0 Then
        tela = Range("B" & DR)
        telb = Range("C" & DR)
        telc = Range("D" & DR)
        Workbooks("返品了解申請.xlsm").Activate
          Worksheets(ASN).Select
          Range("E3") = "FAX " & tela
          Range("E4") = "TEL " & telb
          Range("E5") = "MAIL" & telc
          Range("E3:E5").Font.Size = 10
      End If
    End If
    Workbooks("返品了解申請.xlsm").Activate
  Next i
  Workbooks("FAX電話帳.xlsx").Close False

End Sub

このような感じです。

今回作成したコード

今回作成したコードはこのような感じです。

henpinsinsaku002a

henpinsinsaku003a

henpinsinsaku004a

出版社への送信用返品了解依頼書

henpinsinsaku005a

返品了解依頼書(出版社からの回答分)を返送品に添付した上で、この↑連絡表を返送品梱包に張り付けて返送します。(連絡表が同じもの2枚あるのは1枚は自店控えです。)

データ流し込みコード作成のまとめ

今回作成したVBAは、作成した返品依頼テンプレートを複製して、

出版社毎に返品書誌データをはめ込むところまで完了させました。

次回は、送信元の情報を表示できるようにしていきます。

番線印も表示できるようにします。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは?良書との出会いは重要

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

次の記事を読むのはこちら↓です。

発信元情報を表示するためのエクセルVBA。返品依頼書を作る

エクセルVBAを使って、業務効率UPを図る上で始めのうちに知っておきたい内容を纏めています。

「VBA最速理解」の記事一覧を開く

今回の記事はここまでです。   最後までご覧いただき有難うございました。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。