# powershell script: 06-27-16
#
# 1) gets list of all xlsb files in directory
# 2) goes through each and saves worksheets as separate csv
#
#If you want to search through subdirectories also, add " -Recurse" before "| Foreach-Object"
$scriptpath = "B:"
#path to search
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV
#needed to lookup
$excel = new-object -ComObject "Excel.Application";
$excel.DisplayAlerts=$false;
$excel.Visible =$false;
$csvloc = "b:csv"
#output directory
Get-ChildItem -LiteralPath b: -Filter *.xlsb | ForEach-Object {
$wb = $excel.Workbooks.Open($_.FullName)
$lastmod = $_.LastWriteTime.ToString('yyyy-MM-ddThh-mmss')
$wbn = $wb.name
foreach($ws in $wb.Worksheets) {
$n = $lastmod + "_" + $wbn + "_" + $ws.Name
$ws.SaveAs($csvLoc + $n + ".csv", $xlFixedFormat);
}
$wb.close($False)
#End file-specific code
}
$excel.Quit();
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel);
Related
