MENU
ExcelVBAを中心にお役に立てる情報を発信しています
  1. ホーム
  2. Excel関数その他
  3. Officeスクリプト
  4. 【Office Scripts移行】別ブックを開いてデータを取得する本編｜PAD×PythonでWorkbooks.Openの壁を越える

【Office Scripts移行】別ブックを開いてデータを取得する本編｜PAD×PythonでWorkbooks.Openの壁を越える

記事内には広告が含まれます
Officeスクリプト
Office ScriptsでWorkbooks.Openの代替方法をPADとPythonの連携で解説
目次

はじめに

【Excel VBA】別ブックからデータを抜き出す処理（その１）でマーカー検索によりセル番地を記録し、（その２）ではその番地を使って複数の別ブックを開き、データを抜き出して集計します。第8回で「その１」をOffice Scriptsに移行済みです。

今回は「その２」の本編、別ブックを実際に開いてデータを取得する処理の移行です（対象ファイルはローカルPC・社内共有フォルダ上にある想定）。

Office Scriptsはクラウド実行のため、VBAのWorkbooks.Openのように任意のパスを指定してブックを開く機能がそもそも存在しません。結論から言うと、この壁はOffice Scriptsだけでは越えられず、PADとPythonの力を借りることで解決しました。

元になるVBAコードのおさらい

「その２」のVBAコードは、フォルダ内のファイルを1つずつ開き、第8回で記録済みのセル番地から値を取得して集計シートに書き出します（全文は元記事参照。ここでは核心部分のみ抜粋します）。

Sub TargeCellsPullOut()
    ' ... 設定読み取り・フォルダ存在チェックなどは省略 ...
    n = Application.CountIf(Cells, "*【*】")
    arr() = Setting(n)
    colF = Range(arr(2)).Column: rowF = Range(arr(2)).Row
    colT = Range(arr(3)).Column + 1

    Do Until i > tgFCount
        tgFName = .Cells(rowF + i, colF)
        tgFPN = arr(1) & "\" & tgFName
        Set tgWb = Workbooks.Open(fileName:=tgFPN, ReadOnly:=True)   ' 対象ブックを開く
        ReDim db(tgCellCount) As Variant
        db(0) = tgFName & "_" & arr(6)
        For h = 1 To tgCellCount
            tgAd = .Cells(h, colT).Formula
            db(h) = Worksheets(arr(6)).Range(tgAd).Value             ' セル値を取得
        Next
        tRow = tRow + 1
        gatSh.Range(gatSh.Cells(tRow, 1), gatSh.Cells(tRow, tgCellCount + 1)).Value = db
        Workbooks(tgFName).Close savechanges:=False                  ' 保存せず閉じる
        i = i + 1
    Loop
End Sub

ポイントは3つです。
・Workbooks.Open(fileName:=tgFPN, ReadOnly:=True)：フルパスを指定して対象ブックをリードオンリーで開く
・ 第8回の記事で記録したセル番地（tgAd）を使い、対象ブックの値を配列（db）に取得
・Workbooks(tgFName).Close savechanges:=False：処理後にブックを保存せず閉じる

この「パスを指定してブックを開閉する」という部分が、Office Scriptsではそのまま移植できません。

技術的な壁：Office Scriptsは他ブックを開けない

ExcelScript.Workbookは「今スクリプトが実行されているそのブック」しか表せず、Workbooks.Openに相当するメソッドは存在しません。対象ファイルはローカル・社内共有フォルダ上にあり、そもそもExcel on the webから見えない場所にもあります。

当初は「PADでOneDriveへ一時コピー→Run scriptで抽出」という構成を検討しましたが、フォルダ削除のエラーや、PADとOffice Scriptsを橋渡しするクラウドフローの複雑さといった壁にぶつかりました。そこで方針転換し、対象ファイルの読み取りにはPythonでExcelファイルを直接読む方式を採用しました。Workbooks.Open + .Range(address).Valueがやっていたことは「ファイルを開いてセルを読む」だけの処理で、Excelアプリである必然性はありません。PythonのopenpyxlならExcelもOffice Scriptsも経由せず.xlsxを直接読み書きでき、OneDriveコピー・同期待ち・削除の問題がまるごと不要になります。

全体構成（実機検証済み）

VBAでは1つのマクロが「開く→読む→書く→閉じる」を一気に行っていましたが、今回は役割ごとに分担します。Office Scriptsはマーカー検索（事前準備、1回のみ）、PADはフォルダ・ファイル一覧の取得とMainブックへの書き込み（キーの送信）、Pythonは設定・対象ファイルの読み取りをそれぞれ担当します。

Mainシートの設定レイアウト

VBA版「その２」のSetting()関数は、シート上の"N【...】"という文字列をFindメソッドで検索し、右隣のセルを設定値として読み取る汎用的な仕組みです。実際のサンプルファイルでは以下の6項目が設定されています。

ラベルセル値の例・用途
1【フォルダPATH】A1／値B1対象ファイルのフォルダ（※）
2【フォルダ内ファイル名】A2／値B2以降処理対象ファイル名の一覧（※）
3【Targetの設定用符号】C1／値D1マーカー記号（例：★）
4【Target設定用シート名】C2／値D2マーカーを検索したシート名
5【データ集計用シート名】C3／値D3抜き出した値を書き込むシート名
6【その他の設定】C4／値D4対象ブック側で値を読み取るシート名

（※）VBA原文ではシートから読み取りますが、今回はPADが都度取得し「キーの送信」でこのセルに書き込みます（読み取りではなく書き込み先）。

E列・F列には、第8回相当の処理で生成済みの「セル番地（E列）とマーカー番号（F列）」の対応表が並びます（E1=A1, F1=★1…のように、3【Targetの設定用符号】セルの1列右＝E列に対象セル番地が格納されます）。

事前準備：対象ファイルのレイアウトを設定する（手動・実機検証済み）

対象ファイルの読み取りを自動化する前に、「どのセルから何を取り出すか」を人手で設定しておきます。対象ファイルのレイアウトが決まったとき（様式が変わらない限り基本1回）だけ行う準備作業で、PADフローには含めません。

  1. 「取得セル設定」シートに、対象ファイルのシート構成のコピーを手動で貼り付ける
  2. 抜き出したいセルの位置に、マーカー記号（★1、★2…）を手動で入力する
  3. 「セル番地取得」ボタン（Office Scripts）を押して実行し、E:F列にマーカー→セル番地の対応表を生成する
  4. E:F列の内容が意図した通りになっているか、目視で確認する（★の数と行数が一致しているか、「見つかりません」が出ていないか）

PADフローは以降E:F列の値をそのまま信用するため、4番の確認が重要です。

function main(workbook: ExcelScript.Workbook) {
  const mainSheet = workbook.getWorksheet("Main");
  const usedRange = mainSheet.getUsedRange();
  const values = usedRange.getValues();

  // VBAの Cells.Find(What:=i & "【*】") に相当するラベル検索（自前スキャン）
  function findLabelCell(labelPrefix: string): { row: number; col: number } {
    for (let r = 0; r < values.length; r++) {
      for (let c = 0; c < values[r].length; c++) {
        const cell = values[r][c];
        if (typeof cell === "string" && cell.indexOf(labelPrefix) === 0) {
          return { row: r, col: c };
        }
      }
    }
    throw new Error(`設定ラベル「${labelPrefix}」が見つかりません`);
  }

  function cellValue(row: number, col: number): string {
    const v = values[row][col];
    return v === null || v === undefined ? "" : String(v);
  }

  // 3【Targetの設定用符号】：マーカー記号と、Target番地を書き込む列（値セルの1列右）
  const markPos = findLabelCell("3【");
  const mark = cellValue(markPos.row, markPos.col + 1);
  const addressCol = markPos.col + 2; // 値セル(D)のさらに1列右＝E列
  const markerCol = addressCol + 1;   // その1列右＝F列

  // 4【Target設定用シート名】：マーカーを検索する対象シート
  const settingSheetPos = findLabelCell("4【");
  const settingSheetName = cellValue(settingSheetPos.row, settingSheetPos.col + 1);
  const settingSheet = workbook.getWorksheet(settingSheetName);

  // Target番地保存エリア（E:F列）をクリア
  mainSheet.getRangeByIndexes(0, addressCol, values.length, 2).clear(ExcelScript.ClearApplyTo.contents);

  const settingUsedRange = settingSheet.getUsedRange();
  const settingValues = settingUsedRange.getValues();

  // マーカー文字列 → セル番地 のマップを、値配列を自前でスキャンして作る
  // ※ この値はPythonのopenpyxlでそのまま使うため、VBAの.Addressに合わせた
  //   絶対参照形式（$A$1）への変換はせず、getAddressLocal()の素の値（A1形式）を使う
  const addressMap = new Map<string, string>();
  for (let r = 0; r < settingValues.length; r++) {
    for (let c = 0; c < settingValues[r].length; c++) {
      const cellVal = settingValues[r][c];
      if (typeof cellVal === "string" && cellVal.indexOf(mark) === 0) {
        const fullAddress: string = settingUsedRange.getCell(r, c).getAddressLocal();
        const address: string = fullAddress.includes("!") ? fullAddress.split("!")[1] : fullAddress;
        addressMap.set(cellVal, address);
      }
    }
  }

  const m = addressMap.size;
  for (let i = 1; i <= m; i++) {
    const target = `${mark}${i}`;
    const address: string = addressMap.has(target) ? addressMap.get(target) : "見つかりません";
    // E列：セル番地／F列：マーカー番号
    mainSheet.getRangeByIndexes(i - 1, addressCol, 1, 1).setValue(address);
    mainSheet.getRangeByIndexes(i - 1, markerCol, 1, 1).setValue(target);
  }
}

第8回本体（B2/F2/C:D版）に加え、上記のラベル形式・E:F版、絶対参照を削除したバージョンも実機で動作確認済みです。この事前準備が完了している（E:F列が正しく埋まっている）ことを前提に、以降のPADフローが動きます。

PADフロー（実機検証済み）

事前準備は完了している前提で、ここからはファイルを処理するたびに毎回自動実行される部分です。どのアクションでどの変数が作られるかを明記します。

0. 固定変数の準備（フローの先頭・1回だけ）

  1. 「変数を作成する」でMainFilePathを作成し、Mainブックのローカル同期コピーのフルパスを値に入力する（例：C:\Users\ユーザー名\OneDrive\Main.xlsx）

注意： 値欄に前後のダブルクォーテーション"を入力しないでください（"C:\...\Main.xlsx"は誤り、C:\...\Main.xlsxが正しい）。PADは"も含めて文字列として格納するため、Pythonからは存在しないファイルパスに見え、エラーも出ないまま出力だけが空になります。

1. フォルダ・ファイル一覧の取得と書き込み（実機検証済み）

  1. 「フォルダーの選択ダイアログを表示」でフォルダを選択しSelectedFolderに格納
  2. 「フォルダー内のファイルを取得」でFilesを取得
  3. For eachでファイル名部分だけを抜き出し、FileNamesリストへ追加
  4. ブラウザでMainブック（Excel Online）を開き、「キーの送信」で1【フォルダPATH】・2【フォルダ内ファイル名】のセルへSelectedFolder・FileNamesを書き込む

2. メインブックの設定読み取り（Python）

集計シート名（5【データ集計用シート名】）はドキュメントとして残していますが、集計シートのタブクリックはPAD上で特定のタブに記録するため、実質的にシート名は「集計」固定で、このPADフローでは動的には使いません。

PAD変数の埋め込みはr'%変数名%'（raw文字列＋シングルクォート）で統一。 ダブルクォートでは値が渡らず、Windowsパスの\Usersを\Uと誤認識するリスクもあります。read_only=Trueもセルのピンポイント読み取りと相性が悪いため使いません。ラベル検索はC列を1〜100行目まで直接走査する方式にしています（汎用性より確実さを優先）。

2-1. 対象シート名の取得

  1. 「Pythonスクリプトの実行」を配置し、以下のコードを入力する
import openpyxl

file_path = r'%MainFilePath%'.strip('"').strip("'")

# read_only=True は使わない（セルのピンポイント読み取りができないため）
wb = openpyxl.load_workbook(file_path, data_only=True)
main_sheet = wb["Main"]

target_sheet_name = ""
for r in range(1, 101):
    val = main_sheet.cell(row=r, column=3).value  # C列
    if val is not None and str(val).strip().startswith("6【"):
        target_sheet_name = main_sheet.cell(row=r, column=4).value  # 右隣のD列
        break

wb.close()

# 末尾の改行が入らないよう end='' を指定
print("" if target_sheet_name is None else str(target_sheet_name).strip(), end='')

.strip('"').strip("'")はダブルクォーテーション混入への保険です。実行後、生成変数欄で既定のPythonScriptOutputを直接TargetSheetNameに書き換えます。

2-2. セル番地一覧の取得

  1. もう1つ「Pythonスクリプトの実行」を配置し、以下のコードを入力する
import openpyxl

file_path = r'%MainFilePath%'.strip('"').strip("'")

wb = openpyxl.load_workbook(file_path, data_only=True)
main_sheet = wb["Main"]

addresses = []
for r in range(1, 101):
    val = main_sheet.cell(row=r, column=3).value  # C列
    if val is not None and str(val).strip().startswith("3【"):
        # 「3【」が見つかった行から、E列（5列目）を上から順に読む
        for target_r in range(r, 101):
            addr_val = main_sheet.cell(row=target_r, column=5).value  # E列
            if addr_val in (None, ""):
                break
            addresses.append(str(addr_val).strip())
        break

wb.close()
print(",".join(addresses), end='')

生成変数をAddressesJoinedに書き換えます。PADの「Pythonスクリプトの実行」は標準出力（print()の内容）をまるごと1つの変数に格納するだけの仕組みで、汎用の「JSON の解析」に相当するアクションが見当たらなかったため、複数値はカンマ区切りのテキストで受け渡しています（値にカンマが含まれると区切り位置がズレる点は注意）。

2-1・2-2はいずれもループの外（1回だけ）に配置しているため、対象ファイルが多くても設定ファイル（Main）を読み込むのは1回きりです。

3. 対象ファイルごとの処理（Python、ループ）

FileNamesではなくFiles（「フォルダー内のファイルを取得」の結果）をループします。FileNamesはファイル名の文字列だけのリストですが、Pythonでファイルを開くにはフルパスが必要なため、フルパス情報を持つFilesの方を使います（OneDriveへのコピーは不要。対象ファイルは元のフォルダに置いたまま読み取ります）。

ループに入る前（1回だけ）：

  1. UI要素クリックで集計シートのタブへ切り替える
  2. キーの送信 {Control}({End}) で使用済み範囲の最後のセルへ移動
  3. キーの送信 {Home} でその行のA列へ移動
  4. キーの送信 {Control}({Up}) でA列の使用済み行の最後のセルへ移動
  5. キーの送信 {Down} で次のA列空き行（最初の書き込み位置）へ移動

{Control}({Down})単独だと、集計シートに見出し行しかない（データ0件）場合にシート最下行まで飛んでしまうため、{Control}({End})起点の4手順にしています。集計シートの1行目には必ず見出しを入れておいてください。

ループの中（ファイルごと）：

  1. For eachループの変数CurrentItem（Filesの1件＝対象ファイルのフルパス）と、「ファイル パス部分を取得」でCurrentItemから抜き出したファイル名（CurrentFileName）を使う準備をする
  2. 「Pythonスクリプトの実行」で対象ファイルを直接読み込み、セル番地一覧の値を取得する
import openpyxl
from openpyxl.utils import coordinate_to_tuple

file_path = r'%CurrentItem%'.strip('"').strip("'")
sheet_name = r'%TargetSheetName%'

wb = openpyxl.load_workbook(file_path, data_only=True)
sheet = wb[sheet_name]

# 手順7（2-2）でカンマ区切りにしたセル番地一覧を、Python側で再度リストに戻す
addresses = r'%AddressesJoined%'.split(",")

values = []
for address in addresses:
    if not address.strip():
        continue
    try:
        # 'B1' などの形式を (行番号, 列番号) に変換
        row_idx, col_idx = coordinate_to_tuple(address)
        val = sheet.cell(row=row_idx, column=col_idx).value
        values.append("" if val is None else str(val).strip())
    except Exception:
        values.append("")

wb.close()
print("|".join(values), end='')

coordinate_to_tupleでA1形式を行番号・列番号に変換してからsheet.cell()で取得しています（sheet[address].valueの直接指定はエラーの原因になったため不採用）。生成変数をValuesJoinedに書き換えます。

  1. PADの「テキストを分割する」アクションで、ValuesJoinedを区切り文字|で分割し、Valuesリストを得る
  2. Valuesを「テキストのリストの結合」アクションで区切り文字{Tab}に結合し、ValuesTextを作る
  3. キーの送信で %CurrentFileName%{Tab}%ValuesText%{Enter} のように、ファイル名と取得した値をまとめて入力する

Excelは「Tab移動しながら入力→Enterで、Tab開始列の1つ下の行に戻る」仕様があるため、2回目以降のループは17だけを繰り返せば自動的に次の行のA列から入力されます。

4. 完了

  1. 全ファイル処理後、完了メッセージを表示

実際に動かして検証する

フォルダ選択〜ファイル一覧取得〜キー送信でのMain書き込み、マーカー検索（絶対参照削除版含む）、3本のPythonスクリプト、集計シートへの追記、ループ全体を通した動作まですべて実機で動作確認済みです。

今後の課題： 集計シートへの追記（キーの送信）は状況によって不安定になることがあり、クリップボード経由での書き込みなど代替案を模索中です。最終的にはPADフロー自体をMainブックのシート上から起動できる形を目指します。

まとめ

  • VBAのWorkbooks.Openはサーバーサイド実行のOffice Scriptsには存在せず、任意パスのブックを直接開くことはできない
  • 対象ファイルの読み取りは、Excelアプリを介さずファイルを直接読み書きできるPython（openpyxl）をPADの「Pythonスクリプトの実行」から呼び出す方式を採用し、OneDriveコピー・クラウドフロー・Excelアプリのインストールがいずれも不要になった
  • Office Scriptsの役割は「マーカー検索でのセル番地記録（第8回相当）」に絞られる。ファイル処理のたびに実行するものではなく、対象ファイルのレイアウトが決まったときに行う事前準備として、PADフローとは切り離した
  • メインブックへの書き込みは、PADの「キーの送信」でブラウザのExcel Onlineへ直接入力する方式を採用した
  • read_only=Trueは使わない、PADの「変数を作成する」の値をダブルクォーテーションで囲まない、という2点が実機で長時間ハマった原因だった

次回予告

集計シートへの追記（「キーの送信」の安定性向上。クリップボード経由の書き込みなどを検証中）が固まり次第、その内容と、PADフロー自体をMainブックのシート上から起動できるようにする仕上げの回を予定しています。

関連記事

当サイトの記事で使用したVBAなどのサンプルをDLできます

この記事のサンプルはありません！

ダウンロードページへは下のカードをクリックすればジャンプできます。
よろしければご利用ください！

サンプルのダウンロードはこちらから
【Excel VBA】サンプルファイル_ダウンロードのページ ・最新の登録データが一番上になっています。・目次クリックでジャンプできますのでご利用ください。 ダウンロード一覧（1/7ページ目）新しい登録順です 記事ごとに使っ…
Officeスクリプト
OfficeScripts Officeスクリプト openpyxl PAD PowerAutomateDesktop Python TypeScript 別ブック
よかったらシェアしてね！
  • URLをコピーしました！
目次