【他のレンジ/シート/ブックを更新する】
4 セルで指定したシートを更新
5 複数シートを連続更新
6 Subプロシージャの定義と呼び出し
7 あるブックから別ブックを更新
8 他のブックをオープン・クローズ
9 テンプレートから出力ブックを作成し更新
10 ブック保存時の確認メッセージの非表示
他のブックをオープン・クローズ
今回は、Excel VBA/マクロによって、フルパスを指定した入力ブックをオープンしてデータを読みとり、
フルパスを指定した出力ブックをオープンしてデータを書きこんでセーブしてクローズしてみます。
入力ブック及び出力ブックをオープンして、入力ブックからデータを読みとり、
出力ブックへデータを書きこみ、入力ブックをセーブせずクローズ、
出力ブックをセーブしてクローズするマクロをつくってみます。
入力ブックと出力ブックの間でデータを読み書きすることになるため、
今回も、そのマクロの実行用ブックを作成してみます。
入力ブック・出力ブック・マクロ実行用ブックの関連図は次のようになります。
【『他のブックから他のブックへ更新マクロ2』実行前】
【『他のブックから他のブックへ更新マクロ2』実行後】
1. ブックのフルパスを指定
最初に、10問の漢字文字列を用意した『漢字入力帳.xlsx』を作成しますが、
今回はそのブックをマクロでオープンすることになるので、予めブックの保存先を決めておきます。
パソコンのCドライブの直下にExcelマクロというフォルダを作成し、更にその中に漢字入力帳という
フォルダを作成しておきます。
その漢字入力帳というフォルダの中に『漢字入力帳.xlsx』を保存しておきます。
続けて、白紙の『漢字問題集.xlsx』・『漢字解答集.xlsx』を作成しますが、
今回はそれらのブックをマクロでオープンすることになるので、予めブックの保存先を決めておきます。
パソコンのCドライブの直下のExcelマクロというフォルダの中に、漢字学習帳というフォルダを
作成しておきます。
その漢字学習帳というフォルダの中に白紙の『漢字問題集.xlsx』・『漢字解答集.xlsx』を保存しておきます。
『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックをオープンして、
『漢字入力帳』ブックからデータを読みとり、
『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、
『漢字入力帳』ブックをセーブせずクローズ、
『漢字問題集』・『漢字解答集』ブックをセーブしてクローズする
マクロを実行するためのワークブックを作成します。
前回使用したExcelマクロ有効ブックをコピーして、
次の画像のようなExcelマクロ有効ブック『10_他のブックを更新2.xlsm』を作成します。
今回は、『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックを、マクロでオープン・クローズする
ことになるため、それらの保存先のパス、もしくは、フルパスを指定することになります。
『漢字入力帳』ブックの保存先フォルダとして『C5』に『C:\Excelマクロ\漢字入力帳』を、
保存先ファイルとして『D5』に『漢字入力帳.xlsx』を記入します。
『漢字問題集』ブックの保存先フォルダとして『C8』に『C:\Excelマクロ\漢字学習帳』を、
保存先ファイルとして『D8』に『漢字問題集.xlsx』を記入します。
『漢字解答集』ブックの保存先フォルダとして『C9』に『C:\Excelマクロ\漢字学習帳』を、
保存先ファイルとして『D9』に『漢字解答集.xlsx』を記入します。
パスはファイルやフォルダの置いてある場所のことです。
今回のケースでは、『C:\Excelマクロ\漢字入力帳』や『C:\Excelマクロ\漢字学習帳』のことをいいます。
フルパスはファイルやフォルダの置いてある場所にファイル名を付け足したものです。
今回のケースでは、
『C:\Excelマクロ\漢字入力帳\漢字入力帳.xlsx』、
『C:\Excelマクロ\漢字学習帳\漢字問題集.xlsx』、
『C:\Excelマクロ\漢字学習帳\漢字解答集.xlsx』、
となります。
それでは、『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックをオープンして、
『漢字入力帳』ブックからデータを読みとり、
『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、
『漢字入力帳』ブックをセーブせずクローズ、
『漢字問題集』・『漢字解答集』ブックをセーブしてクローズするプログラムを作成します。
宣言部を改修します。
定数『入力帳フォルダセル_Cst』に『C5』を代入
定数『問題集フォルダセル_Cst』に『C8』行を代入
定数『解答集フォルダセル_Cst』に『C9』行を代入
定数『入力帳ファイルセル_Cst』に『D5』を代入
定数『問題集ファイルセル_Cst』に『D8』行を代入
定数『解答集ファイルセル_Cst』に『D9』行を代入
読取フォルダを文字列型の変数『EFolder』として定義
読取ファイルフルパスを文字列型の変数『EPath』として定義
問題集フォルダを文字列型の変数『WFolderMD』として定義
解答集フォルダを文字列型の変数『WFolderKT』として定義
書込ファイルフルパスを文字列型の変数『WPath』として定義
Public Const 入力帳フォルダセル_Cst = “C5”
Public Const 問題集フォルダセル_Cst = “C8”
Public Const 解答集フォルダセル_Cst = “C9”
Public Const 入力帳ファイルセル_Cst = “D5”
Public Const 問題集ファイルセル_Cst = “D8”
Public Const 解答集ファイルセル_Cst = “D9”
Public EFolder As String
Public EPath As String
Public WFolderMD As String
Public WFolderKT As String
Public WPath As String
【宣言部】
Option Explicit
Public Const 入力帳フォルダセル_Cst = “C5”
Public Const 問題集フォルダセル_Cst = “C8”
Public Const 解答集フォルダセル_Cst = “C9”
Public Const 入力帳ファイルセル_Cst = “D5”
Public Const 問題集ファイルセル_Cst = “D8”
Public Const 解答集ファイルセル_Cst = “D9”
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 EPath As String
Public WFolderMD As String
Public WFolderKT As String
Public WPath 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
2. ブックのオープンとクローズ
『10_他のブックを更新2.xlsm』のマクロでは、
『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックをオープンして、
『漢字入力帳』ブックからデータを読みとり、
『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、
『漢字入力帳』ブックをセーブせずクローズ、
『漢字問題集』・『漢字解答集』ブックをセーブしてクローズします。
A. ブックのオープン
ワークブックをオープンするときは、『Workbooks.Open』メソッドを使用します。
『漢字入力帳』ブックをオープンするときは、『Workbooks.Open』メソッドをつかい、
FileNameに『C:\Excelマクロ\漢字入力帳\漢字入力帳.xlsx』を指定して、
Workbooks.Open (“C:\Excelマクロ\漢字入力帳\漢字入力帳.xlsx”)
『漢字問題集』ブックをオープンするときは、『Workbooks.Open』メソッドをつかい、
FileNameに『C:\Excelマクロ\漢字学習帳\漢字問題集.xlsx』を指定して、
Workbooks.Open (“C:\Excelマクロ\漢字学習帳\漢字問題集.xlsx”)
『漢字解答集』ブックをオープンするときは、『Workbooks.Open』メソッドをつかい、
FileNameに『C:\Excelマクロ\漢字学習帳\漢字解答集.xlsx』を指定して、
Workbooks.Open (“C:\Excelマクロ\漢字学習帳\漢字解答集.xlsx”)
とします。
B. ブックのクローズ
ワークブックをクローズするときは、『Workbooks( ).Close』メソッドを使用します。
ワークブックをクローズするときに、それをセーブつまり保存するかどうかは、
引数『SaveChanges』に指定する定数によって決まります。
『漢字問題集』ブックをセーブしてクローズするときは、『Workbooks( ).Close』メソッドをつかい、
ブック名に『漢字問題集.xlsx』、SaveChangesに『True:保存する』を指定して、
Workbooks(“漢字問題集.xlsx”).Close SaveChanges:=True
『漢字解答集』ブックをセーブしてクローズするときは、『Workbooks( ).Close』メソッドをつかい、
ブック名に『漢字解答集.xlsx』、SaveChangesに『True:保存する』を指定して、
Workbooks(“漢字解答集.xlsx”).Close SaveChanges:=True
『漢字入力帳』ブックをセーブせずクローズするときは、『Workbooks( ).Close』メソッドをつかい、
ブック名に『漢字入力帳.xlsx』、SaveChangesに『False:保存しない』を指定して、
Workbooks(“漢字入力帳.xlsx”).Close SaveChanges:=False
とします。
3. 漢字問題集ブックと漢字解答集ブックを更新
処理部を改修します。
A. ブックのオープン
漢字入力帳フォルダ名・ワークブック名・フルパス名の取得
EFolder = Workbooks(ThisBook).Worksheets(Cst).Range(入力帳フォルダセル_Cst).Value
EBook = Workbooks(ThisBook).Worksheets(Cst).Range(入力帳ファイルセル_Cst).Value
EPath = EFolder & "\" & EBook
フルパスを指定して漢字入力帳ブックを開く
Workbooks.Open (EPath)
漢字問題集フォルダ名・ワークブック名・フルパス名の取得
WFolderMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集フォルダセル_Cst).Value
WBookMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集ファイルセル_Cst).Value
WPath = WFolderMD & "\" & WBookMD
フルパスを指定して漢字問題集ブックを開く
Workbooks.Open (WPath)
漢字解答集フォルダ名・ワークブック名・フルパス名の取得
WFolderKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集フォルダセル_Cst).Value
WBookKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集ファイルセル_Cst).Value
WPath = WFolderKT & "\" & WBookKT
フルパスを指定して漢字解答集ブックを開く
Workbooks.Open (WPath)
B. ブックのクローズ
漢字問題集ブックを保存して閉じる
Workbooks(WBookMD).Close SaveChanges:=True
漢字解答集ブックを保存して閉じる
Workbooks(WBookKT).Close SaveChanges:=True
漢字入力帳ブックを保存せずに閉じる
Workbooks(EBook).Close SaveChanges:=False
【サブプロシージャ『他のブックから他のブックへ更新2』】
サブプロシージャ『他のブックから他のブックへ更新』は、『他のブックから他のブックへ更新2』に
名称を変更しておきます。
Sub 他のブックから他のブックへ更新2()
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)
WFolderMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集フォルダセル_Cst).Value
WBookMD = Workbooks(ThisBook).Worksheets(Cst).Range(問題集ファイルセル_Cst).Value
WPath = WFolderMD & "\" & WBookMD
Workbooks.Open (WPath)
WFolderKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集フォルダセル_Cst).Value
WBookKT = Workbooks(ThisBook).Worksheets(Cst).Range(解答集ファイルセル_Cst).Value
WPath = WFolderKT & "\" & WBookKT
Workbooks.Open (WPath)
'◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 漢字問題集 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆
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_他のブックを更新2.xlsm』のプログラムが完了しました。
そこで、『更新』ボタンに、マクロ『他のブックから他のブックへ更新2』をセットします。
それでは、『漢字入力帳.xlsx』・『漢字問題集.xlsx』・『漢字解答集.xlsx』を閉じた状態で、
『更新』ボタンをクリックしてマクロ『他のブックから他のブックへ更新2』を実行してみましょう。
『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックをオープンして、『漢字入力帳』ブックからデータを読みとり、
『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、『漢字入力帳』ブックをセーブせずクローズ、
『漢字問題集』・『漢字解答集』ブックをセーブしてクローズしました。