PowerShellでフォルダにあるExcelのシート名を出力するスクリプト

• 環境
  • Windows10 Pro 64bit バージョン1803
  • PowerShell 5.1.17134.858
  • Microsoft Excel 2016 32bit

仕様

• 指定したフォルダ(サブフォルダ含む)にあるExcel(.xlsxと.xlsm)のブック名とシート名をカンマ区切りでコンソールに出力する。
• ファイルに出力する
  • リダイレクトする。
• 任意の文字列が含まれるシート名のみ出力する。
  • スクリプト実行時の引数に任意の文字列を指定する。
  • 全角半角は区別される。

使い方

何もかも自己責任で使ってください。

1. スクリプトのコードをコピペしてスクリプトファイル(.ps1)を任意の場所に作る
2. Win + X > [PowerShell]でPowerShellのコンソールを起動する
   2. PowerShellが起動すれば、起動方法はなんでもOK
3. Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope Process を実行して、一時的にスクリプトファイルを実行できるようにする
4. C:\{スクリプトファイルがあるフォルダパス}\{スクリプトファイル}.ps1 {Excelのあるフォルダパス} を実行する
   • ファイルに出力したい場合 : リダイレクトする
     • C:\{スクリプトファイルがあるフォルダパス}\{スクリプトファイル}.ps1 {Excelのあるフォルダパス} > C:\path\hoge.csv
   • 任意の文字列が含まれるシート名のみ出力したい場合
     • C:\{スクリプトファイルがあるフォルダパス}\{スクリプトファイル}.ps1 {Excelのあるフォルダパス} {任意の文字列}

例

こんなフォルダにあるExcelのシート名を出力する

taget
│  fuga.xlsx
│  osusume_services.ico
│  マクロ.xlsm
│  定義.ico
│
└─folder
    hoge.xlsx
    文書.docx

# 実行ポリシーを設定する
PS C:> Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope Process

実行ポリシーの変更
実行ポリシーは、信頼されていないスクリプトからの保護に役立ちます。実行ポリシーを変更すると、about_Execution_Policies
のヘルプ トピック (https://go.microsoft.com/fwlink/?LinkID=135170)
で説明されているセキュリティ上の危険にさらされる可能性があります。実行ポリシーを変更しますか?
[Y] はい(Y)  [A] すべて続行(A)  [N] いいえ(N)  [L] すべて無視(L)  [S] 中断(S)  [?] ヘルプ (既定値は "N"): y

# (基本)フォルダを指定して実行する
PS C:> C:\path\to\script-file.ps1 C:\path-to\target\
hoge.xlsx,シート名 (2)
hoge.xlsx,定義
hoge.xlsx,定義 (2)
hoge.xlsx,teigi
hoge.xlsx,シート名
hoge.xlsx,Sheet1
hoge.xlsx,Sheet2
hoge.xlsx,Sheet3
hoge.xlsx,Sheet4
fuga.xlsx,全角２
fuga.xlsx,②
fuga.xlsx,Sheet2 (2)
fuga.xlsx,半角2
fuga.xlsx,シート名
fuga.xlsx,Sheet1
fuga.xlsx,点.
fuga.xlsx,Sheet3
fuga.xlsx,Sheet4
マクロ.xlsm,表紙
マクロ.xlsm,更新履歴
マクロ.xlsm,目次
マクロ.xlsm,メニュー
マクロ.xlsm,定義
マクロ.xlsm,マスタ(画面)
マクロ.xlsm,間に定義があるyo
マクロ.xlsm,マスタ(セッション)
マクロ.xlsm,含む文字は定義
マクロ.xlsm,マスタ(データ項目)
マクロ.xlsm,定と義

# ファイルに出力する
PS C:> C:\path\to\script-file.ps1 C:\path-to\target\ > C:\path\to\list.csv

# 任意の文字列が含まれるシート名のみ出力する
PS C:> C:\path\to\script-file.ps1 C:\path-to\target\ 定義
hoge.xlsx,定義
hoge.xlsx,定義 (2)
マクロ.xlsm,定義
マクロ.xlsm,間に定義があるyo
マクロ.xlsm,含む文字は定義

PS C:>

使い道

大量にシートがある大量のExcelファイルからシート名を抜かねばならない時に。
1ファイルずつ開いてイミディエイトウィンドウでFor Each i In ThisWorkbook.Sheets: debug.print ThisWorkbook.Name","i.name : next iを実行して結果をファイルにコピペしていたら腱鞘炎ぽくなったので。

スクリプト

param(
    [string]$folderPath,
    $include
)

function check-param() {
    if([string]::IsNullOrEmpty($folderPath)) {
        write-host '対象のフォルダを-folderPathの後に指定してください。'
        return $false
    }
    if(!(Test-Path $folderPath)) {
        write-host '-folderPathに指定されたフォルダは存在しません。'
        return $false
    }
    return $true
}

function get-sheet-name($sheet) {
    $name = ''
    if ($sheet -ne $null) {
        if (!([string]::IsNullOrEmpty([string]$include))) {
            # シート名に含む文字が指定されている場合
            $likeWord = '*' + $include + '*'
            if ($sheet.Name -like $likeWord) {
                # 任意の文字列を含むシート名のみ対象にする
                $name = $sheet.Name
            }
        } else {
            # シート名に含む文字が指定されていない場合:すべてのシート名を対象にする
            $name = $sheet.Name
        }
    }
    return $name
}

function output-sheet-list() {
    $fileList = Get-ChildItem $folderPath -Include *.xlsx,*.xlsm -Recurse
    if ($fileList -ne $null) {
        # ExcelのCOMオブジェクトを作成する
        $excel = New-Object -ComObject Excel.Application
        # Excelを可視化しない
        $excel.Visible = $false

        foreach ($file in $fileList) {
            # ブックを開く
            $book = $excel.Workbooks.Open($file.FullName)
            foreach ($sheet in $book.Sheets) {
                $sheetName = get-sheet-name($sheet)
                if ($sheetName -ne '') {
                    # リダイレクトできるようにwrite-hostではなくechoで出力する
                    echo ($file.Name+','+$sheet.Name)
                }
            }
            # ブックを閉じる
            $excel.Quit()
        }
        # プロセスを開放する
        $excel = $null
        [GC]::Collect()
    }
}

if(check-param) {
    output-sheet-list
}

おわりに

はじめてPowerShellを書いてみました。
改善点がありましたらご指摘ください。