動機

ExcelのPowerQueryは、データ整形や取得の自動化に欠かせない強力な機能なのですが、複数ブックにまたがるクエリの管理(例えば、クエリ定義そのものをGitで管理とか)や再利用をしようとして、今までは手作業でコピペでファイルにしてました。これがとにかくメンドクサイですし、ミスも起こりがち。そこで下記のPowerShellスクリプト「pqman.ps1」により、コマンド一発でエクスポート/インポートできます。

このスクリプトは、Excelブックに含まれるPowerQuery定義をファイルとしてエクスポートしたり、別のブックにインポートしたりする機能を備えています。バックアップ機能やエラー出力、一括処理にも対応してます。
クエリ管理の効率化と信頼性向上に貢献できるかなと思います。

まずはPowerShellスクリプト

pqman.ps1

<#
.SYNOPSIS
    ExcelブックのPowerQuery定義をエクスポート/インポートするスクリプト。
    - インポートする際、既存Excelをバックアップする機能。
    - 複数ブックの一括処理機能。
    - 失敗したクエリ名を標準エラー出力に出す機能。

    -Mode export:エクスポートモード
    例:
    pqman.ps1 -Mode export -ExcelPath "c:\input" -FolderPath "c:\PQ"

    [-ExcelPath]のブック群を読み、[-FolderPath]にPowerQuery定義群を出力します。

    例えば、[-ExcelPath "c:\input"]が次だったとします。

    c:\input
      +-- wb1.xlsx(PowerQueryの定義が「クエリ1」だけ存在している)
      +-- wb2.xlsx(PowerQueryの定義が「query1,query2」の2つ存在している)

    この場合、[-FolderPath "c:\PQ"]にPowerQuery定義を「ブック名\クエリ名.pq」の形式で出力します。

    c:\PQ
      +-- wb1
      |     +-- クエリ1.pq
      |
      +-- wb2
            +-- query1.pq
            +-- query2.pq

    -Mode import:インポートモード
    例:
    pqman.ps1 -Mode import -ExcelPath "c:\output" -FolderPath "c:\PQ"

    [-FolderPath]配下の「ブック名\クエリ名.pq」形式のPowerQuery定義群を読み、
    [-ExcelPath]のブック群に取り込みます。
    [-ExcelPath]配下のブック群はあらかじめ存在している必要があり
    且つ[-FolderPath]配下の「ブック名」フォルダの名称と一致している必要があります。

    次の例は、エクスポートしたときと違うブックにインポートする例です。
    
    例えば、[-FolderPath "c:\PQ"]が次だったとします。

    c:\PQ
      +-- b1
      |     +-- クエリ1.pq
      |
      +-- b2
            +-- query1.pq
            +-- query2.pq
    ※ブック名に該当するフォルダ名の所をわざと「b1、b2」にしています。
    ※こうすることで、インポート先にブック名「b1.xlsx、b2.xlsx」を容易しておけば、
    ※エクスポートしたときと違うブックにもインポートできます。

    c:\output
      +-- b1.xlsx(PowerQueryの定義「クエリ1」がインポートされる)
      +-- b2.xlsx(PowerQueryの定義「query1,query2」がインポートされる)

.PARAMETER Mode
    "export"または"import"を指定します。

.PARAMETER ExcelPath
    Excelファイル(.xlsx, .xlsm, .xlsb)または、フォルダのパス(フォルダの場合は直下の
    Excelファイル(.xlsx, .xlsm, .xlsb)を一括処理)します。

.PARAMETER FolderPath
    エクスポート先、または、インポート元のルートフォルダを指定します。
    - export:<FolderPath>/<ブック名>/*.pq に書き出し
    - import:<FolderPath>/<ブック名>/*.pq から読み込み

.PARAMETER Backup
    このオプションを指定すると、インポート時にバックアップファイルを作成します。
    バックアップファイルは<FolderPath>\Backup配下に保存します。

.EXAMPLE
    pqman.ps1 -Mode export -ExcelPath "c:\excel_files" -FolderPath "c:\pq"

.EXAMPLE
    pqman.ps1 -Mode imprt -ExcelPath "c:\excel_files" -FolderPath "c:\pq" -Backup
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [ValidateSet("export","import")]
    [string]$Mode,

    [Parameter(Mandatory=$true)]
    [string]$ExcelPath,

    [Parameter(Mandatory=$true)]
    [string]$FolderPath,

    [switch]$Backup
)

## エラーキャッチ用
$ErrorActionPreference = 'Stop'

## エクスポート用の出力ルートフォルダ作成
if ($Mode -eq "export" -and -not (Test-Path $FolderPath)) {
    New-Item -ItemType Directory -Path $FolderPath | Out-Null
}

## Excelファイル一覧取得(.xlsx, .xlsm, .xlsb)
$excelFiles = @()
if(Test-Path $ExcelPath -PathType Container) {
    $excelFiles = Get-ChildItem -Path $ExcelPath -File | Where-Object {
        $_.Extension -in ".xlsx",".xlsm",".xlsb"
    }
} elseif (Test-Path $ExcelPath -PathType Leaf) {
    $ext = [IO.Path]::GetExtension($ExcelPath).ToLower()
    if($ext -in @(".xlsx",".xlsm",".xlsb")){
        $excelFiles = , (Get-Item $ExcelPath)
    } else {
        Write-Error "ExcelPathは.xlsx,.xlsm,.xlsbのいずれかを指定してください。: $excelPath"
        exit 1
    }
} else {
    Write-Error "指定したパスが存在しません。: $ExcelPath"
    exit 1
}
Write-Host "Excelファイル数: $($excelFiles.Count)"

## Excel起動(COMオブジェクト利用)
$excel = $null
try {
    $excel = New-Object -ComObject Excel.Application
} catch {
    Write-Error "ExcelのCOMオブジェクトを作成できませんでした。Excelがインストールされているか確認してください。"
    exit 1
}
$excel.Visible = $false

foreach ($file in $excelFiles) {
    Write-Host "`n=== 処理中:$($file.FullName) ==="
    $workbook = $null
    try {
        $workbook = $excel.Workbooks.Open($file.FullName)
        if ($Mode -eq "export") {
            ## ブック毎の出力フォルダ
            $exportFolder = Join-Path $FolderPath $file.BaseName
            if (-not (Test-Path $exportFolder)) {
                New-Item -ItemType Directory -Path $exportFolder | Out-Null
            }
            ## クエリ毎にtry/catchして、失敗時はクエリ名のみを標準エラー出力
           foreach ($query in $workbook.Queries) {
                try {
                    $filePath = Join-Path $exportFolder ($query.Name + ".pq")
                    $query.Formula | Out-File -FilePath $filePath -Encoding UTF8 -ErrorAction Stop
                    Write-Host "Exported: $($query.Name) -> $filePath"
                } catch{
                    [Console]::Error.WriteLine($query.Name)
                    Write-Host "Exported failed: $($query.Name) - $($_.Exception.Message)"
                }
            }
        } elseif ($Mode -eq "import") {
            ## バックアップ
            if ($Backup) {
                try {
                    $backupFolder = Join-Path $FolderPath "Backup"
                    if (-not (Test-Path $backupFolder)) {
                        New-Item -ItemType Directory -Path $backupFolder | Out-Null
                    }
                    ## TODO:オリジナルの拡張子を活かす必要がある
                    $backupFile = Join-Path $backupFolder ($file.BaseName + "_" + (Get-Date).ToString("yyyyMMdd_HHmmss") + ".xlsx")
                    Copy-Item -Path $file.FullName -Destination $backupFile -ErrorAction Stop
                    Write-Host "Backup created: $backupFile"
                } catch {
                    Write-Host "Backup failed: $($file.Name) - $($_.Exception.Message)"
                    exit 1
                }
            }
            ## インポート元フォルダ
            $importFolder = Join-Path $FolderPath $file.BaseName
            if (-not (Test-Path $importFolder)) {
                Write-Host "インポート先ブックに対応するインポート元フォルダ・ファイルが見つかりません。: $importFolder"
            } else {
                ## 各pqファイル毎にtry/catchし、失敗時は名前を標準エラー出力
                Get-ChildItem -Path $importFolder -Filter *.pq -File | ForEach-Object {
                    $queryName = $_.BaseName
                    try {
                        $formula = Get-Content $_.FullName -Raw -ErrorAction Stop

                        ## 既存クエリ削除(存在しない場合は無視)
                        try {
                            $existing = $workbook.Queries.Item($queryName)
                            if ($existing) {
                                Write-Host "インポート先ブックに同名のクエリが存在していたので上書きします。: $queryName"
                                $existing.Delete()
                            }
                        } catch {
                            ## 存在しない場合は、そのままインポートします
                        }
                        ## クエリを追加。単純にAddすると、クエリそのものを標準出力するのが邪魔なのでOut-Nullにする
                        ## pqファイルが破損していても、破損したなりにインポートしてしまいます。
                        $workbook.Queries.Add($queryName, $formula) | Out-Null
                        Write-Host "Imported: $queryName"
                    } catch {
                        [Console]::Error.WriteLine($queryName)
                        Write-Host "Import failed: $queryName - $($_.Exception.Message)"
                    }
                }
                ## 少なくとも1件処理した場合はブックを保存(保存失敗はthrow)
                try {
                    $workbook.Save()
                } catch {
                    Write-Host "Save failed: $($file.Name) - $($_.Exception.Message)"
                }

            }
        }
    } catch {
        ## ブックオープン等で失敗
        Write-Host "Workbook-level failed: $($file.Name) - $($_.Exception.Message)"
    } finally {
        if ($workbook -ne $null) {
            try {
                $workbook.Close($false)
            } catch {
                ## ここでの失敗はどうしようもない
            }
        }
    }
}

## 終了処理
try {
    $excel.Quit()
} catch {
    ## ここでの失敗はどうしようもない
}
try {
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
} catch {
    ## ここでの失敗はどうしようもない
}

Write-Host "`n=== 全処理終了 ==="

スクリプトの概要と目的

「pqman.ps1」は、PowerQuery定義をファイルとしてエクスポート・インポートするPowerShellスクリプトです。主な機能は以下の通りです：

Excelブックからクエリ定義を抽出し、フォルダ構造に保存（exportモード）
クエリ定義ファイルを既存のExcelブックに取り込み（importモード）
インポート時のバックアップ機能（-Backupスイッチ）
複数ブックの一括処理
クエリ単位でのエラー出力（標準エラー）

エクスポート処理の詳細

exportモードでは、指定されたExcelファイル群からPowerQuery定義を抽出し、以下の形式で保存します。

<FolderPath>\<ブック名>\<クエリ名>.pq

例えば、wb1.xlsxに「クエリ1」、wb2.xlsxに「query1」「query2」が含まれている場合、出力は以下のようになります。

c:\PQ
  +-- wb1
  |     +-- クエリ1.pq
  |
  +-- wb2
        +-- query1.pq
        +-- query2.pq

各クエリは個別にtry/catchして、失敗した場合はクエリ名を標準エラー出力します。

インポート処理の詳細

importモードでは、指定されたフォルダ構造からクエリ定義を読み込み、対応するExcelブックに追加します。インポート先のブック名とフォルダ名が一致している必要があります。

また、-Backupスイッチを指定すると、インポート前にバックアップファイルが作成されます。

<FolderPath>\Backup\<ブック名>_yyyyMMdd_HHmmss.xlsx

既存クエリが存在する場合は削除してから追加されます。処理結果は標準出力に、失敗したクエリ名は標準エラー出力します。

TODO:バックアップファイルの拡張子が".xlsx"固定なのを何とかしたい。。。

実行例とユースケース

一括エクスポート

pqman.ps1 -Mode export -ExcelPath "c:\input" -FolderPath "c:\PQ"

異なるブックへのインポート

pqman.ps1 -Mode import -ExcelPath "c:\output" -FolderPath "c:\PQ" -Backup

このように、クエリ定義をテンプレート化して別ブックに展開することで、業務の標準化や再利用が容易になります。Gitでバージョン管理もやりやすくなります。

参考にしたページ

感謝です。