【他のレンジ/シート/ブックを更新する】
6 関数の定義と呼び出し
7 Pythonの汎用型プログラム構成
8 定数で指定したブックを更新
9 セルで指定したブックを更新
10 Excel_VBA/マクロからPythonを実行しブックを更新
11 フルパスを指定したブックを更新
12 テンプレートから出力ブックを作成し更新
フルパスを指定したブックを更新
今回は、Excel_VBA/マクロからPython-xlwingsを呼び出し実行し、
フルパスを指定した入力ブックからデータを読みとり
フルパスを指定した出力ブックへデータを書きこんでみます。
入力ブック・出力ブック・Pythonプログラム実行用ブックの関連図は次のようになります。
前回も、『漢字入力帳.xlsx』からデータを読みとり、
『漢字問題集.xlsx』、及び、『漢字解答集.xlsx』へ連続してデータを書きこみましたが、
『漢字入力帳.xlsx』、『漢字問題集.xlsx』、『漢字解答集.xlsx』といった
Excelブック名のみをExcelマクロ有効ブックで指定していました。
今回も、『漢字入力帳.xlsx』からデータを読みとり、
『漢字問題集.xlsx』、及び、『漢字解答集.xlsx』へ連続してデータを書きこみますが、
今回は、『漢字入力帳.xlsx』、『漢字問題集.xlsx』、『漢字解答集.xlsx』の
フルパスをExcelマクロ有効ブックで指定します。
Excel_VBA/マクロからPythonを実行しブックを更新することになるため、
Excelマクロ有効ブック(*.xlsm)を作成します。
Pythonプログラムファイルを保存しているフォルダ内に、
入力ブック名と出力ブック名を記載した『ブックからブックへ更新マクロ2.xlsm』を作成します。
Pythonファイルを呼び出しコマンドプロンプトで実行するExcel_VBA/マクロの構文は次の通りです。
※『WshShellオブジェクト』をつかうためには、下記のアドイン設定が必要です。
『Windows Script Host Object Model』をチェックして『OK』をクリック
最初に、10問の漢字文字列を用意した『漢字入力帳.xlsx』を作成しますが、
今回はそのブックをPythonプログラムでオープンすることになるので、予めブックの保存先を決めておきます。
パソコンのCドライブの直下にExcelマクロというフォルダを作成し、更にその中に漢字入力帳という
フォルダを作成しておきます。
その漢字入力帳というフォルダの中に『漢字入力帳.xlsx』を保存しておきます。
続けて、白紙の『漢字問題集.xlsx』・『漢字解答集.xlsx』を作成しますが、
今回はそれらのブックをPythonプログラムでオープンすることになるので、予めブックの保存先を決めておきます。
パソコンのCドライブの直下のExcelマクロというフォルダの中に、漢字学習帳というフォルダを
作成しておきます。
その漢字学習帳というフォルダの中に白紙の『漢字問題集.xlsx』・『漢字解答集.xlsx』を保存しておきます。
『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックをオープンして、
『漢字入力帳』ブックからデータを読みとり、
『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、
『漢字入力帳』ブックをセーブせずクローズ、
『漢字問題集』・『漢字解答集』ブックをセーブしてクローズする
Pythonプログラムを実行するためのExcelマクロ有効ブックを作成します。
前回使用したExcelマクロ有効ブックをコピーして、
次の画像のようなExcelマクロ有効ブック『ブックからブックへ更新マクロ2.xlsm』を作成します。
今回は、『漢字入力帳』・『漢字問題集』・『漢字解答集』ブックを、Pythonプログラムでオープン・クローズする
ことになるため、それらの保存先のパス、もしくは、フルパスを指定することになります。
『漢字入力帳』ブックの保存先フォルダとして『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』となります。
それでは、『漢字入力帳』ブックからデータを読みとり、
『漢字問題集』・『漢字解答集』ブックへデータを書きこみ、
『漢字問題集』・『漢字解答集』ブックをセーブしてクローズするプログラムを作成しましょう。
WorkbookオブジェクトをWorkbookファイルとしてセーブする書式は次の通りです。
Workbookオブジェクトをクローズする書式は次の通りです。
『漢字問題集』、または、『漢字解答集』を作成する共通の処理を関数『kosin_kanjigakusyutyo』として定義、
その関数『kosin_kanjigakusyutyo』を『漢字問題集』の作成、及び、『漢字解答集』の作成でそれぞれ呼び出す
メイン処理を関数『main』として定義、
『if __name__ == '__main__':』のときに関数『main』を呼び出す
といった仕様のPythonプログラム『PXS8110.py』を作成します。
Pythonプログラム『PXS8100.py』をコピーして、Pythonプログラム『PXS8110.py』に名前を変えます。
オブジェクト生成部・定数部を改修します。
定数『CBook』に『ブックからブックへ更新マクロ2.xlsm』を格納
定数『CBook』よりブックオブジェクト『CB』を生成
ブックオブジェクト『CB』の1シート目よりシートオブジェクト『Cst』を生成
フォルダー名『EFolder』にシート『Cst』のセル番地：C5の値を格納
ブック名『EBook』にシート『Cst』のセル番地：D5の値を格納
フルパス名『EPath』に『EFolder』と『/』と『EBook』を文字連結した値を格納
フルパス名『EPath』よりブックオブジェクト『EB』を生成
フォルダー名『WFolderMD』にシート『Cst』のセル番地：C8の値を格納
ブック名『WBookMD』にシート『Cst』のセル番地：D8の値を格納
フルパス名『WPathMD』に『WFolderMD』と『/』と『WBookMD』を文字連結した値を格納
フォルダー名『WFolderKT』にシート『Cst』のセル番地：C9の値を格納
ブック名『WBookKT』にシート『Cst』のセル番地：D9の値を格納
フルパス名『WPathKT』に『WFolderKT』と『/』と『WBookKT』を文字連結した値を格納
import xlwings as xw
CBook = 'ブックからブックへ更新マクロ2.xlsm'
CB = xw.Book(CBook)
Cst = CB.sheets[0]
EFolder = Cst.range('C5').value
EBook = Cst.range('D5').value
EPath = EFolder + "/" + EBook
EB = xw.Book(EPath)
WFolderMD = Cst.range('C8').value
WBookMD = Cst.range('D8').value
WPathMD = WFolderMD + "/" + WBookMD
WFolderKT = Cst.range('C9').value
WBookKT = Cst.range('D9').value
WPathKT = WFolderKT + "/" + WBookKT
関数『kosin_kanjigakusyutyo』を改修します。
引数に『WPath』、『WSheet』を備えた関数『kosin_kanjigakusyutyo』を定義
ブック引数『WPath』よりブックオブジェクト『WB』を生成
ブックオブジェクト『WB』のシート引数『WSheet』よりシートオブジェクト『Wst』を生成
ブックオブジェクト『WB』をフルパス『WPath』にセーブ
ブックオブジェクト『WB』をクローズ
def kosin_kanjigakusyutyo(WPath, WSheet):
WB = xw.Book(WPath)
Wst = WB.sheets[WSheet]
WB.save(WPath)
WB.close()
関数『main』を改修します。
関数『main』を定義
引数『WPath』に定数『WPathMD』、引数『WSheet』に定数『WSheetMD』を格納して
関数『kosin_kanjigakusyutyo』を呼び出し
引数『WPath』に定数『WPathKT』、引数『WSheet』に定数『WSheetKT』を格納して
関数『kosin_kanjigakusyutyo』を呼び出し
ブックオブジェクト『EB』をクローズ
def main():
kosin_kanjigakusyutyo(WPathMD, WSheetMD)
kosin_kanjigakusyutyo(WPathKT, WSheetKT)
EB.close()
『漢字入力帳.xlsx』からデータを読みとり、『漢字問題集.xlsx』、及び、『漢字解答集.xlsx』へ連続して
データを書きこむPythonファイルは次のようになります。
【Pythonファイル】
# PXS8110.py
import xlwings as xw
CBook = 'ブックからブックへ更新マクロ2.xlsm'
CB = xw.Book(CBook)
Cst = CB.sheets[0]
EFolder = Cst.range('C5').value
EBook = Cst.range('D5').value
EPath = EFolder + "/" + EBook
EB = xw.Book(EPath)
Est = EB.sheets['漢字入力帳']
読取問題番号列 = 1
読取列 = 2
最大枠数 = 4
漢字最小列 = 3
漢字最大列 = 6
読取最小行 = 2
読取最大行 = Est.range(Est.cells.last_cell.row, 2).end('up').row
WFolderMD = Cst.range('C8').value
WBookMD = Cst.range('D8').value
WPathMD = WFolderMD + "/" + WBookMD
WSheetMD = '漢字問題集'
WFolderKT = Cst.range('C9').value
WBookKT = Cst.range('D9').value
WPathKT = WFolderKT + "/" + WBookKT
WSheetKT = '漢字解答集'
最大列 = 10
書込列数 = 5
書込ステップ列 = -2
書込問題番号最小行 = 1
書込ステップ行 = 15
def main():
kosin_kanjigakusyutyo(WPathMD, WSheetMD)
kosin_kanjigakusyutyo(WPathKT, WSheetKT)
EB.close()
def kosin_kanjigakusyutyo(WPath, WSheet):
WB = xw.Book(WPath)
Wst = WB.sheets[WSheet]
# ===== 読取行の最小行から最大行へのループにより読取行をカウントアップ =====
for 読取行 in range(読取最小行, 読取最大行 + 1):
問題番号 = Est.range(読取行, 読取問題番号列).value
書込問題番号行 = int((問題番号 - 1) / 書込列数) * 書込ステップ行 \
+ 書込問題番号最小行
最小行 = 書込問題番号行 + 1
列番号 = 最大列 + ((問題番号 - 1) % 書込列数) * 書込ステップ列
列英字名 = Wst.range(1, 列番号).get_address \
(False, False).replace("1", "")
枠領域 = 列英字名 + str(書込問題番号行)
Wst.range(枠領域).value = 問題番号
Wst.range(枠領域).api.Font.Size = 10
txt = Est.range(読取行, 読取列).value
最大文字数 = len(txt)
# == 文字列の先頭文字から最終文字へのループにより位置インデックスをカウントアップ ==
for 位置インデックス in range(0, 最大文字数):
行番号 = 最小行 + 位置インデックス
Wst.range(行番号, 列番号).value = txt[位置インデックス]
# ===== 漢字列の最小列から最大列へのループにより漢字列をカウントアップ =====
for 漢字列 in range(漢字最小列, 漢字最大列 + 1):
漢字名 = Est.range(読取行, 漢字列).value
if 漢字名 is not None:
位置インデックス = Est.range(読取行, 読取列).value.find(漢字名)
if 位置インデックス >= 0:
行番号 = 最小行 + 位置インデックス
列英字名 = Wst.range(1, 列番号).get_address \
(False, False).replace("1", "")
枠領域 = 列英字名 + str(行番号)
Wst.range(枠領域).api.Borders.LineStyle = 1
if WSheet == WSheetMD:
Wst.range(枠領域).value = ""
ふりがな読取列 = 漢字列 + 最大枠数
ふりがな名 = Est.range(読取行, ふりがな読取列).value
if ふりがな名 is not None:
ふりがな列 = 列番号 + 1
列英字名 = Wst.range(1, ふりがな列).get_address \
(False, False).replace("1", "")
枠領域 = 列英字名 + str(行番号)
Wst.range(枠領域).value = ふりがな名
Wst.range(枠領域).api.Font.Size = 8
Wst.range(枠領域).api.HorizontalAlignment = -4131
Wst.range(枠領域).api.Orientation = -4166
Wst.range(枠領域).api.ShrinkToFit = True
WB.save(WPath)
WB.close()
if __name__ == '__main__':
main()
次に、Pythonファイルを呼び出しコマンドプロンプトで実行するExcel_VBA/マクロを作成します。
WshShellオブジェクト(Windows Script Host Shell Object)の変数『Wsh』を同型で定義
実行するPythonファイルのフルパスの変数『py_file』を文字列型で定義
実行するコマンドの変数『cmd_str』を文字列型で定義
WshShellオブジェクトを生成し変数『Wsh』に格納
実行するPythonファイルのフルパス『C:\Python_Excel_PG\Python_Excel_xlwings\PXS8110.py』を
変数『py_file』に格納
"python " と Pythonファイルのフルパス：変数『py_file』を結合した値を変数『cmd_str』に格納
変数『cmd_str』で指定したPythonファイルをコマンドプロンプトで実行
Pythonファイルを呼び出しコマンドプロンプトで実行するExcel_VBA/マクロは次のようになります。
【Excel_VBA/マクロ】
Sub ブックからブックへ更新2()
Dim Wsh As WshShell
Dim py_file As String
Dim cmd_str As String
Set Wsh = CreateObject("WScript.Shell")
py_file = "C:\Python_Excel_PG\Python_Excel_xlwings\PXS8110.py"
cmd_str = "python " & py_file
Call Wsh.Run(cmd_str, 0, True)
End Sub
それから、『ブックからブックへ更新マクロ2.xlsm』の更新ボタンに
Excel_VBA/マクロ『ブックからブックへ更新2』をセットします。
それでは、『更新』ボタンをクリックしてExcel_VBA/マクロ『ブックからブックへ更新2』を実行してみましょう。
Excel_VBA/マクロ『ブックからブックへ更新2』から
フルパス『C:\Python_Excel_PG\Python_Excel_xlwings\PXS8110.py』を呼び出し
そのPythonファイルをコマンドプロンプトで実行した結果、
『漢字入力帳』ブックからデータが読みとられ、『漢字問題集』・『漢字解答集』ブックへデータが書きこまれ、
『漢字問題集』・『漢字解答集』ブックがセーブされ閉じられました。