Why I needed to do this
Alteryx wasn’t picking up all the cells from my xlsb files. Converting xlsb to xlsx fixed it, but I had 270 files. Instead of opening Excel 270 times, I automated it with PowerShell and saved a lot of time.
Step 1
Make a .ps1 script
I've left comments, starting with '#', to break the script down/
Open Notepad, copy and paste the script below. Save it in a desired folder and make sure to add a ".ps1" at the end of your filename.
# convert-xlsb-to-xlsx.ps1
# Temporarily allow script execution (for this session only)
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -Force
# Folders (your setup)
$SourceFolder = "G:\My Drive\03 Personal Projects\PowerShell\xlsb_files"
$DestinationFolder = "G:\My Drive\03 Personal Projects\PowerShell\xlsx_files"
# Create destination folder if it doesn't exist
If (!(Test-Path $DestinationFolder)) {
New-Item -ItemType Directory -Path $DestinationFolder | Out-Null
}
# Start Excel (must be installed)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
# Get all .xlsb files
$files = Get-ChildItem -Path $SourceFolder -Filter "*.xlsb" -File
if ($files.Count -eq 0) {
Write-Host "No .xlsb files found in $SourceFolder"
} else {
foreach ($f in $files) {
try {
$SourceFile = $f.FullName
$DestFile = Join-Path $DestinationFolder ($f.BaseName + ".xlsx")
Write-Host "Converting:`n $SourceFile`n -> $DestFile"
$Workbook = $Excel.Workbooks.Open($SourceFile)
$Workbook.SaveAs($DestFile, 51) # 51 = Excel .xlsx
$Workbook.Close($false)
}
catch {
Write-Host "Error converting $($f.Name): $($_.Exception.Message)"
}
}
}
# Quit Excel and release resources
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Write-Host "Conversion complete. Files saved in $DestinationFolder"
What you need to tailor for your script
$SourceFolder = "G:\My Drive\03 Personal Projects\PowerShell\xlsb_files"
$DestinationFolder = "G:\My Drive\03 Personal Projects\PowerShell\xlsx_files"
- Change the file path of the source folder of your xlsb files and the folder where you want to save your xlsx files
Step 2
Make a .bat script
Open Notepad, copy and paste the script below. Save it in a desired folder and make sure to add a ".bat" at the end of your filename instead this time.
powershell -ExecutionPolicy Bypass -File "G:\My Drive\03 Personal Projects\PowerShell\PowerShell_Script\convert-xlsb-to-xlsx.ps1"
What you need to tailor for your script
G:\My Drive\03 Personal Projects\PowerShell\PowerShell_Script\convert-xlsb-to-xlsx.ps1"
- Change the file path of to the .ps1 script you created in step 1
Run the .bat script
Double click your .bat file OR right click and open.
You will see PowerShell open, execute the .ps1 script, and close automatically.
Step 3
Enjoy!
