【他のレンジ/シート/ブックを更新する】

4 セルで指定したシートを更新

5 複数シートを連続更新

6 Subプロシージャの定義と呼び出し

7 あるブックから別ブックを更新

8 他のブックをオープン・クローズ

9 テンプレートから出力ブックを作成し更新

10 確認メッセージの非表示

あるブックから別ブックを更新

 今回は、Excel VBA/マクロによって、入力ブックからデータを読みとり出力ブックへデータを書きこんでみます。



1. あるブックから別ブックを更新

 あるシートのセルから別シートのセルに値を代入するときは、次のような構文になります。

Img10_1_01

 あるシートから別のシートに値を代入するときは、それぞれのワークシート名を指定しました。

 あるブックのセルから別ブックのセルに値を代入するときは、

 あるブックのワークブック名と、別ブックのワークブック名をそれぞれ指定する必要があります。

 あるブックのセルから別ブックのセルに値を代入するときは、

 それぞれのワークブック名及びワークシート名を指定します。

 次のような構文になります。

Img10_1_02


2. マクロ実行用のブックを作成

 前回は、同一ブック内で、入力シートからデータを読みとり、出力シートへデータを書きこみました。

 今回は、入力ブックからデータを読みとり、出力ブックへデータを書きこみます。

 入力ブックと出力ブックの間でデータを読み書きすることになるため、

 今回は、そのマクロの実行用ブックを作成してみます。

 入力ブック・出力ブック・マクロ実行用ブックの関連図は次のようになります。

 【『他のブックから他のブックへ更新マクロ1』実行

Img10_1_08

 【『他のブックから他のブックへ更新マクロ1』実行

Img10_1_09

 最初に、10問の漢字文字列を用意した『漢字入力帳.xlsx』、白紙の『漢字問題集.xlsx』・『漢字解答集.xlsx』

 を作成します。

『漢字入力帳.xlsx』
Img10_1_11
『漢字問題集.xlsx』
Img10_1_12
『漢字解答集.xlsx』
Img10_1_13

 続けて、『漢字入力帳』ブックからデータを読みとり、『漢字問題集』・『漢字解答集』ブックへデータを

 書きこむマクロを実行するためのワークブックを作成します。

 先ず、次の画像のようなExcelマクロ有効ブック『10_あるブックから別ブックを更新1.xlsm』を作成しましょう。

Img10_1_14

 セル『C5』に入力ファイル名『漢字入力帳.xlsx』、セル『C8』に出力ファイル名『漢字問題集.xlsx』、

 セル『C9』に出力ファイル名『漢字解答集.xlsx』、を記入します。

 次に、更新ボタンを作成します。

Img10_1_15

 開発タブの挿入コマンドをクリックしたら表示されるプルダウンメニューから、

 フォームコントロールのボタンを選んでクリックしてください。

 そして、『D8』あたりにマウスでカーソルを合わせてマウスを指でクリックして、

 そのまま指を離さずドラッグして『E9』あたりで指を離します。

 マクロの登録画面が現れますが、ここではキャンセルボタンをクリックします。

 すると、『ボタン1』というボタンが現れます。

Img10_1_16

 『ボタン1』というボタンにマウスでカーソルを合わせて右クリックしたら表示されるプルダウンメニューから、

 テキストの編集を選んでクリックしてください。

Img10_1_17

 ボタン上の文字が編集可能になったため、『ボタン1』を『更新』という名前に変更します。

Img10_1_18

 『更新』ボタンの選択状態を解除するために、『更新』ボタンの外側のセルをクリックします。

 とりあえず、『E11』をクリックしておきます。

Img10_1_19

 これで、Excelマクロ有効ブック『10_あるブックから別ブックを更新1.xlsm』ができました。



3. 漢字問題集ブックと漢字解答集ブックを更新

 それでは、『漢字入力帳』ブックからデータを読みとり、『漢字問題集』・『漢字解答集』ブックへ

 データを書きこむプログラムを作成しましょう。

 『9_他の複数シートを更新3.xlsm』のマクロを全てコピーして、

 『10_あるブックから別ブックを更新1.xlsm』の新規標準モジュールにペーストします。


 宣言部を改修します。

定数『入力帳ファイルセル_Cst』に『C5』を代入

定数『問題集ファイルセル_Cst』に『C8』行を代入

定数『解答集ファイルセル_Cst』に『C9』行を代入

定数『入力帳ファイルセル_Cst』に『C5』を代入

定数『問題集ファイルセル_Cst』に『C8』行を代入

定数『解答集ファイルセル_Cst』に『C9』行を代入


このワークブックを文字列型の変数『ThisBook』として定義

データ変換実行ワークシートを文字列型の変数『Cst』として定義

読取ワークブックを文字列型の変数『EBook』として定義

問題集ワークブックを文字列型の変数『WBookMD』として定義

解答集ワークブックを文字列型の変数『WBookKT』として定義

書込ワークブックを文字列型の変数『WBook』として定義

 Public Const 入力帳ファイルセル_Cst = “C5”

 Public Const 問題集ファイルセル_Cst = “C8”

 Public Const 解答集ファイルセル_Cst = “C9”


 Public ThisBook As String

 Public Cst As String

 Public EBook As String

 Public WBookMD As String

 Public WBookKT As String

 Public WBook As String


 【宣言部】

Option Explicit


 Public Const 入力帳ファイルセル_Cst = “C5”

 Public Const 問題集ファイルセル_Cst = “C8”

 Public Const 解答集ファイルセル_Cst = “C9”

 Public Const Est = "漢字入力帳"

 Public Const WSheetMD = "漢字問題集"

 Public Const WSheetKT = "漢字解答集"

 Public Const 読取問題番号列 = "A"

 Public Const 読取列 = "B"

 Public Const 最大枠数 = 4

 Public Const 漢字最小列 = 3

 Public Const 漢字最大列 = 6

 Public Const 読取最小行 = 2

 Public Const 最大列 = 10

 Public Const 書込列数 = 5

 Public Const 書込ステップ列 = -2

 Public Const 書込問題番号最小行 = 1

 Public Const 書込ステップ行 = 15


 Public ThisBook As String

 Public Cst As String

 Public EBook As String

 Public WBookMD As String

 Public WBookKT As String

 Public WBook As String

 Public Wst As String

 Public 読取最大行 As Long

 Public 読取行 As Long

 Public 行番号 As Long

 Public txt As String

 Public 最大文字数 As Integer

 Public 文字位置 As Integer

 Public 漢字列 As Integer

 Public 漢字名 As String

 Public 列英字名 As String

 Public 枠領域 As String

 Public ふりがな読取列 As Integer

 Public ふりがな列 As Integer

 Public ふりがな名 As String

 Public 列番号 As Integer

 Public 問題番号 As Long

 Public 書込問題番号行 As Long

 Public 最小行 As Long



 処理部を改修します。


A. マクロ『他のブックから他のブックへ更新』

 サブプロシージャ『シートから複数シート3』は、『他のブックから他のブックへ更新』に名称を変更しておきます。

 プログラムの実行時に、動作中で、複数あるものの内、最前面に表示されている状態のことを、

 アクティブと言います。

 そして、プログラムの実行時に、動作中で、複数あるものの内、最前面に表示されている

 ワークブックのことを、アクティブワークブック(ActiveWorkbook)と言います。

 更に、プログラムの実行時に、動作中で、複数あるものの内、最前面に表示されているシートの

 ことを、アクティブシート(ActiveSheet)と言います。


 (1) このExcelマクロ実行ブックの名前を取得します。

  『更新』ボタンを押した直後に、このExcelマクロ実行ブックはアクティブワークブックとなるため、

  マクロの最初の箇所で、アクティブワークシートの名前(ActiveWorkbook.Name)を取得し、

  変数『ThisBook』に格納します。

 ThisBook = ActiveWorkbook.Name


 (2) このExcelマクロ実行シートの名前を取得します。

  『更新』ボタンを押した直後に、このExcelマクロ実行シートはアクティブシートとなるため、

  マクロの最初の箇所で、アクティブシートの名前(ActiveSheet.Name)を取得し、

  変数『Cst』に格納します。

 Cst = ActiveSheet.Name


 (3) このExcelマクロ実行ブックのExcelマクロ実行シートのセル『C5』から

  入力ファイル名『漢字入力帳.xlsx』を取得し、変数『EBook』に格納します。

 EBook = Workbooks(ThisBook).Worksheets(Cst).Range(入力帳ファイルセル_Cst).Value


 (4) このExcelマクロ実行ブックのExcelマクロ実行シートのセル『C8』から

  出力ファイル名『漢字問題集.xlsx』を取得し、変数『WBookMD』に格納します。

 WBookMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集ファイルセル_Cst).Value


 (5) このExcelマクロ実行ブックのExcelマクロ実行シートのセル『C9』から

  出力ファイル名『漢字解答集.xlsx』を取得し、変数『WBookKT』に格納します。

 WBookKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集ファイルセル_Cst).Value


 (6) 変数『WBook』に変数『WBookMD』を格納。

   変数『Wst』に変数『WSheetMD』を格納。

   サブプロシージャ『ワークシート更新』をコール。

 WBook = WBookMD

 Wst = WSheetMD

 Call = ワークシート更新


 (7) 変数『WBook』に変数『WBookKT』を格納。

   変数『Wst』に変数『WSheetKT』を格納。

   サブプロシージャ『ワークシート更新』をコール。

 WBook = WBookKT

 Wst = WSheetKT

 Call = ワークシート更新


 【サブプロシージャ『他のブックから他のブックへ更新』】

Sub 他のブックから他のブックへ更新()


 ThisBook = ActiveWorkbook.Name

 Cst = ActiveSheet.Name

 EBook = Workbooks(ThisBook).Worksheets(Cst).Range(入力帳ファイルセル_Cst).Value

 WBookMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集ファイルセル_Cst).Value

 WBookKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集ファイルセル_Cst).Value

 '◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 漢字問題集 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆

 WBook = WBookMD

 Wst = WSheetMD

 Call = ワークシート更新


 '◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 漢字解答集 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆

 WBook = WBookKT

 Wst = WSheetKT

 Call = ワークシート更新


End Sub



B. マクロ『ワークシート更新』

 『9_他の複数シートを更新3.xlsm』のマクロ『ワークシート更新』では、同一ブック内で、

 あるシートのセルからデータを読みとり、別シートのセルへデータを書きこみましたが、

 『10_あるブックから別ブックを更新1.xlsm』のマクロ『ワークシート更新』では、

 あるブック・シートのセルからデータを読みとり、別ブック・シートのセルへデータを書きこみます。

 そのため、変更箇所は、次のようになります。

Img10_1_22

 このように、Worksheets(Est).Cells(読取行, 読取列)の前にWorkbooks(EBook).

 Worksheets(Wst).Cells(行番号, 列番号)の前にWorkbooks(WBook).を追加します。


 【サブプロシージャ『ワークシート更新』】

Sub ワークシート更新()


 読取最大行 = Workbooks(EBook).Worksheets(Est).Cells(Rows.Count, "B").End(xlUp).Row

 ' ===== 読取行のカウントアップS =====

 For 読取行 = 読取最小行 To 読取最大行

 問題番号 = Workbooks(EBook).Worksheets(Est).Cells(読取行, 読取問題番号列).Value

 書込問題番号行 = Int((問題番号 - 1) / 書込列数) * 書込ステップ行 _

 + 書込問題番号最小行

 最小行 = 書込問題番号行 + 1

 列番号 = 最大列 + ((問題番号 - 1) Mod 書込列数) * 書込ステップ列

 列英字名 = Replace(Workbooks(WBook).Worksheets(Wst).Cells(1, 列番号) _

 .Address(False, False), "1", "")

 枠領域 = 列英字名 & 書込問題番号行

 Workbooks(WBook).Worksheets(Wst).Range(枠領域).Value = 問題番号

 Workbooks(WBook).Worksheets(Wst).Range(枠領域).Font.Size = 10


 txt = Workbooks(EBook).Worksheets(Est).Cells(読取行, 読取列).Value

 最大文字数 = Len(txt)

 ' ===== 対象文字列文字位置のカウントアップS =====

 For 文字位置 = 1 To 最大文字数

 行番号 = 最小行 + 文字位置 - 1

 Workbooks(WBook).Worksheets(Wst).Cells(行番号, 列番号).Value _

 = Mid(txt, 文字位置, 1)

 Next 文字位置

 ' ===== 対象文字列文字位置のカウントアップE =====


 ' ===== 漢字列のカウントアップS =====

 For 漢字列 = 漢字最小列 To 漢字最大列

 漢字名 = Workbooks(EBook).Worksheets(Est).Cells(読取行, 漢字列).Value

 If 漢字名 <> "" Then

 文字位置 = InStr(Workbooks(EBook).Worksheets(Est). _

 Cells(読取行, 読取列).Value,漢字名)

 If 文字位置 > 0 Then

 行番号 = 最小行 + 文字位置 – 1

 列英字名 = Replace(Workbooks(WBook).Worksheets(Wst). _

 Cells(1, 列番号).Address(False, False), "1", "")

 枠領域 = 列英字名 & 行番号

 Workbooks(WBook).Worksheets(Wst).Range(枠領域).Borders. _

 LineStyle = xlContinuous

 If Wst = WSheetMD Then

 Workbooks(WBook).Worksheets(Wst).Range(枠領域).Value = ""

 End If


 ふりがな読取列 = 漢字列 + 最大枠数

 ふりがな名 = Workbooks(EBook).Worksheets(Est). _

 Cells(読取行, ふりがな読取列).Value

 If ふりがな名 <> "" Then

 ふりがな列 = 列番号 + 1

 列英字名 = Replace(Workbooks(WBook).Worksheets(Wst). _

 Cells(1, ふりがな列).Address(False, False), "1", "")

 枠領域 = 列英字名 & 行番号

 Workbooks(WBook).Worksheets(Wst).Range(枠領域).Value _

 = ふりがな名

 Workbooks(WBook).Worksheets(Wst).Range(枠領域). _

 Font.Size = 8

 Workbooks(WBook).Worksheets(Wst).Range(枠領域). _

 HorizontalAlignment = -4131

 Workbooks(WBook).Worksheets(Wst).Range(枠領域). _

 Orientation = -4166

 Workbooks(WBook).Worksheets(Wst).Range(枠領域). _

 ShrinkToFit = True

 End If

 End If

 End If

 Next 漢字列

 ' ===== 漢字列のカウントアップE =====

 列番号 = 列番号 + 書込ステップ列

 Next 読取行

 ' ===== 読取行のカウントアップE =====


End Sub


 これで、Excelマクロ有効ブック『10_あるブックから別ブックを更新1.xlsm』のプログラムが完了しました。

 そこで、『更新』ボタンにマウスでカーソルを合わせて右クリックしたら表示されるプルダウンメニューから、

 マクロの登録を選んでクリックしてください。

Img10_1_28

 『マクロの登録』画面が現れました。

Img10_1_29

 『マクロの登録』画面に表示されているマクロの一覧から、『他のブックから他のブックへ更新』を選択し、

 OKボタンをクリックします。

 これで、『更新』ボタンにマクロ『他のブックから他のブックへ更新』がセットされました。

Img10_1_30

 それでは、『漢字入力帳.xlsx』・『漢字問題集.xlsx』・『漢字解答集.xlsx』を開いたままの状態で、

 『更新』ボタンをクリックしてマクロ『他のブックから他のブックへ更新』を実行してみましょう。


『漢字問題集.xlsx』
Img10_1_31
『漢字解答集.xlsx』
Img10_1_32

 『漢字入力帳.xlsx』からデータが読みとられ、『漢字問題集.xlsx』・『漢字解答集.xlsx』へ

 データが書きこまれました。