みなさんこんにちは、ビジネス代行の梅澤です。業務でラベルを扱う人も多いかと思います。今回はExcelで差し込み印刷を行うマクロを作成しました。汎用的なVBAではないので、一部分の公開と考え方について解説します。
作成の目的
今回は24面のラベルを使用しています。
希望の数量を入力するとラベルを作成できるVBAをすでに組んでいましたが、可変の数量で作成することで、事故防止になるかと思い作成しました。
例えば実際に使用する枚数は5枚なのに、必要最大数の10枚に合わせると、未使用ラベルが5枚できますが、誤って使用してしまうことも考えられます。最初から必要数を印刷することで事故防止になります。
- ・作業時の事故防止を目的としていること
- ・複数名で作業することを前提にしていること
この二点が目的の為、ラベル用紙を節約するのではなく、未使用領域が余っても廃棄する事を前提に作成することにしました。ですので、ラベル紙を節約される場合は、今回のようなプログラムは不向きかと思います。
発送業では、ラベル作成業務は日常的に行われます。この作業時間を短縮することで、塵積で手すきの時間が生まれ、更なる業務効率が期待できます。
VBAの設計
Accessのラベル作成の差し込み機能を活用してもよいかと思いますが、複数のソフトを行ったり来たりするタイムロスを考えると、一つのソフトで完結させる方法を考えました。
1.エクセルの前提
エクセルは表計算ソフトですので、印刷のレイアウト調整を非常に苦手としています。また、業務用プリンター（事務機用など）はフチなし印刷に対応していないことがほとんどです。そこは力業でレイアウト調整を行いました。
2.レイアウト調整のコツ
レイアウト調整のコツとしてはセーフラインを作成することです。
印刷物や映像作品を作成するときには、このエリアには重要なオブジェクトを配置しない法則があります。
今回もそれに倣って作成しています。今回作成する24面ラベルについては、3×8のラベル配置になっています。1列しっかり作りこみ、コピーして3列作成。
…っと行いたいところですが、これで完成ではありません。
かなり緻密にレイアウトの調整を行ったところで、フチなし印刷ができない以上、どこかの列で調整をかけなければなりません。
更にExcelの幅調整で、列幅を長さ(1.6mmなど）を入力しても1.59mmの設定に変わってしまいます。
これは、Excelはピクセル単位で列幅や行幅を設定しているので、ズレが発生してしまいます。細かなズレがラベル紙の枠から差し込み内容がはみ出ることもあります。
緻密な入力を行ってもズレが発生してしまいますので、セーフラインの設定は必須です。
今回は中心の列幅を広げる調整を行い、両サイドはその分狭めることにしました。 また、ある程度の汎用性を残せるように、各列幅を少し小さめに設定いたしました。
レイアウト済みファイルのDLはコチラ（A-ONEやaskul製のラベルに合わせました。）
3.コピー方式かリセット方式か
レイアウト調整が完了した後は、どのような印刷方法をするのかを考えます。
今回は2つのパターンが考えられます。
・コピー方式
差し込み用シートに対して、全ての印刷情報をコピーし差し込みます。
・リセット方式
一度差し込んだ後に自動で印刷。シートをクリアし再度次の情報を差し込み印刷。これを繰り返す。
今回はリセット方式を採用しました。作成数が多く、作業時間も可能な限り圧縮したいと思い判断しました。
また、今回はワンクリックで全ての作業を完了させるのではなく、
①印刷用シートの作成
②差し込み、印刷
の二段階方式にしました。1つで実行する意味を感じられなかったので、2段階実行方式です。
4.座標の設定
ワードの差し込みやAccessの差し込みと異なるのは、座標の設定が必要になるところです。 一つ差し込み終わったら、次はどこのセル（座標）に入力するのか。この座標問題を解決するには、プログラミングで必ず問われる、「a=a+1」（インクリメント）の概念を理解する必要があります。 今回の座標として使用する場合は、
・差し込み終わったら右にいくつ座標をずらすのか（例：X座標を+5する など）
・X方向、Y方向、どこまで進んだらリセットするのか
このような思考が必要になります。その分コードも長くなりますし、複雑化もしますが、これも一つのソフトで完成させるためと思って、強引にコードを作成します。
プログラミング未経験の場合、躓くポイントです。数学的な考えではなく、左辺（変数a)に右辺（変数aに1を足す）を代入します。これをループ中に行うと、ループが行われる度に変数aが1ずつ増えていきます。
5.VBAのフロー図
VBAを作成するにあたって、全体の設計を考えました。フロー図は以下の通りです。
左がシートを複製し、差し込み用のシートを表示させるVBAです。
右は複製されたシートに、差し込み情報を転記し印刷するVBAです。
6.完成PDF
今回は印刷見本をPDFで出力し、jpegに変換してみました。ご覧いただけるように、必要数のラベルが出力されています。
下記のような差し込み用のシートに差し込み情報を入力します。
7.VBAの一部公開
Function GetMasterSheet() As Worksheet
Set GetMasterSheet = ThisWorkbook.Sheets("差し込み情報入力シート")
End Function
Function GetLastRow(Mst As Worksheet) As Long
GetLastRow = Mst.Cells(Rows.Count, 3).End(xlUp).Row
End Function
Function GetPrintSheet() As Worksheet
Set GetPrintSheet = ThisWorkbook.Sheets("印刷用シート")
End Function
Sub 24()
Dim lastRow As Long
Dim masterSheet As Worksheet
Dim printSheet As Worksheet
Dim totalPages As Long
Dim printRange As Range
Dim pageBreakRow As Long
Dim labelIndex As Long ' ラベル番号（1～24）
Dim labelCount As Long ' 必要ラベル数ループ用
Dim rowPos As Long ' 出力先行
Dim colPos As Long ' 出力先列
Dim pageOffset As Long
Dim i As Long
Dim textLength As Long
' --- シート取得 ---
Set masterSheet = GetMasterSheet()
Set printSheet = GetPrintSheet()
lastRow = GetLastRow(masterSheet)
' --- メインループ ---
For i = lastRow To 4 Step -1
' 必要ページ数
totalPages = WorksheetFunction.RoundUp(masterSheet.Cells(i, 11) / 24, 0)
' 印刷範囲設定
Set printRange = printSheet.Range(printSheet.Cells(1, 1), printSheet.Cells(totalPages * 88, 36))
printSheet.PageSetup.PrintArea = printRange.Address
' 改ページ設定
If totalPages > 1 Then
For pageBreakRow = 88 To totalPages * 88 Step 88
printSheet.Cells(pageBreakRow + 1, 1).PageBreak = xlPageBreakManual
Next pageBreakRow
End If
' --- ラベル作成 ---
For labelCount = 1 To masterSheet.Cells(i, 11)
labelIndex = labelIndex + 1
If labelIndex = 25 Then
labelIndex = 1
End If
' --- 配置計算 ---
Select Case labelIndex
Case 1 To 3
If labelIndex = 1 Then rowPos = rowPos + 10
colPos = colPos + 12
Case 4 To 6, 7 To 9, 10 To 12, 13 To 15, 16 To 18, 19 To 21, 22 To 24
If (labelIndex - 1) Mod 3 = 0 Then
colPos = 0
rowPos = rowPos + 11
End If
colPos = colPos + 12
If labelIndex = 24 Then
pageOffset = pageOffset + 24
End If
End Select
' --- 書き込み ---
With printSheet
.Cells(rowPos - 5, colPos - 10) = masterSheet.Cells(i, 3) ' 差し込み情報1
.Cells(rowPos - 8, colPos - 10) = masterSheet.Cells(i, 4) ' 差し込み情報2
' 書式コピー
.Cells(rowPos - 8, colPos - 10).Interior.Color = masterSheet.Cells(i, 3).Interior.Color
.Cells(rowPos - 8, colPos - 10).Font.Color = masterSheet.Cells(i, 3).Font.Color
.Cells(rowPos - 6, colPos - 5) = masterSheet.Cells(i, 5) ' 差し込み情報3
.Cells(rowPos - 5, colPos - 8) = masterSheet.Cells(i, 6) ' 差し込み情報4
.Cells(rowPos - 2, colPos - 10) = masterSheet.Cells(i, 7) ' 差し込み情報5
' 文字数で調整
textLength = Len(masterSheet.Cells(i, 7).Value)
If textLength > 17 Then
With .Cells(rowPos - 2, colPos - 10)
.WrapText = True
.Font.Size = 8
End With
End If
End With
' --- ページ切替時 ---
If labelIndex = 24 Then
colPos = 0
rowPos = rowPos + 1
End If
Next labelCount
printSheet.print
' --- 初期化 ---
colPos = 0
rowPos = 0
labelIndex = 0
pageOffset = 0
printSheet.Range("A1:A264").EntireRow.ClearContents
Next i
' --- 後処理 ---
printSheet.Unprotect
Set printRange = Nothing
Set printSheet = Nothing
Set masterSheet = Nothing
Application.ScreenUpdating = True
End Sub
差込情報入りシートは、私仕様にカスタムされていますので、完全公開はできませんが考え方としては、以下の通りです。
- 必要ページ数を取得
- 必要ページ数に応じた印刷範囲の設定
- 改ページを挿入
- 情報を差込み、印刷する（表面排紙なので、最終行から印刷）
- これをループ
8.まとめ
- Excelのみでラベル印刷は可能
- 印刷レイアウトの調整が必要
- 最大ページ数が1026（対策可能）
- 座標設定が必要</li>
- インクリメント（a=a+1）の理解が必須
- </ol>
Excelのみでラベル印刷は可能です。座標の設定でコードが複雑になりがちですが、一度作成してしまえば快適にラベルが作成されます。
些細なことでも構いません。お困りごと・ご相談など、お気軽にご連絡下さい。
どんなご質問でもお答えいたします。お問い合わせお待ちしております。
