自習用コンテンツ その8(https://automate.sct.co.jp/knowledge/10128/)の内容は如何でしたでしょうか？
簡単に出来てしまった方も、難しかった方もいたかと思います。
下記では、解説を書いていきます。
(解答例のコードは、ページ最下部に書いています。)
■観点
今回のコンテンツでは、「ファイルを開閉するタイミング」および「その条件判断」を観点としました。
■1. 変数宣言
必須の変数としては、「Excelの書込み先の行の数字」となります。
書込み場所が固定ではなく、なおかつ書込み対象行数も固定ではないので、変数で動的に設定することが必須となります。
書込み位置の初期位置は「5行目」なので、初期値は「5」で作成します。
また、今回の解説では、「Excelファイルのありか」「全体リストのファイル名」「雛形のファイル名」も変数化します。
<AMVARIABLE NAME="var_Folder">%GetDesktopDirectory()%\00010128</AMVARIABLE> <AMVARIABLE NAME="var_ALL_DATA" VALUE="ALL_DATA.xlsx" /> <AMVARIABLE NAME="var_Template" VALUE="雛形.xlsx" /> <AMVARIABLE NAME="var_RowNo" VALUE="5" TYPE="number" />
■2. データの抽出およびデータセット化
データ一覧のExcelからデータを抽出し、データセットとして格納します。
なお、サンプルでは1行目（見出し行）も併せて取得していますが、2行目から取得しても問題ありません。
<AMEXCEL SESSION="ExcelSession1" WORKBOOK="%var_Folder%\%var_ALL_DATA%" /> <AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" ACTION="rangebyreference" RESULTDATASET="ds_ALL" STARTCELLREF="A1" LASTCELL="YES" />
■3. 転記先のExcelを開く
転記先のExcelをループよりも前で開きます。
理由としては、次のループでは、ループの最後で「Excelを閉じる」処理と「Excelを開く」処理を同時に実施するためです。
すべてのループの回で同じことを実施するわけでは無いので、どこかで帳尻を合わせなければなりません。
<AMEXCEL SESSION="ExcelSession2" WORKBOOK="%var_Folder%\%var_Template%" />
■4. ループの開始
■2. で作成したデータセットをもとにループを行います。
なお、1行目はヘッダー行なので、2行目からループを開始します。
<AMLOOP ACTIVITY="dataset" DATASET="ds_ALL" FROM="2" />
------------------------以下、ループ内部の処理------------------------
■5. 必要なデータの転記
雛形Excelについて、それぞれ必要なデータの書込みを行います。
顧客名 → C2セル（固定）
注文日 → B列のvar_RowNo行目(変動)
注文番号 → C列のvar_RowNo行目(変動)
注文金額 → D列のvar_RowNo行目(変動)
なお、ループ内部で何度も顧客名を書くことになりますが、結果は変わらないので「1回目のみ、C2セルに書く」のような制御はしなくても構いません。
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.B%" CELLREF="C2" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.E%" CELLREF="B%var_RowNo%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.C%" CELLREF="C%var_RowNo%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.D%" CELLREF="D%var_RowNo%" />
■6. 書込み位置を1行下げる
必要なデータの書込みを終えたら、次の行の書込み位置を1行下に下げます。
そのため、書込み行数を規定している変数「var_RowNo」の値を1増やします。
<AMVARIABLE ACTIVITY="increment" RESULTVARIABLE="var_RowNo" />
■7. 処理中の行がデータセット最終行なら、Excelを保存してループを終了
処理中の行がデータセットの最終行ならば、Excelを別名保存してループを終了(Break)する処理を入れます。
そうでなければ、次に構築する処理「次の行と現在の行のB列を比較」のロジックにおいて、IF文で実行エラーになります。
(存在しない行を参照すると、その時点で実行エラーとなります。)
なお、現在処理中の行が最終行であるか否かは、「データセット名.CurrentRow」と「データセット名.TotalRows」が等しいか否かで判断することが可能です。
<AMIF EXPRESSION="%ds_ALL.CurrentRow = ds_ALL.TotalRows%" USECOMPLEXUI="YES" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" SAVETYPE="save_as" DESTINATION="%var_Folder%\%ds_ALL.B%_注文書.xlsx" /> <AMBREAK /> <AMIF ACTIVITY="end" />
■8. 現在処理中の行のB列の値と次の行のB列の値を比較
「現在の行のB列(顧客)」と「次の行のB列(顧客)」が違うという図式が成り立てば、書込み先ファイルが変わるサインとなります。
その条件を満たした場合に実施する必要のある内容は以下となります。
・現在の雛形Excelを別名保存
・変数var_RowNo(Excelの書込み位置)を初期化
・もう一度雛形Excelを開く
<AMIF EXPRESSION="%ds_ALL.B <> ds_ALL(ds_ALL.CurrentRow + 1).B%" USECOMPLEXUI="YES" /> <!--ファイルを別名保存--> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" SAVETYPE="save_as" DESTINATION="%var_Folder%\%ds_ALL.B%_注文書.xlsx" /> <!--書込み位置の初期化--> <AMVARIABLE ACTIVITY="set" VARIABLENAME="var_RowNo" VALUE="5" /> <!--もう一度転記先のファイル（雛形）を開く--> <AMEXCEL SESSION="ExcelSession2" WORKBOOK="%var_Folder%\%var_Template%" /> <AMIF ACTIVITY="end" />
------------------------以上、ループ内部の処理------------------------
■9. ループの終了
■5. のループをここで終了させます。
<AMLOOP ACTIVITY="end" />
■10. 一覧Excelを閉じる
■2. で開いたExcelファイルを閉じます。内容の変更は無いので、保存せずに閉じます。
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />
今回定義する処理は以上になります。
(処理構築例)
<AMVARIABLE NAME="var_Folder">%GetDesktopDirectory()%\00010128</AMVARIABLE> <AMVARIABLE NAME="var_ALL_DATA" VALUE="ALL_DATA.xlsx" /> <AMVARIABLE NAME="var_Template" VALUE="雛形.xlsx" /> <AMVARIABLE NAME="var_RowNo" VALUE="5" TYPE="number" /> <!--転記元のブックを開き、データセット化--> <AMEXCEL SESSION="ExcelSession1" WORKBOOK="%var_Folder%\%var_ALL_DATA%" /> <AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" ACTION="rangebyreference" RESULTDATASET="ds_ALL" STARTCELLREF="A1" LASTCELL="YES" /> <!--転記先のブックをここで開く(閉じる処理および開く処理がループの中でワンセットになるため)--> <AMEXCEL SESSION="ExcelSession2" WORKBOOK="%var_Folder%\%var_Template%" /> <!--1行目はヘッダー行なので処理しない--> <AMLOOP ACTIVITY="dataset" DATASET="ds_ALL" FROM="2" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.B%" CELLREF="C2" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.E%" CELLREF="B%var_RowNo%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.C%" CELLREF="C%var_RowNo%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.D%" CELLREF="D%var_RowNo%" /> <AMVARIABLE ACTIVITY="increment" RESULTVARIABLE="var_RowNo" /> <!--最終行なら処理を終了 ここで制御しないと次の条件式がエラーになる--> <AMIF EXPRESSION="%ds_ALL.CurrentRow = ds_ALL.TotalRows%" USECOMPLEXUI="YES" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" SAVETYPE="save_as" DESTINATION="%var_Folder%\%ds_ALL.B%_注文書.xlsx" /> <AMBREAK /> <AMIF ACTIVITY="end" /> <!--現在処理中の行と次の行で「顧客」が同じかどうかを判断 同じなら継続、違うならファイルを別名保存--> <AMIF EXPRESSION="%ds_ALL.B <> ds_ALL(ds_ALL.CurrentRow + 1).B%" USECOMPLEXUI="YES" /> <!--ファイルを別名保存--> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" SAVETYPE="save_as" DESTINATION="%var_Folder%\%ds_ALL.B%_注文書.xlsx" /> <!--書込み位置の初期化--> <AMVARIABLE ACTIVITY="set" VARIABLENAME="var_RowNo" VALUE="5" /> <!--もう一度転記先のファイル（雛形）を開く--> <AMEXCEL SESSION="ExcelSession2" WORKBOOK="%var_Folder%\%var_Template%" /> <AMIF ACTIVITY="end" /> <AMLOOP ACTIVITY="end" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />
(別解1. Excelを開閉するタイミングを1ループにつき1回にしたもの)
<!--変数宣言--> <AMVARIABLE NAME="var_Folder">%GetDesktopDirectory()%\00010128</AMVARIABLE> <AMVARIABLE NAME="var_ALL_DATA" VALUE="ALL_DATA.xlsx" /> <AMVARIABLE NAME="var_Template" VALUE="雛形.xlsx" /> <AMVARIABLE NAME="var_RowNo" VALUE="5" TYPE="number" /> <AMVARIABLE NAME="var_Torihiki_List" DESCRIPTION="取引先一覧（カンマ区切り）" VALUE="" /> <AMVARIABLE NAME="var_Torihiki" DESCRIPTION="取引先（現在処理中）" VALUE="" /> <AMVARIABLE NAME="var_StartRow" VALUE="0" TYPE="number" /> <AMVARIABLE NAME="var_SheetName" VALUE="ALL_DATA" DESCRIPTION="Excelシート名" /> <!--もとになるExcelを開く--> <AMEXCEL SESSION="ExcelSession1" WORKBOOK="%var_Folder%\%var_ALL_DATA%" /> <!--後の「Match」関数と場所を合わせるため、ヘッダー行も含めてデータセット化--> <AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" ACTION="rangebyreference" RESULTDATASET="ds_ALL" STARTCELLREF="A1" LASTCELL="YES" WORKSHEET="%var_SheetName%" /> <!--出現する「顧客」をリストアップ--> <AMLOOP ACTIVITY="dataset" DATASET="ds_ALL" FROM="2" /> <!--カンマ区切りで顧客のリストを作成 リストに無い顧客が出現したらリストに追加--> <AMIF ACTIVITY="contains_text" TEXT="%var_Torihiki_List%" SUBSTRING="%ds_ALL.B%" ACTION="not_contain" /> <AMVARIABLE ACTIVITY="set" VARIABLENAME="var_Torihiki_List">%var_Torihiki_List & ds_ALL.B & ","%</AMVARIABLE> <AMIF ACTIVITY="end" /> <AMLOOP ACTIVITY="end" /> <!--最後のカンマを除去--> <AMTEXT ACTIVITY="trim" TEXT="%var_Torihiki_List%" RESULTVARIABLE="var_Torihiki_List" ACTION="remove_end" REMOVE="user" CHARACTERS="," /> <!--AutoMateの作業用シートを作成--> <AMEXCEL ACTIVITY="add_worksheet" SESSION="ExcelSession1" WORKSHEETNAME="データ検索用" LASTWORKSHEET="YES" /> <!--データ検索用式を埋め込み MATCH関数で、指定した顧客が最初に出現する行を特定できるようにし、検索効率を上げる--> <!--行数調整を回避するため、Match関数の範囲は1行目から指定--> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="=MATCH(A1,%var_SheetName%!B1:B65535,FALSE)" CELLREF="B1" WORKSHEET="データ検索用" /> <!--作成した顧客リストに基づきループ--> <AMLOOP ACTIVITY="list" LIST="%var_Torihiki_List%" RESULTVARIABLE="var_Torihiki" /> <!--書込み位置の初期化--> <AMVARIABLE ACTIVITY="set" VARIABLENAME="var_RowNo" VALUE="5" /> <!--転記先のファイルを開く--> <AMEXCEL SESSION="ExcelSession2" WORKBOOK="%var_Folder%\%var_Template%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.B%" CELLREF="C2" /> <!--転記元のブックでデータ検索--> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="%var_Torihiki%" CELLREF="A1" WORKSHEET="データ検索用" /> <AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" RESULTVARIABLE="var_StartRow" CELLREF="B1" WORKSHEET="データ検索用" /> <!--ヒットした行からループ開始--> <AMLOOP ACTIVITY="dataset" DATASET="ds_ALL" FROM="%var_StartRow%" /> <!--現在処理中の顧客とは別の顧客が出現したらループを抜ける--> <AMIF EXPRESSION="%ds_ALL.B% <> %var_Torihiki%" /> <AMBREAK /> <AMIF ACTIVITY="end" /> <!--対応するデータを書き込み--> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.E%" CELLREF="B%var_RowNo%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.C%" CELLREF="C%var_RowNo%" /> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%ds_ALL.D%" CELLREF="D%var_RowNo%" /> <!--書込み対象行を1段下げる--> <AMVARIABLE ACTIVITY="increment" RESULTVARIABLE="var_RowNo" /> <AMLOOP ACTIVITY="end" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" SAVETYPE="save_as" DESTINATION="%var_Folder%\%var_Torihiki%_注文書.xlsx" /> <AMLOOP ACTIVITY="end" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />
(別解2. 同じ顧客のデータが連続していない場合でも対応可能な方法 一時ファイルを使用)
<AMVARIABLE NAME="var_Folder">%GetDesktopDirectory()%\00010128</AMVARIABLE> <AMVARIABLE NAME="var_ALL_DATA" VALUE="ALL_DATA.xlsx" /> <AMVARIABLE NAME="var_Template" VALUE="雛形.xlsx" /> <AMVARIABLE NAME="var_RowNo" VALUE="5" TYPE="number" /> <AMVARIABLE NAME="var_FileName" /> <!--処理の混同を回避する為、テキストファイルを全て削除--> <AMFILESYSTEM ACTIVITY="delete" AM_ONERROR="CONTINUE" SOURCE="%var_Folder%\*.txt" /> <!--転記元のブックを開き、データセット化--> <AMEXCEL SESSION="ExcelSession1" WORKBOOK="%var_Folder%\%var_ALL_DATA%" /> <AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" ACTION="rangebyreference" RESULTDATASET="ds_ALL" STARTCELLREF="A1" LASTCELL="YES" /> <!--「顧客」のデータ別にテキストファイルをカンマ区切りで生成--> <AMLOOP ACTIVITY="dataset" DATASET="ds_ALL" FROM="2" /> <!--この時点で並び替えを済ませる--> <AMFILESYSTEM ACTIVITY="write_file" FILE="%var_Folder%\%ds_ALL.B%.txt">"%ds_ALL.E%","%ds_ALL.C%","%ds_ALL.D%"</AMFILESYSTEM> <AMLOOP ACTIVITY="end" /> <!--生成されたテキストファイルをもとにしてループ--> <AMLOOP ACTIVITY="folder" FOLDER="%var_Folder%\*.txt" RESULTVARIABLE="var_FileName" /> <!--テキストファイルの内容をデータセット化--> <AMFILESYSTEM ACTIVITY="csv_to_dataset" SOURCE="%var_FileName%" RESULTDATASET="ds_TXT" ROWASHEADER="NO" /> <!--ファイル名から顧客名を抽出 ファイル名だけにする → 「.txt」を除去すると顧客名になる--> <AMVARIABLE ACTIVITY="set" VARIABLENAME="var_FileName">%replace(ExtractFileName(var_FileName), ".txt", "")%</AMVARIABLE> <!--雛形ファイルを開く--> <AMEXCEL SESSION="ExcelSession2" WORKBOOK="%var_Folder%\%var_Template%" /> <!--顧客名を記入--> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%var_FileName%" CELLREF="C2" /> <!--テキストファイルから抽出したデータセットをそのまま転記--> <AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" SETTERTYPE="dataset" DATASET="ds_TXT" CELLREF="B%var_RowNo%" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" SAVETYPE="save_as" DESTINATION="%var_Folder%\%var_FileName%_注文書.xlsx" /> <AMLOOP ACTIVITY="end" /> <AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />
