|
|
|
|
|
|
|
|
|
|
|
|
EXERCISE01:
train0101 |
|
2007-1-8 by KOMPas |
|
|
|
■ |
「はがき印刷2」 |
|
|
|
|
exercise01.xls |
|
|
|
■ |
「はがき印刷」のコードをコピーして、少し手を加え、「はがき印刷2」をつくります。 |
|
|
主な変更点は、一覧表と、様式とを、頁を変えて作成する点と(転記マクロではこっちが普通)、 |
|
|
はがきの様式を縦置きに変え、様式が頁の左上(A1)から始まるように、若干変更します。 |
|
|
住所欄も、2行表示できるように変更します。(サンプルは、exercise01.xls) |
|
|
|
(1) |
EXCELを開いて、新規作成から「空白のブック」を作り、 |
|
|
「名前を変えて保存」で、ファイル名を「はがき印刷2.xls」とします。 |
|
|
exercise01.xls
は、シートだけでなく、マクロも書き込んであります。 |
|
|
練習する時間があるなら、是非「空白のブック」からお始めください。 |
|
|
|
(2) |
Sheet1 を住所録とします。 |
|
|
5行目をみだし行にするなら、 5行目A列から順に(ここでは必要ないので、セルの連結をしません。)、 |
|
|
|
見出し名 |
セル番地 |
列番号 |
|
|
氏名 |
A5 |
1 |
|
|
郵便番号 |
B5 |
2 |
|
|
住所1 |
C5 |
3 |
|
|
住所2 |
D5 |
4 |
|
|
連絡先 |
E5 |
5 |
|
|
区分 |
F5 |
6 |
|
|
|
|
|
ここでは連結セルを使わなくてもよいので、それぞれの列幅を適当に変更してください。 |
|
|
6行目からがデータ部となります。 |
|
|
お好みの行数分、罫線をいれ、見出し行の色付け等をおこなってください。 |
|
|
|
(3) |
Sheet2 をはがき様式頁にします。 |
|
|
様式頁を作成するコツは、あらかじめ充分な列数をまとめて、 |
|
|
全体が、印刷時にちょっきり(方言か?)1頁におさまるように、全ての列を同じ幅(できるだけ狭く)に調整し、 |
|
|
転記対象部は、セルの連結をつかって、複数セルを1つにして使います。 |
|
|
☆ |
はがき縦置の場合、 |
|
|
|
横幅が10cm なので、印刷時プリンタの左右余白を合計で 1.5cm とると、 |
|
|
|
印刷範囲は、85mm となります。 |
|
|
|
EXCEL様式頁の1列を5mmときめると、85 = 5 x 17 |
|
|
|
印刷に必要な列数は、17列なので、ここでは、A列〜Q列までを小さな幅にするわけです。 |
|
|
☆ |
ほかの帳票で、例えばA4用紙なら、 |
|
|
印刷範囲がおよそ17cm とすれば、 |
|
|
EXCEL様式頁の1列を5mmときめると、170 = 5 x 34 |
|
|
印刷に必要な列数は、34列なので、ここでは、A列〜AH列までを小さな幅にします。 |
|
|
☆ |
行政が要求する様式の場合、列幅をもっと小さくしたほうが作りやすいかもしれません。 |
|
|
|
|
|
はがき様式の郵便番号や、住所、氏名等データを転記する欄を連結で作成します。 |
|
|
転記部はデータが印刷され、転記部以外は、様式のまま印刷されます。 |
|
|
以下は、サンプルsexercise01.xls のはがき様式上のそれぞれ番地です。 |
|
|
|
データ名 |
セル番地 |
行 |
列 |
|
|
氏名 |
E13 |
13 |
5 |
|
|
郵便番号 |
D9 |
9 |
4 |
|
|
住所1 |
C10 |
10 |
3 |
|
|
住所2 |
C11 |
11 |
3 |
|
|
連絡先 |
H15 |
15 |
8 |
|
|
|
(4) |
いよいよマクロコードのお勉強です。 |
|
|
まず、住所録側で、 |
|
|
「氏名セルをダブルクリック」「区分セルをダブルクリック」マクロを作成します。 |
|
|
次に、はがき側で、 |
|
|
「一括印刷ボタンをダブルクリック」マクロを作成します。 |
|
|
|
(5) |
EXCELメニューから、「ツール」-「マクロ」-「Visual
Basic Editor」を選択します。 |
|
|
画面左側に、「Sheet1」とか、「Thisworkbook」とかあるのが、「プロジェクト
エクスプローラ」枠です。 |
|
|
「プロジェクト エクスプローラ」が表示されていないときは、 |
|
|
EXCELメニューから、「表示」-「プロジェクト エクスプローラ」を選択します。 |
|
|
「プロジェクト エクスプローラ」のSheet1
をダブルクリックします。 |
|
|
右側上部左枠に (General)、右枠に (Declarations) と表示されます。 |
|
|
左枠のリスト▼をクリックして、worksheet を選択します。 |
|
|
右枠のリスト▼をクリックして、BeforeDoubleClick
を選択します。 |
|
|
画面右側中央部の大きな枠に、 |
|
|
|
Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) |
|
|
|
(始めは何も記述されていない。otenki.xls のコードを貼り付ける場所) |
|
|
|
|
End Sub |
|
|
|
|
|
|
|
|
|
|
|
|
が表示されます。(コードを記述する前なので、これだけしか表示されない) |
|
|
前段の、「マクロをのぞいてみる」をおこなって、otenki.xls
のSheet2モジュールを同時に開きます。 |
|
|
otenki.xls 側のコードのうち、 |
|
|
(#1) |
Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) |
|
|
(#2) |
'Target はダブルクリックした場所。rr
はtarget左上セルの行番号、cc は左上セルの列番号。 |
|
|
(#3) |
rr = Target.Row |
|
|
(#4) |
cc = Target.Column |
|
|
(#5) |
If cc = 2 Then |
|
|
(#6) |
|
(「住所録 氏名セルをダブルクリック」コード) |
|
|
(#7) |
ElseIf rr = 21 And cc =
30 Then |
|
|
(#8) |
|
(「はがき様式 一括印刷をダブルクリック」コード) |
|
|
(#9) |
ElseIf cc = 33 Then |
|
|
(#10) |
|
(「住所録 区分セルをダブルクリック」コード) |
|
|
(#11) |
End If |
|
|
|
(#12) |
End Sub |
|
|
|
|
|
(6) |
Sheet1 モジュール 住所録頁マクロ |
|
|
|
|
を除いてコピーしてはがき印刷2に貼り付けます。(上部例では#2〜#11) |
|
|
「はがき印刷2.xls」Sheet1 に貼り付けたコードから、 |
(「はがき様式 一括印刷をダブルクリック」コード) |
部分を削除します。 |
|
セル番地やシート番号等を「はがき印刷2」に合わせて書き換えると、 |
|
|
はがき印刷2.xls Sheet1 モジュール
Worksheet_BeforeDoubleClick は、次のようになります。 |
|
|
白地が実際のコードで(緑文字はコード中のコメント)、橙文字はここで付け足した注釈です。 |
|
|
|
|
|
|
Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) |
|
|
|
rr = Target.Row |
|
|
|
cc = Target.Column |
|
|
|
If cc = 1 Then |
氏名列を 1 に変更 |
|
|
|
If rr
< 6 Then |
住所録欄の行範囲の変更 |
|
|
|
ElseIf rr > 15 Then |
住所録欄の行範囲の変更 |
|
|
|
Else |
|
|
|
Sheet2.Cells(9, 4) = Sheet1.Cells(rr, 2) |
|
住所録/はがき様式の番地変更 |
|
|
|
Sheet2.Cells(10, 3) = Sheet1.Cells(rr, 3) |
|
左辺転記先は、はがき様式につき
Sheet2 |
|
|
|
Sheet2.Cells(11, 3) = Sheet1.Cells(rr, 4) |
|
右辺転記元は、住所録につき
Sheet1 |
|
|
|
Sheet2.Cells(13, 5) = Sheet1.Cells(rr, 1) |
|
|
|
Sheet2.Cells(15, 8) = Sheet1.Cells(rr, 5) |
|
|
|
'はがき様式頁の表示 |
|
|
|
|
|
Sheet2.Activate |
|
|
|
|
|
|
|
If
Sheet1.Cells(rr, 6) = "中止" Then |
|
住所録は Sheet1、区分列を 6 に変更 |
|
|
|
Else |
|
|
|
www = MsgBox(Sheet1.Cells(rr, 1) & " さんのはがきを印刷しますか?",
vbOKCancel) |
|
|
|
If www = vbCancel Then |
|
|
|
Else |
|
|
|
Sheet2.Range("A1:Q29").PrintOut 1, 1 |
|
はがき様式 Sheet2 の印刷範囲を指定 |
|
|
|
End If |
|
|
|
End If |
|
|
|
'住所録頁の表示 |
|
|
|
Sheet1.Activate |
|
|
|
|
|
|
|
End
If |
|
|
|
ElseIf rr = 21 And cc =
30 Then |
|
|
|
'(住所録頁 では関係ないので削除) |
|
|
|
ElseIf cc = 6 Then |
区分列を 6 に変更 |
|
|
|
If rr
< 6 Then |
住所録欄の行範囲の変更 |
|
|
|
ElseIf rr > 15 Then |
住所録欄の行範囲の変更 |
|
|
|
ElseIf Sheet1.Cells(rr, 1)
& "" = "" Then |
|
住所録につき Sheet1、氏名列は 1 |
|
|
|
Else |
|
|
|
If
Sheet1.Cells(rr, cc) & "" = "" Then |
|
|
|
Sheet1.Cells(rr, cc) = "中止" |
|
|
|
Else |
|
|
|
Sheet1.Cells(rr, cc) = "" |
|
|
|
End If |
|
|
|
End
If |
|
|
|
|
|
End If |
|
|
|
|
|
End Sub |
|
|
|
|
|
(7) |
Sheet2 モジュール はがき様式頁マクロ |
|
|
|
|
|
Sheet1 と同じやり方で、Sheet2 モジュール
Worksheet_BeforeDoubleClick を開き、otenki.xls
コードを、 |
|
|
|
を除いてコピーして貼り付けます。(上部例では#2〜#11) |
|
|
「はがき印刷2.xls」Sheet2 に貼り付けたコードから、 |
(「住所録 氏名/区分セルをダブルクリック」コード) |
部分を削除します。 |
|
セル番地やシート番号等を「はがき印刷2」に合わせて書き換えると、 |
|
|
はがき印刷2.xls Sheet2 モジュール
Worksheet_BeforeDoubleClick は、次のようになります。 |
|
|
白地が実際のコードで(緑文字はコード中のコメント)、橙文字はここで付け足した注釈です。 |
|
|
|
|
|
|
Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) |
|
|
|
rr = Target.Row |
|
|
|
cc = Target.Column |
|
|
|
If cc = 2 Then |
|
|
|
'(様式頁 では関係ないので削除) |
|
|
|
ElseIf rr = 2 And cc = 19
Then |
|
「一括印刷」ボタンは、S2セル |
|
|
|
i = 6 |
|
住所録のデータ読込開始行を 6 に変更 |
|
|
|
Do Until Sheet1.Cells(i,
1) = "" |
|
住所録は Sheet1、氏名列は 1 |
|
|
|
If
Sheet1.Cells(i, 6) = "中止" Then |
|
住所録は Sheet1、区分列は 6 |
|
|
|
Else |
|
|
|
Sheet2.Cells(9, 4) = Sheet1.Cells(rr, 2) |
|
住所録/はがき様式の番地変更 |
|
|
|
Sheet2.Cells(10, 3) = Sheet1.Cells(rr, 3) |
|
左辺転記先は、はがき様式につき
Sheet2 |
|
|
|
Sheet2.Cells(11, 3) = Sheet1.Cells(rr, 4) |
|
右辺転記元は、住所録につき
Sheet1 |
|
|
|
Sheet2.Cells(13, 5) = Sheet1.Cells(rr, 1) |
|
|
|
Sheet2.Cells(15, 8) = Sheet1.Cells(rr, 5) |
|
|
|
|
|
|
|
www = MsgBox(Sheet1.Cells(rr, 1) & " さんのはがきを印刷しますか?",
vbOKCancel) |
|
|
|
If www = vbCancel Then |
|
|
|
Else |
|
|
|
Sheet2.Range("A1:Q29").PrintOut 1, 1 |
|
|
|
End If |
|
|
|
End If |
|
|
|
i
= i + 1 |
|
|
|
Loop |
|
|
|
ElseIf cc = 33 Then |
|
|
|
'(様式頁 では関係ないので削除) |
|
|
|
End If |
|
|
|
|
|
End Sub |
|
|
|
|
|
(8) |
あなたが書いたマクロを実際に動かしてみてください。 |
|
|
(9) |
コピー&貼り付けの威力を体験できましたか? |
|
|
転記マクロは、これで全てです。 |
|
|
あとは、転記元、転記先のセル番地を、自家製 一覧表/様式に合わせて、 |
|
|
根性で記述していくだけの話です。 |
|
|
|
こういったコード記述を業界では「力技(チカラワザ)」といいます。 |
|
|
時間さえあれば、必ずできる仕事だからです。 |
|
|
|
EXCELマクロでありがたいことの1つは、通常のコードならコード内で処理すべき計算を、 |
|
|
EXCELシートのシート関数をつかって、事前に終わらせることができるところにあります。 |
|
|
様式への転記を行う前に、一覧表時点で、計算を終わらせられる、ということです。 |
|
|
|
その為、事務系のマクロは、この転記マクロコードと、EXCELのシート関数だけで多くをこなせるわけです。 |
|
|
(・・・ 転記マクロを書く時間さえあれば) |
|
|
|
|
|
|
|
|
|
|
|
|
|