VBAモジュール(Sheet、Userform含む)を出力するPowerShellスクリプト

ExcelModulesExporter.cmd

rem Charset: Shift-JIS
rem usage: .\ExcelModulesExporter.ps1 <EXCEL FILEPATH ...>

PowerShell -ExecutionPolicy RemoteSigned .\ExcelModulesExporter.ps1 <EXCEL FILEPATH ...>
pause

ExcelModulesExporter.ps1

param([string]$fileName)  
  
$filePath = Join-Path $pwd $fileName  
  
$excel = New-Object -ComObject Excel.Application  
  
$excel.Workbooks.Open($filePath) | % {
  $_.VBProject.VBComponents | % {
    switch ($_.Type) {
      1 { # vbext_ct_StdModule
        $moduleType = "Modules"
        $extension = ".bas"
      }
      2 { # vbext_ct_ClassModule
        $moduleType = "Classes"
        $extension = ".cls"
      }
      3 { #vbext_ct_MSForm
        $moduleType = "Forms"
        $extension = ".frm"
      }
      100 { # vbext_ct_Document
        $moduleType = "Sheets"
        $extension = ".cls"
      }
      Default { # vbext_ct_ActiveXDesigner and Other
        $moduleType = "Others"
        $extension = ".cls"
      }
    }
    $exportFilePath = Join-Path ($filePath + "_src") $moduleType
    if (-not (Test-Path $exportFilePath)) {
      New-Item $exportFilePath -ItemType Directory
    }
    $exportFileNameFull = Join-Path $exportFilePath ($_.Name + $extension)
    $_.Export($exportFileNameFull)
    Write-Host $exportFileNameFull
  }  
}  
  
$excel.Quit()