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

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

5 複数シートを連続更新

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

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

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

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

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

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

 今回は、Excel VBA/マクロによって、テンプレートブックから出力ブックとして新規ブックを作成し、

 入力ブックからデータを読みとり出力ブックへデータを書きこんでみます。

 テンプレートブックから出力ブックとして新規ブックを作成し、

 入力ブックからデータを読みとり、出力ブックへデータを書きこみ、

 入力ブックをセーブせずクローズ、出力ブックをセーブしてクローズする

 マクロをつくってみます。

 テンプレートブックから出力ブックとして新規ブックを作成した後、

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

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

 入力ブック・テンプレートブック・出力ブック・マクロ実行用ブックの関連図は

 次のようになります。


 【『他のブックから他のブックへ更新マクロ3』】

Img10_3_10


1. テンプレートを作成

 前回と同様に、『漢字入力帳.xlsx』を『C:\Excelマクロ\漢字入力帳』に保存しておきます。

 今回は『漢字問題集.xlsx』・『漢字解答集.xlsx』を作成するときに、そのコピー元として

 テンプレートブック『漢字学習帳テンプレート.xlsx』を利用します。

 テンプレートブックとは、新規のExcelファイルを複製して作成するときに

 複製元となるExcelファイルのことです。

 セルの書式設定・ページレイアウト・数式等のフォームが定型のExcelファイルを繰り返し

 何回も複製して作成するときは、予め複製元となる定型Excelファイルを一つ作成しておけば、

 複製後のExcelファイルを一つ一つフォーム編集する必要がなくなりとても便利です。

 そこで、前回使用した『漢字問題集.xlsx』のデータをクリアした後、リネームして

 『漢字学習帳テンプレート.xlsx』としますが、今回はそのブックをマクロでオープンする

 ことになるので、予めブックの保存先を決めておきます。

Img10_3_11

 パソコンのCドライブの直下のExcelマクロというフォルダの中に、漢字テンプレートというフォルダを

 作成しておきます。

Img10_3_12

 その漢字テンプレートというフォルダの中に『漢字学習帳テンプレート.xlsx』を保存しておきます。

Img10_3_13

 前回と同様、『漢字問題集.xlsx』・『漢字解答集.xlsx』の保存先は、パソコンのCドライブの直下の

 Excelマクロフォルダの中の漢字学習帳フォルダとします。

Img10_3_14

 今回は、『C:\Excelマクロ\漢字学習帳』フォルダの中は空にします。

Img10_3_15

 『漢字学習帳テンプレート』ブックから『漢字問題集』・『漢字解答集』ブックを新規作成し、

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

 『漢字入力帳』ブックをセーブせずクローズ、『漢字問題集』・『漢字解答集』ブックをセーブしてクローズする

 プログラムを実行するための特別なワークブックを作成します。

 前回使用したExcelマクロ有効ブックをコピーして、次の画像のような

 Excelマクロ有効ブック『10_あるブックから別ブックを更新3.xlsm』を作成します。


『10_あるブックから別ブックを更新3.xlsm』
Img10_3_16

 今回は、『漢字入力帳』・『漢字学習帳テンプレート』・『漢字問題集』・『漢字解答集』ブックを、

 マクロでオープン・クローズすることになるため、

 それらの保存先のパス、もしくは、フルパスを指定することになります。

 『漢字入力帳』ブックの保存先フォルダとして『C5』に『C:\Excelマクロ\漢字入力帳』を、

 保存先ファイルとして『D5』に『漢字入力帳.xlsx』を記入します。

 『漢字学習帳テンプレート』ブックの保存先フォルダとして『C8』に『C:\Excelマクロ\漢字テンプレート』を、

 保存先ファイルとして『D8』に『漢字学習帳テンプレート.xlsx』を記入します。

 『漢字問題集』ブックの保存先フォルダとして『C11』に『C:\Excelマクロ\漢字学習帳』を、

 保存先ファイルとして『D11』に『漢字問題集.xlsx』を記入します。

 『漢字解答集』ブックの保存先フォルダとして『C12』に『C:\Excelマクロ\漢字学習帳』を、

 保存先ファイルとして『D12』に『漢字解答集.xlsx』を記入します。

 『漢字問題集』・『漢字解答集』ブックのバージョンとして『E11』に『V10』を記入します。



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

 それでは、『漢字学習帳テンプレート』ブックから『漢字問題集』・『漢字解答集』ブックを新規作成し、

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

 『漢字入力帳』ブックをセーブせずクローズ、『漢字問題集』・『漢字解答集』ブックをセーブしてクローズする

 プログラムを作成します。


 宣言部を改修します。

定数『入力帳フォルダセル_Cst』に『C5』を代入

定数『テンプレートフォルダセル_Cst』に『C8』を代入

定数『問題集フォルダセル_Cst』に『C11』行を代入

定数『解答集フォルダセル_Cst』に『C12』行を代入

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

定数『テンプレートファイルセル_Cst』に『D8』を代入

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

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

定数『バージョンセル_Cst』に『E11』を代入


テンプレートフォルダを文字列型の変数『TFolder』として定義

テンプレートワークブックを文字列型の変数『TBook』として定義

テンプレートファイルフルパスを文字列型の変数『TPath』として定義

バージョンを文字列型の変数『Vsn』として定義

 Public Const 入力帳フォルダセル_Cst = “C5”

 Public Const テンプレートフォルダセル_Cst = “C8”

 Public Const 問題集フォルダセル_Cst = “C11

 Public Const 解答集フォルダセル_Cst = “C12

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

 Public Const テンプレートファイルセル_Cst = “D8”

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

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

 Public Const バージョンセル_Cst = “E11”


 Public TFolder As String

 Public TBook As String

 Public TPath As String

 Public Vsn As String


 【宣言部】

Option Explicit


 Public Const 入力帳フォルダセル_Cst = “C5”

 Public Const テンプレートフォルダセル_Cst = “C8”

 Public Const 問題集フォルダセル_Cst = “C11

 Public Const 解答集フォルダセル_Cst = “C12

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

 Public Const テンプレートファイルセル_Cst = “D8”

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

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

 Public Const バージョンセル_Cst = “E11”

 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 EFolder As String

 Public EBook As String

 Public EPath As String

 Public TFolder As String

 Public TBook As String

 Public TPath As String

 Public Vsn As String

 Public WFolderMD As String

 Public WFolderKT As String

 Public WPath 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



 『10_あるブックから別ブックを更新3.xlsm』のマクロでは、『漢字学習帳テンプレート』ブックから

 『漢字問題集』・『漢字解答集』ブックを新規作成し、『漢字入力帳』ブックからデータを読みとり、

 『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、『漢字入力帳』ブックをセーブせずクローズ、

 『漢字問題集』・『漢字解答集』ブックをセーブしてクローズします。


 テンプレートブックを元に新規ブックを作成するときは、『Workbooks( ).SaveAs ( )』メソッドを使用します。

Img10_3_22

 『漢字学習帳テンプレート.xlsx』を『C:\Excelマクロ\漢字学習帳\漢字問題集.xlsx』として保存するときは、

 『Workbooks( ).SaveAs ( )』メソッドに、ブック名に『漢字学習帳テンプレート.xlsx』、

 フルパスに『C:\Excelマクロ\漢字学習帳\漢字問題集.xlsx』を指定して保存します。

 Workbooks(“漢字学習帳テンプレート.xlsx”).SaveAs _

 (“C:\Excelマクロ\漢字学習帳\漢字問題集.xlsx”)


 『漢字学習帳テンプレート.xlsx』を『C:\Excelマクロ\漢字学習帳\漢字解答集.xlsx』として保存するときは、

 『Workbooks( ).SaveAs ( )』メソッドに、ブック名に『漢字学習帳テンプレート.xlsx』、

 フルパスに『C:\Excelマクロ\漢字学習帳\漢字解答集.xlsx』を指定して保存します。

 Workbooks(“漢字学習帳テンプレート.xlsx”).SaveAs _

 (“C:\Excelマクロ\漢字学習帳\漢字解答集.xlsx”)



 では、処理部を改修します。


 漢字学習帳テンプレートフォルダ名・ワークブック名・フルパス名の取得

 TFolder = Workbooks(ThisBook).Worksheets(Cst).Range(テンプレートフォルダセル_Cst).Value

 TBook = Workbooks(ThisBook).Worksheets(Cst).Range(テンプレートフォルダセル_Cst).Value

 TPath = TFolder & "\" & TBook

 書込ファイルバージョン名の取得

 Vsn = Workbooks(ThisBook).Worksheets(Cst).Range(バージョンセル_Cst).Value

 漢字学習帳テンプレートブックを開く

 Workbooks.Open (TPath)


 漢字問題集フォルダ名・ワークブック名・フルパス名の取得

 WFolderMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集フォルダセル_Cst).Value

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

 & Vsn & ".xlsx"

 WPath = WFolderMD & "\" & WBookMD


 『漢字学習帳テンプレート.xlsx』を『漢字問題集(フルパス)』として保存

 Workbooks(TBook).SaveAs (WPath)


 漢字問題集ブックのワークシート名をリネーム

 Workbooks(WBookMD).Worksheets(1).Name = WSheetMD


 漢字学習帳テンプレートブックを開く

 Workbooks.Open (TPath)


 漢字解答集フォルダ名・ワークブック名・フルパス名の取得

 WFolderKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集フォルダセル_Cst).Value

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

 & Vsn & ".xlsx"

 WPath = WFolderKT & "\" & WBookKT


 『漢字学習帳テンプレート.xlsx』を『漢字解答集(フルパス)』として保存

 Workbooks(TBook).SaveAs (WPath)


 漢字解答集ブックのワークシート名をリネーム

 Workbooks(WBookKT).Worksheets(1).Name = WSheetKT


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

 サブプロシージャ『他のブックから他のブックへ更新2』は、『他のブックから他のブックへ更新3』に

 名称を変更しておきます。

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


 ThisBook = ActiveWorkbook.Name

 Cst = ActiveSheet.Name


 EFolder = Workbooks(ThisBook).Worksheets(Cst).Range(入力帳フォルダセル_Cst).Value

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

 EPath = EFolder & "\" & EBook

 Workbooks.Open (EPath)


 TFolder = Workbooks(ThisBook).Worksheets(Cst).Range(テンプレートフォルダセル_Cst).Value

 TBook = Workbooks(ThisBook).Worksheets(Cst).Range(テンプレートフォルダセル_Cst).Value

 TPath = TFolder & "\" & TBook


 Vsn = Workbooks(ThisBook).Worksheets(Cst).Range(バージョンセル_Cst).Value


 Workbooks.Open (TPath)


 WFolderMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集フォルダセル_Cst).Value

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

 & Vsn & ".xlsx"

 WPath = WFolderMD & "\" & WBookMD


 Workbooks(TBook).SaveAs (WPath)

 Workbooks(WBookMD).Worksheets(1).Name = WSheetMD


 Workbooks.Open (TPath)


 WFolderKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集フォルダセル_Cst).Value

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

 & Vsn & ".xlsx"

 WPath = WFolderKT & "\" & WBookKT


 Workbooks(TBook).SaveAs (WPath)

 Workbooks(WBookMD).Worksheets(1).Name = WSheetKT


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

 WBook = WBookMD

 Wst = WSheetMD

 Call = ワークシート更新


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

 WBook = WBookKT

 Wst = WSheetKT

 Call = ワークシート更新


 Workbooks(WBookMD).Close SaveChanges:=True

 Workbooks(WBookKT).Close SaveChanges:=True

 Workbooks(EBook).Close SaveChanges:=False


End Sub


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

  サブプロシージャ『ワークシート更新』に変更はありません。

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_あるブックから別ブックを更新3.xlsm』のプログラムが完了しました。

 そこで、『更新』ボタンに、マクロ『他のブックから他のブックへ更新3』をセットします。


『10_あるブックから別ブックを更新3.xlsm』
Img10_3_30

 それでは、『漢字入力帳.xlsx』・『漢字学習帳テンプレート.xlsx』を閉じた状態で、

 『更新』ボタンをクリックしてマクロ『他のブックから他のブックへ更新3』を

 実行してみましょう。

 【漢字学習帳フォルダ】

Img10_3_31

 『漢字学習帳』フォルダの中に、『漢字問題集V10.xlsx』・『漢字解答集V10.xlsx』が作成されています。

 『漢字問題集V10.xlsx』と『漢字解答集V10.xlsx』を開いてください。


『漢字問題集V10.xlsx』
Img10_3_32
『漢字解答集V10.xlsx』
Img10_3_33

 『漢字入力帳』ブックからデータが読みとられ、

 『漢字問題集』・『漢字解答集』ブックへデータが書きこまれました。