【他のレンジ/シート/ブックを更新する】
4 セルで指定したシートを更新
5 複数シートを連続更新
6 Subプロシージャの定義と呼び出し
7 あるブックから別ブックを更新
8 他のブックをオープン・クローズ
9 テンプレートから出力ブックを作成し更新
10 確認メッセージの非表示
テンプレートから出力ブックを作成し更新
今回は、Excel VBA/マクロによって、テンプレートブックから出力ブックとして新規ブックを作成し、
入力ブックからデータを読みとり出力ブックへデータを書きこんでみます。
テンプレートブックから出力ブックとして新規ブックを作成し、
入力ブックからデータを読みとり、出力ブックへデータを書きこみ、
入力ブックをセーブせずクローズ、出力ブックをセーブしてクローズする
マクロをつくってみます。
テンプレートブックから出力ブックとして新規ブックを作成した後、
入力ブックと出力ブックの間でデータを読み書きすることになるため、
今回も、そのマクロの実行用ブックを作成してみます。
入力ブック・テンプレートブック・出力ブック・マクロ実行用ブックの関連図は
次のようになります。
【『他のブックから他のブックへ更新マクロ3』】
1. テンプレートを作成
前回と同様に、『漢字入力帳.xlsx』を『C:\Excelマクロ\漢字入力帳』に保存しておきます。
今回は『漢字問題集.xlsx』・『漢字解答集.xlsx』を作成するときに、そのコピー元として
テンプレートブック『漢字学習帳テンプレート.xlsx』を利用します。
テンプレートブックとは、新規のExcelファイルを複製して作成するときに
複製元となるExcelファイルのことです。
セルの書式設定・ページレイアウト・数式等のフォームが定型のExcelファイルを繰り返し
何回も複製して作成するときは、予め複製元となる定型Excelファイルを一つ作成しておけば、
複製後のExcelファイルを一つ一つフォーム編集する必要がなくなりとても便利です。
そこで、前回使用した『漢字問題集.xlsx』のデータをクリアした後、リネームして
『漢字学習帳テンプレート.xlsx』としますが、今回はそのブックをマクロでオープンする
ことになるので、予めブックの保存先を決めておきます。
パソコンのCドライブの直下のExcelマクロというフォルダの中に、漢字テンプレートというフォルダを
作成しておきます。
その漢字テンプレートというフォルダの中に『漢字学習帳テンプレート.xlsx』を保存しておきます。
前回と同様、『漢字問題集.xlsx』・『漢字解答集.xlsx』の保存先は、パソコンのCドライブの直下の
Excelマクロフォルダの中の漢字学習帳フォルダとします。
今回は、『C:\Excelマクロ\漢字学習帳』フォルダの中は空にします。
『漢字学習帳テンプレート』ブックから『漢字問題集』・『漢字解答集』ブックを新規作成し、
『漢字入力帳』ブックからデータを読みとり、『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、
『漢字入力帳』ブックをセーブせずクローズ、『漢字問題集』・『漢字解答集』ブックをセーブしてクローズする
プログラムを実行するための特別なワークブックを作成します。
前回使用したExcelマクロ有効ブックをコピーして、次の画像のような
Excelマクロ有効ブック『10_他のブックを更新3.xlsm』を作成します。
今回は、『漢字入力帳』・『漢字学習帳テンプレート』・『漢字問題集』・『漢字解答集』ブックを、
マクロでオープン・クローズすることになるため、
それらの保存先のパス、もしくは、フルパスを指定することになります。
『漢字入力帳』ブックの保存先フォルダとして『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 ( )』メソッドを使用します。
『漢字学習帳テンプレート.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』をセットします。
それでは、『漢字入力帳.xlsx』・『漢字学習帳テンプレート.xlsx』を閉じた状態で、
『更新』ボタンをクリックしてマクロ『他のブックから他のブックへ更新3』を
実行してみましょう。
【漢字学習帳フォルダ】
『漢字学習帳』フォルダの中に、『漢字問題集V10.xlsx』・『漢字解答集V10.xlsx』が作成されています。
『漢字問題集V10.xlsx』と『漢字解答集V10.xlsx』を開いてください。
『漢字入力帳』ブックからデータが読みとられ、
『漢字問題集』・『漢字解答集』ブックへデータが書きこまれました。