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

7 Pythonの汎用型プログラム構成

8 定数で指定したブックを更新

9 セルで指定したブックを更新

10 Excel_VBA/マクロからPythonを実行しブックを更新

11 フルパスを指定したブックを更新

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

13 PythonからExcel_VBA/マクロを実行しブックを更新

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

 今回は、Excel_VBA/マクロからPython-xlwingsを呼び出し実行し、
 テンプレートブックから出力ブックとして新規ブックを作成し、
 入力ブックからデータを読みとり出力ブックへデータを書きこんでみます。


 入力ブック・テンプレートブック・出力ブック・Pythonプログラム実行用ブックの関連図は次のようになります。

Img10_5_01

 前回も、『漢字入力帳.xlsx』からデータを読みとり、

 『漢字問題集.xlsx』、及び、『漢字解答集.xlsx』へ連続してデータを書きこみましたが、

 『漢字問題集.xlsx』、『漢字解答集.xlsx』を予め作成していました。

 今回も、『漢字入力帳.xlsx』からデータを読みとり、

 『漢字問題集.xlsx』、及び、『漢字解答集.xlsx』へ連続してデータを書きこみますが、

 今回は、『漢字問題集.xlsx』、『漢字解答集.xlsx』をテンプレートブックから出力ブックとして作成します。


 Excel_VBA/マクロからPythonを実行しブックを更新することになるため、

 Excelマクロ有効ブック(*.xlsm)を作成します。

 Pythonプログラムファイルを保存しているフォルダ内に、

 入力ブック名と出力ブック名を記載した『ブックからブックへ更新マクロ3.xlsm』を作成します。

Img10_5_02

 Pythonファイルを呼び出しコマンドプロンプトで実行するExcel_VBA/マクロの構文は次の通りです。

Img10_5_06

  ※『WshShellオブジェクト』をつかうためには、下記のアドイン設定が必要です。

Img10_5_08

    『Windows Script Host Object Model』をチェックして『OK』をクリック


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

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

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

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

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

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

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

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

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

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

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

Img10_5_11

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

 作成しておきます。

Img10_5_12

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

Img10_5_13

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

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

Img10_5_14

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

Img10_5_15

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

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

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

 Pythonプログラムを実行するためのExcelマクロ有効ブックを作成します。


 前回使用したExcelマクロ有効ブックをコピーして、

 次の画像のようなExcelマクロ有効ブック『ブックからブックへ更新マクロ3.xlsm』を作成します。

Img10_5_16

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

 Pythonプログラムでオープン・クローズすることになるため、

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

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

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

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

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

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

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

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

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

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


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

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

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

 プログラムを作成しましょう。


 ワークシート名を変更する書式は次の通りです。

Img10_5_17

 『漢字問題集』、または、『漢字解答集』を作成する共通の処理を関数『kosin_kanjigakusyutyo』として定義、

 その関数『kosin_kanjigakusyutyo』を『漢字問題集』の作成、及び、『漢字解答集』の作成でそれぞれ呼び出す

 メイン処理を関数『main』として定義、

 『if __name__ == '__main__':』のときに関数『main』を呼び出す

 といった仕様のPythonプログラム『PXS8120.py』を作成します。

 Pythonプログラム『PXS8110.py』をコピーして、Pythonプログラム『PXS8120.py』に名前を変えます。


 オブジェクト生成部・定数部を改修します。

 

定数『CBook』に『ブックからブックへ更新マクロ3.xlsm』を格納

定数『CBook』よりブックオブジェクト『CB』を生成

ブックオブジェクト『CB』の1シート目よりシートオブジェクト『Cst』を生成

フォルダー名『EFolder』にシート『Cst』のセル番地:C5の値を格納

ブック名『EBook』にシート『Cst』のセル番地:D5の値を格納

フルパス名『EPath』に『EFolder』と『/』と『EBook』を文字連結した値を格納

フルパス名『EPath』よりブックオブジェクト『EB』を生成

 

フォルダー名『TFolder』にシート『Cst』のセル番地:C8の値を格納

ブック名『TBook』にシート『Cst』のセル番地:D8の値を格納

フルパス名『TPath』に『TFolder』と『/』と『TBook』を文字連結した値を格納

バージョン名『WVersion』にシート『Cst』のセル番地:E11の値を格納

フォルダー名『WFolderMD』にシート『Cst』のセル番地:C11の値を格納

ブック名『WBookMD』にシート『Cst』のセル番地:D11の値とバージョン名『WVersion』と『.xlsx』を

文字連結した値を格納

フルパス名『WPathMD』に『WFolderMD』と『/』と『WBookMD』を文字連結した値を格納

 

フォルダー名『WFolderKT』にシート『Cst』のセル番地:C12の値を格納

ブック名『WBookKT』にシート『Cst』のセル番地:D12の値とバージョン名『WVersion』と『.xlsx』を

文字連結した値を格納

フルパス名『WPathKT』に『WFolderKT』と『/』と『WBookKT』を文字連結した値を格納

 

import xlwings as xw

CBook = 'ブックからブックへ更新マクロ3.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)

 

TFolder = Cst.range('C8').value

TBook = Cst.range('D8').value

TPath = TFolder + "/" + TBook

WVersion = Cst.range('E11').value

WFolderMD = Cst.range('C11').value

WBookMD = Cst.range('D11').value + WVersion + ".xlsx"

WPathMD = WFolderMD + "/" + WBookMD

 

WFolderKT = Cst.range('C12').value

WBookKT = Cst.range('D12').value + WVersion + ".xlsx"

WPathKT = WFolderKT + "/" + WBookKT

 


 関数『kosin_kanjigakusyutyo』を改修します。

引数に『WPath』、『WSheet』を備えた関数『kosin_kanjigakusyutyo』を定義

テンプレートフルパス『TPath』よりブックオブジェクト『WB』を生成

ブックオブジェクト『WB』の1シート目よりシートオブジェクト『Wst』を生成

シートオブジェクト『Wst』の名前をシート名『WSheet』に変更

ブックオブジェクト『WB』をフルパス『WPath』にセーブ

ブックオブジェクト『WB』をクローズ

def kosin_kanjigakusyutyo(WPath, WSheet):

WB = xw.Book(TPath)

Wst = WB.sheets[0]

Wst.name = WSheet

WB.save(WPath)

WB.close()


 関数『main』については、変更点はありません。


 『漢字入力帳.xlsx』からデータを読みとり、『漢字問題集.xlsx』、及び、『漢字解答集.xlsx』へ連続して

 データを書きこむPythonファイルは次のようになります。

 【Pythonファイル】

# PXS8120.py

import xlwings as xw

CBook = 'ブックからブックへ更新マクロ3.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

TFolder = Cst.range('C8').value

TBook = Cst.range('D8').value

TPath = TFolder + "/" + TBook

WVersion = Cst.range('E11').value

WFolderMD = Cst.range('C11').value

WBookMD = Cst.range('D11').value + WVersion + ".xlsx"

WPathMD = WFolderMD + "/" + WBookMD

WSheetMD = '漢字問題集'

WFolderKT = Cst.range('C12').value

WBookKT = Cst.range('D12').value + WVersion + ".xlsx"

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(TPath)

Wst = WB.sheets[0]

Wst.name = 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\PXS8120.py』を

変数『py_file』に格納

"python " と Pythonファイルのフルパス:変数『py_file』を結合した値を変数『cmd_str』に格納

変数『cmd_str』で指定したPythonファイルをコマンドプロンプトで実行


 Pythonファイルを呼び出しコマンドプロンプトで実行するExcel_VBA/マクロは次のようになります。

 【Excel_VBA/マクロ】

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

  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\PXS8120.py"

  cmd_str = "python " & py_file

  Call Wsh.Run(cmd_str, 0, True)

End Sub


 それから、『ブックからブックへ更新マクロ3.xlsm』の更新ボタンに

 Excel_VBA/マクロ『ブックからブックへ更新3』をセットします。


Img10_5_20

 それでは、『更新』ボタンをクリックしてExcel_VBA/マクロ『ブックからブックへ更新3』を実行してみましょう。


 【漢字学習帳フォルダ】

Img10_5_21

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

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


『漢字問題集V10.xlsx』
Img10_5_22
『漢字解答集V10.xlsx』
Img10_5_23

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

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