書籍データを出版社別に自動で振り分けて注文書を作成。VBAコードの核心部分

faxhatyudataeyecatch

じゅんぱ店長(@junpa33)です。

 

今回も引き続き書籍の注文書の作成について解説していきます。

 

今回は前々回作成のエクセルBOOK「FAX注文書作成.xlsm」を中心に加工していきます。

Fax注文書作成するVBAコードの最重要部分になります。

取り込んできた注文書籍のデータを、次の作業として出版社別に振り分けてまとめて行きます。

最終的に、1枚のFAX注文書の注文先は出版社1社のみである必要があるからです。

 

FAX注文書作成の関連の記事はこちらになります。

「FAX発注書作成」の記事一覧を開く

 

出版社別に自動で振り分けた書籍データで注文書を作成する

 

フォルダを作成する

 

今回のエクセルBOOK編集作業の前に、重要な作業があります。

 

前回作成の「FAX注文書テンプレート.xlsm」と前々回作成の「FAX注文書作成.xlsm」を

一つのフォルダで保管します。

 

フォルダ名を書籍FAX注文書としてください。(お好きな名前でもOKです。)

 

「FAX注文書作成.xlsm」を開く

 

「FAX注文書作成.xlsm」を開いてください。

 

「Sheet1」の名前を変更

 

シート選択タブで「Sheet1」の名前を変更して、「操作ボタン」としてください。

 

新しくシートを追加する

新しくシートを追加します。その新しいシート名を「出版社リスト」とします。

 

この様な感じになります。

faxhatyudata001aa

 

VBAコードの核心部分。注文内容を出版社別に振り分ける

 

先回の作業で書誌データを読み込んで「抽出データ」シートに一覧表示させました。

このデータは取り込んだ順番に並んでいます。

ですので、そのまま注文書テンプレートに転記するには非常に非効率です。

そこで、一覧表示されたデータを出版社別に”各出版社シート”を作り仕分けをしていきます。

 

「抽出データ」をソートします

 

ソートキーを”出版社名”として並び替えをします。

VBAコードを記入しますので、module2を挿入追加してください。

追加方法がイマイチと言われる場合は、

先記事の「VBE(ビジュアルベーシックエディター)を起動する」で確認してください。

参考記事です。

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

 

MEMO

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

データ入力済セルの最終行番号を取得するVBAコード

 

コードはこのようになります。

コード①

Sub ソート()  

Dim Hsrow As Long

 Worksheets(“抽出データ”).Activate

 Hsrow = Cells(Rows.Count, 1).End(xlUp).Row

 Worksheets(“抽出データ”).Range(Cells(2, 1), Cells(Hsrow, 7)) _

 .Sort Key1:=Worksheets(“抽出データ”).Cells(1, 3), order1:=xlAscending

End Sub

 

faxhatyudata002a

 

出版社リストを作ります

 

「出版社リスト」シートに出版社の一覧を表示させます。

一覧に表示されるリストは、同じ出版社であっても表示方法の違いで区別されて表示されます。

例えば「新春社」、「新春 社」,「(株)新春社」などすべて区別されます。

社名の表示は完全一致です。(通常のエクセルシートのソートの癖と同じです。)

 

コードは少し長いですがこのような感じです。

このように”Dictionaryオブジェクト”を使って重複を排除したリストを作成します。

 

MEMO

重複データの整理についてはこちらの記事を参考にしてください。

エクセルVBA 同じ項目をまとめる(重複データの整理)コード作成

If条件文についてはこの記事を参考にしてください。

「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。

For~Nextの使い方については、この記事も参考にしてください。

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

With~End Withの使い方については、この記事を参考にしてください。

With~End Withの使い方。VBAコードを簡潔に記述する

 

コード②

Sub 出版社名抽出()

 Dim tRange As Range, dRange As Range

 Dim buf As Variant, buf2 As Variant

 Dim myDic As Object

 Dim i As Long

 Dim myKeys As Variant

 

Worksheets(“抽出データ”).Select

Aho = Worksheets(“抽出データ”).Range(“C2”).Value

HaRow = Cells(Rows.Count, 6).End(xlUp).Row

If HaRow > 2 Then

 Set tRange = Sheets(“抽出データ”).Range(“C2:C” & HaRow)

  buf = tRange

 Set myDic = CreateObject(“Scripting.Dictionary”)

  On Error Resume Next

 For i = 1 To HaRow

  myDic.Add buf(i, 1), “”

 Next i

  On Error GoTo 0

  With Sheets(“出版社リスト”)

   Set dRange = .Range(.Range(“A1”), .Range(“A” & myDic.Count))

  End With

  buf2 = dRange

  myKeys = myDic.keys

 If myDic.Count = 1 Then

  Worksheets(“出版社リスト”).Range(“A1”).Value = Aho

 Else

  For i = 1 To myDic.Count

  buf2(i, 1) = myKeys(i – 1)

  Next i

  dRange = buf2

 End If

Else

  Worksheets(“出版社リスト”).Range(“A1”).Value = Aho

End If

End Sub

 

faxhatyudata003a

 

出版社別のシートを作成

 

「出版社リスト」にある出版社別にシートを新しく追加します。

コード③

Sub シート作成()

 Worksheets(“出版社リスト”).Activate

 SuRow = Cells(Rows.Count, 1).End(xlUp).Row

 For t = 1 To SuRow

  Vn = Range(“A” & t).Value

  Worksheets.Add After:=Worksheets(Worksheets.Count)

  ActiveSheet.Name = Vn

  Worksheets(“出版社リスト”).Activate

 Next t

End Sub

Worksheetsの追加時に行うポイントは、

VBA 新規作成ブックとシート。アクティブ状態でやっておくこと」を参考にできます。

各出版社シートへのデータ振り分け

 

先ほど作った出版社シートへデータを振り分けていきます。

For~Nextの入れ子構造については、

エクセルVBA!For~Nextの入れ子ループを最速に理解する」を参考してください。

コード④

Sub 発注振り分け()

 Worksheets(“出版社リスト”).Activate

 SuRow = Cells(Rows.Count, 1).End(xlUp).Row

 For t = 1 To SuRow

  Vn = Range(“A” & t).Value

  Worksheets(“抽出データ”).Activate

   HaRow = Cells(Rows.Count, 6).End(xlUp).Row

   s = 1

   For N = 1 To HaRow

    If Range(“C” & N).Value = Vn Then

     Rows(N).Copy Destination:=Worksheets(Vn).Range(“A” & s)

     s = s + 1

     Worksheets(Vn).Columns(“A:G”).AutoFit

    End If

    Worksheets(“抽出データ”).Activate

   Next N

   Worksheets(“出版社リスト”).Activate

 Next t

End Sub

 

faxhatyudata004a

 

ここまでのVBA動作を纏める

 

VBAコード①から④を一つのボタン操作で一度に行えるようにします。

「操作ボタン」シートに「注文データまとめ」ボタンを作ります。

4つのコードを纏めるVBAコードを作ります。

コード⑤

Sub 注文データまとめ()   

  ソート   

  出版社名抽出   

  シート作成   

  発注振り分け   

 Worksheets(“操作ボタン”).Select   

 Range(“A1”).Select

End Sub

VBAコード参考記事
ここの説明で出てくるVBAコードの参考にしていただける記事です。

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

 

faxhatyudata006ca

 

次に「操作ボタン」シートにボタンを設置します。

 

先回に行ったのと同じ作業です。

作業確認はデータ抽出ボタンを設置するでご確認ください。

 

こんな感じになります。

faxhatyudata007a

 

FAX注文書テンプレートBOOKと連携する

ここで作成した注文書のデータを「FAX注文書テンプレート.xlsm」に送り、連携をセットします。

 

FAX注文書テンプレートの”Sheet2”のシート名を変更する

 

「FAX注文書テンプレート.xlsm」を開き、”Sheet2”のシート名を「発注資料表」としてください。

faxhatyudata005aa

 

 

注文データのBOOK間移動をセットする

 

「FAX注文書作成.xlsm」に戻ります。

この「FAX注文書作成.xlsm」のVBAマクロで「FAX注文書テンプレート.xlsm」のシートのデータをコントロールします。

 

先ほど名前変更した「FAX注文書テンプレート.xlsm」の「発注資料表」に作表します。

BOOKとシートを行ったり来たりしますので、記号化してコードを書いていきます。

 

VBAコードをmodule3に記入します。モジュールの挿入追加をしてください。

追加方法の再度の確認は

先記事の「VBE(ビジュアルベーシックエディター)を起動する」です。

 

テンプレートにある様に出版社(発注先)ごと、5品目ごとに区分けして40ページまで作ることができます。

「 For t = 1 To 40 」のところで 40を50にすれば50ページになります。ご自由に変更ください。

「発注資料表」シートのM列は、いまどこまでページ数を使っているかを調べる”マーカー”を挿入しています。

文字・記号などを書き入れないようにしてください。

コードは以下のようになります。

 

MEMO

ChDirの使い方についてはこちらの記事が参考になります。

カレントフォルダを簡単に変更できるChDirステートメントの使い方

「For Each~Next」の使い方についてはこちらの記事を参考にしてください。

VBA 回数不定のループ処理はDo LoopとFor Each

 

コード⑥

Sub 発注資料()
    Dim Hsheet As Worksheet
    Dim Ssheet As Worksheet
    Dim SPRow As Long
    Dim VARow As Long
    Dim HBRow As Long
    Dim VA As Variant, Hva As Variant, Fva As Variant, Iva As Variant
    Dim Bva As Variant, Jva As Variant, Gva As Variant, Nva As Variant
    Dim i As Long, j As Long, x As Long, xx As Long, y As Long, t As Long, tt As Long
    Dim w As Long, ww As Long, z As Long, AR As Long

    ChDir ThisWorkbook.Path
    Workbooks.Open Filename:="FAX注文書テンプレート.xlsm"
    Set Hsheet = Workbooks("FAX注文書テンプレート.xlsm").Worksheets("発注資料表")
    Set Ssheet = Workbooks("FAX注文書作成.xlsm").Worksheets("出版社リスト")
    Ssheet.Activate
    SPRow = Cells(Rows.Count, 1).End(xlUp).Row
    Hsheet.Activate
    For t = 1 To 40 '40ページまで 50にすれば50ページまで可
        tt = t * 9 - 8
        Range("A" & tt) = "P" & t
        Range("A" & tt + 1) = "分類名"
        Range("B" & tt + 1) = "出版社名"
        Range("C" & tt + 1) = "書名"
        Range("D" & tt + 1) = "著者名"
        Range("E" & tt + 1) = "冊数"
        Range("F" & tt + 1) = "本体価格"
        Range("G" & tt + 1) = "シリーズ名"
        Range("H" & tt + 1) = "版数"
        Range("I" & tt + 1) = "送品条件"
        Range("J" & tt + 1) = "ISBNコード"
        Range("K" & tt + 1) = "客注名"
        Range("M" & 1) = "この列文字記入禁止"
        With Range(Cells(tt, 1), Cells(tt + 1, 13))
                .Font.Bold = True
                .Interior.ColorIndex = 35
        End With
    Next t
    Ssheet.Activate
    z = 1
    For i = 1 To SPRow
        VA = Range("A" & i).Value
        Worksheets(VA).Activate
        VARow = Cells(Rows.Count, 1).End(xlUp).Row
        For j = 1 To VARow
            Hva = Range("C" & j).Value
            Fva = Range("B" & j).Value
            Iva = Range("D" & j).Value
            Bva = Range("F" & j).Value
            Jva = Range("E" & j).Value
            Gva = Range("A" & j).Value
            Nva = Range("G" & j).Value
        Hsheet.Activate
        AR = WorksheetFunction.Match("P" & z, Hsheet.Columns("A"), 0)
            x = j Mod 5
            xx = j \ 5
        If x = 0 Then xx = j \ 6
           y = AR + 1 + j + 4 * xx
                Range("B" & y).Value = Hva  '出版社名
                Range("C" & y).Value = Fva  '書名
                Range("D" & y).Value = Iva  '著者名
                Range("E" & y).Value = Bva  '冊数
                Range("F" & y).Value = Jva  '本体価格
                Range("J:J").NumberFormatLocal = "0_ "
                Range("J" & y).Value = Gva  'ISBNコード
                Range("K" & y).Value = Nva  '客注名
                Range("M" & y).Value = "○" 'マーキング
            Workbooks("FAX注文書作成.xlsm").Activate
            Worksheets(VA).Select
        Next j
        Hsheet.Activate
        HBRow = Cells(Rows.Count, 13).End(xlUp).Row
        w = HBRow Mod 9
        ww = HBRow \ 9
    If ww = 0 Then
        If w < 3 Then
            z = 1
        Else
            z = 2
        End If
    Else
        z = ww + 1 + 1
    End If
        Workbooks("FAX注文書作成.xlsm").Activate
        Worksheets("出版社リスト").Select
    Next i
    Hsheet.Activate
    Hsheet.Columns("A:M").AutoFit
End Sub

 

複数に跨ってエクセルBOOKを操作しますので、注意点があります。

「FAX注文書作成.xlsm」のVBAを使って「FAX注文書テンプレート.xlsm」を開きますが、すでに「FAX注文書テンプレート.xlsm」が開いている場合、上のコードではエラーとなります。
ですので、すでに「FAX注文書テンプレート.xlsm」が開いていれば”VBAのBOOK オープンの命令”をスルーして次の作業に移らせるようにします。この修正コードはこのようになります。なお変数宣言のコードの下側にある
ChDir ThisWorkbook.Path
Workbooks.Open Filename:=”FAX注文書テンプレート.xlsm”
は削除してください。

コード⑥修正

 Dim flag As Boolean 
 Dim Wb As Workbook 
 Dim Fpa As String 
 ChDir ThisWorkbook.Path 
 Fpa = ThisWorkbook.Path & "\FAX注文書テンプレート.xlsm" 
 flag = False 
 For Each Wb In Workbooks 
   If Wb.FullName = Fpa Then 
     flag = True 
     Exit For 
   End If 
 Next Wb 
 If flag = False Then 
   Workbooks.Open Fpa 
 End If

 

この様になります。

faxhatyudata001aa
このVBAマクロのボタンを設置します。

「操作ボタン」シートに発注資料作成ボタンを作ります。

先ほどと同じようにしてください。

faxhatyudata009a

 

 データクリアボタンの設置

 

VBAコードの設置は新しいモジュール(module4)に記入します。

まず挿入設置してください。

「FAX注文書作成.xlsm」BOOKを使用後に、最初のデフォルト状態に戻すようにします。

データが入力された各シートと出版社ごとのシートをすべて削除します。

 

MEMO

シートのクリアのためのエクセルVBAコードの作り方については、この記事が参考になります。

エクセルVBA シートのクリアーを目的のメソッド別に最速理解

 

抽出データをクリアーする

 

最初にクリアーするのは「抽出データ」シートです。

コード⑦

Sub 抽出データクリアー()
Worksheets("抽出データ").Select
Cells.Select
Selection.Clear
Range("A1").Select
Worksheets("操作ボタン").Select
Range("A1").Select
End Sub

 

出版社別シートを削除する

 

作成順で行うと「出版社リスト」になります。

しかしこれを先にクリアーすると、”出版社別シート”を削除するVBAコードの設計が面倒です。

コード⑧

Sub 出版社シートクリアー()
Dim PrRow as Long
Dim PrV as Variant
Worksheets("出版社リスト").Activate
  If Range("A1").Value <> "" Then
   PrRow = Cells(Rows.Count, 1).End(xlUp).Row
    For t = PrRow To 1 Step -1
      PrV = Range("A" & t).Value
      Worksheets(PrV).Select
      Application.DisplayAlerts = False
      Worksheets(PrV).Delete
      Worksheets("出版社リスト").Activate
    Next t
      Application.DisplayAlerts = True
 End If
End Sub

 

出版社リストをクリアーする

 

そして出版社リストをクリアーします。

コード⑨

Sub 出版社リストクリアー()
Worksheets("出版社リスト").Select
Cells.Select
Selection.Clear
Range("A1").Select
Worksheets("操作ボタン").Select
Range("A1").Select
End Sub

 

クリアーボタンを設置する

 

コード⑦から⑨のVBAを順に実行するALLクリアVBAコードとそのボタンを設置します。

コード⑩

Sub ALLクリアクリアー()
抽出データクリアー
出版社シートクリアー
出版社リストクリアー
End Sub

この様になります。

faxhatyudata011a

 

faxhatyudata010a

 

書籍データを出版社別に注文書を作成。
VBAコードの核心部分のまとめ

 

これで「FAX注文書作成.xlsm」の方は完成です。

操作方法についてですが、

 

  1. 「書誌データ貼付」シートにWebからの書籍情報をコピーペーストします。”形式を選択して貼り付け”で”テキスト”を選択します。
  2. 「抽出項目・抽出セル名表」に必要データのセル位置を入力します。
  3. 「書誌データ抽出ボタン」を押すと、「抽出データ」シートに情報が貼り付けられます。
  4. 「操作ボタン」シートに移動し「注文データまとめ」ボタンを押せば、情報が出版社別に仕分けされます。
  5. 「発注資料作成」ボタンを押せば、「FAX注文書テンプレート.xlsm」に情報が送られます。
  6. 最後に「ALLクリアー」ボタンで使用前のデフォルト状態に戻ります。終了時にはこの状態で保存終了してください。

 

次回の記事は「FAX注文書テンプレート.xlsm」での作業となります。

 

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

 

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

 

 

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

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

 

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

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