今回はクラウド上のエクセルファイル内で使える、オフィススクリプト（旧VBA）とPower Automateの組み合わせで自動化処理を行います

やりたい事

OneDriveのフォルダに入れた「すべて」のエクセルファイルの「すべて」のシートを、事前に決められた内容に従って変換処理を行うことです

上の画像では、B2セルの①フォント文字を強調、②背景の色を黄色に、の２種類の変換が行われています

この①②の処理をすべてのファイル、すべてのシートで行います

ポイント

読者の皆様の中には、エクセルの記録マクロを使用した方が多くいらっしゃると思います

今回はエクセルマクロのクラウド版、オフィススクリプトで記録した内容を修正して活用します

修正した内容は、Power Automate/クラウド版RPAで動かします

Power AutomateでOneDriveのあるフォルダから全てのファイルの内容を取得し、エクセルファイルのみを選別して前述のオフィススクリプトを動かします

オフィススクリプト

記録操作

オフィススクリプトは「自動化」のタブから行います

自動化のタブをクリックすると「操作を記録」が行えるようになります

「操作を記録」を押した後に①フォント文字を強調、②背景の色を黄色、を行い「停止」を押します

「停止」を押すと、記録されたコードが「編集」から見れるようになります

コードで注目して頂きたいのは２つの点です

一つ目は「波括弧」です

{と}の間にB2セルを選択した処理が２つ書かれています

特に注目して頂きたいのが２点目です

こちらの「let」です

こちらはプログラミングの「変数」と同じ役割をします

「workbook.getActiveWorksheet()」すなわちファイル内の「アクティブ＝選択・処理しているシート/単数」を「selectedSheet」に当てはめています

ですので、次に続くコードはselectedSheetすなわち「選択・処理しているシート/単数」　の　「getRange(“B2”)=B2セルの内容」　の「getFormat=書式」 の　「getFont=フォント」 を　「setBold(true)＝太くする」になります

次はこのletで設定する変数と、変数を使用していたコードの内容を変更します

コードの変更

この時点では、letで設定する「selectedSheet」は選択されているシートのみが対象になります

こちらを次のように変更します

「workbook.getWorksheets()」とすることで、ファイル内の全てのシート（sがつく複数形）にします

そして、次に取得したシートの全てに前述の①②の処理が行えるように修正を行います

要は繰り返し処理を行うのですが、繰り返し処理の構文*は次の通りとなります

*こちらのページのコレクションの箇所になります

for(let 変数2 of 変数1){

・繰り返し処理する内容1

・繰り返し処理する内容2

}

こちらの内容は英語の「All of ~」と同じになります

All of ～とすることで～の内容の全てが対象になります

この仕組みと同じです

ですので、letで取得したSheetsの全ての各シート：Sheetを変数２として設定します

ここで、では「Sheets」で設定した内容と「Sheet」の内容とは何が違うのか？と疑問に思われる方もいらっしゃると思います

対象は同じですが、Sheetsの方は複数形で全体そのものを指しています

一方、Sheetの方はあくまで全体の構成要素の一つ一つを指します

では、コードの修正の最後として「selectedSheet」を「Sheet」に変更します

こちらは「Ctrl＋H」によって一気に変更することも可能です

コードの修正が完成したらコードの保存を行い、名称（allSheets）を付けます

Power Automate

Power Automateでは、フォルダ内にある全てのファイルを取得しつつ、各ファイルに対してオフィススクリプトを実行します

Power Automateではシナリオを実行するトリガー（起動条件*）をまず最初に選ぶのですが、今回は「インスタントクラウドフロー」すなわち手動でトリガーを選択します

*メールが届いた時、〇時に起動などを本来は選択できます

次にフロー名を付けて「手動でフローをトリガーします」を選択します

次の画面にて新しいステップを追加します

そして「OneDrive」を検索します

次にOneDrive関連のアクションから「フォルダー内のファイルリスト」を選択します

「フォルダー内のファイルのリスト」のアクションでは、該当のフォルダを設定します

この「フォルダー内のファイルのリスト」アクションにより、該当フォルダ内のファイルが一覧になります

ファイルの一覧は後述する「ID」という形で次のアクションに受け渡します

これで２つアクションが設定できました

次は「スクリプト（オフィススクリプト）の実行」アクションを設定します

スクリプトの実行アクションでは、前述のIDを指定します

上のGIF画像では、ファイル欄にIDを設定した途端に「Apply to each」というものが適用されています

これはeach/それぞれのファイルにスクリプトを実行するという意味になります

スクリプトは前述の「allSheets」を選択します

付け加えてスクリプトを実行するファイルは、拡張子が「.xlsx」に限定できるようにします

この限定作業には「コントロール」から「条件」を使用します

次に展開する画面で実際の条件を指定します

条件を設定したら、「スクリプトの実行」アクションは「はい」の下に移動します

これで「フォルダーから全てのファイルを取得」⇒「拡張子が.xlsxのファイルにてスクリプトを実行」のシナリオが完成しました

＜まとめ＞

今回はオフィススクリプトとPower Automateを組み合わせ、フォルダ内のエクセルファイルの全てのシートを変更する方法を解説しました

これは従来のVBAやRPAでは行えない処理です

クラウドの醍醐味がある処理だと思います

ぜひ試してみてください

にほんブログ村