Converts one or more files from .xlsb to .xlsx, accepts wildcards.

xlsb2xlsx.ps1

# xlsb2xlsx.ps1 - converts .xlsb files into .xlsx while removing macros
# - takes one argument from command line (accepts wildcards)
# - opens up each matching file in Excel
# - disables Autosave, strips down the macros
# - saves using the same name + .xlsx
# - closes Excel
# Error handling:
# - if parameter missing displays help
# - if file doesnt end in .xlsb skips it
# - if file doesnt exist displays info

param ($in)

$processed = 0
Write-Host [ xlsb2xlsx ] Starting...

# check if parameter passed
if ($null -ne $in){
    try {
        # check if any files match parameter
        $all = Get-ChildItem $in -ErrorAction Stop
        # process all found files
        foreach ($s_file in $all) 
        {
            # continue only foe .xlsb files
            if ($s_file -like "*.xlsb")
            {
                $f_input = $s_file.FullName
                $loc = (Get-Item $s_file).DirectoryName
                $f_name = (Get-Item $s_file.FullName).BaseName + ".xlsx"
                $f_output = Join-Path $loc $f_name
                # make sure file actually exists
                if (Test-Path $s_file -PathType Leaf)
                {
                    Write-Host [ $f_name ] Converting...
                    $xlApp = New-Object -Com Excel.Application      # creating object
                    $xlApp.Visible = $false                         # preventing window from being displayed
                    $xlApp.DisplayAlerts = $false                   # disabling alerts
                    $xlApp.Workbooks.Open($f_input) | Out-Null      # open the file    
                    # strip all macros from the file
                    foreach ($v in $xlApp.ActiveWorkbook.VBProject.VBComponents) 
                    { 
                        if ($v.Type -eq 1) 
                        { 
                            $xlApp.ActiveWorkbook.VBProject.VBComponents.Remove($v)  
                        } 
                    }     
                    $xlApp.ActiveWorkbook.SaveAs($f_output, 51) | Out-Null 
                    $xlApp.Quit()
                    Write-Host [ $f_name ] Done.
                    $processed = $processed + 1                     # increase number of processed files
                } 
                else 
                {
                    Write-Host [Skipping] $f_input does not exist
                }
            } 
            else 
            {
                Write-Host [($s_file.Name)] is NOT .xlsb
            }
        }
    }
    catch [System.Management.Automation.ItemNotFoundException]
    {
        Write-Host [Skipping] No files found
    }
    # show message about number of files processed at the end
    Write-Host [ xlsb2xlsx ] Finished: $processed file/s processed.
} 
# no parameters passed, display help
else 
{
    Write-Host [ xlsb2xlsx ] Error: Parameter missing / Usage: xlsb2xlsx [filename.xlsb]
}