Batch Conversion of XLS to XLSX using Excel and PowerShell

I recently needed to convert a ton of XLS files into XLSX files. While there are some solutions from Microsoft and others, none seemed to fit my fancy - so out to the internet I went!

I found some great starter scripts from multiple users, cited in the script itself, and modified them to add logging, recovery options, and a few other things that make the script a lot more useful for myself.

Currently, the script is set to set the Excel COM object as visible. Things actually go a lot faster if it is set to $false, but the scripts I was converting were throwing a lot of errors due to macros/lookups/etc that hung the script if it was hidden. I used a few AutoIT scripts to automate the 'OK-ing' of the warnings to help the process move along whilst I kept my coffee warm.

I do apologize for the lack of formatting in the script below, but this was quickly thrown together to help put out a few fires at my job!

#Source script created by Dmitry Sotnikov
#https://dmitrysotnikov.wordpress.com/2013/05/30/powershell-script-to-bulk-change-excel-file-formats/
#
#Modified by Mike Riston

# create COM object to use Excel

#set our search location
    $dir = Read-Host "Where are the files stored?"
    if($dir.EndsWith("\")){}else{$dir=$dir+'\';}
    Write-Host We are currently going to search for XLS files in $dir
    
    $searchpath=$dir+'*.xls'
    Write-Host $searchpath
    write-host "$searchpath"


#Set our Log Location
    $log= Read-Host "Where do you want the log files stored? (Directory only, please)"
    if($log.EndsWith("\")){}else{$log=$log+'\';}
    Write-Host We are going to store log files in $log

#Set our Old File Format Storage Location
    $xlsrepository= Read-Host "Where do you want the source XLS files to be moved to? (Directory only, please)"
    if($xlsrepository.EndsWith("\")){}else{$xlsrepository=$xlsrepository+'\';}
    Write-Host We are going to store XLS files in $xlsrepository
#
#Create Excel Object
$objExcel = new-object -comobject excel.application
#Set Excel to Visible
$objExcel.Visible = $True
#$objExcel.Visible = $False


$counter = 0



Function Set-FileTimeStamps {
# Taken from http://blogs.technet.com/b/heyscriptingguy/archive/2012/06/01/use-powershell-to-modify-file-access-time-stamps.aspx

 Param (

    [Parameter(mandatory=$true)]

    [string[]]$path,

    [datetime]$date = (Get-Date))

    Get-ChildItem -Path $path |

    ForEach-Object {

     $_.CreationTime = $creationtime

     $_.LastAccessTime = $accesstime

     $_.LastWriteTime = $modificationtime }

} #end function Set-FileTimeStamps



# open the files to re-save one by one
dir "$searchpath" -Recurse -ErrorAction SilentlyContinue | ForEach-Object {

    write-host (Get-Location).Path

    set-location $_.Directory
      
    $currentdirectory = Get-Location

    [string]$loglines= "

Orig File location:  " 
    $loglines|Out-File $log\XLS_Conversion.log -Append
    $_.FullName |Out-File $log\XLS_Conversion.log -Append
    [string]$loglines= "
Accessed at: "
    $loglines|Out-File $log\XLS_Conversion.log -Append
Get-Date -DisplayHint DateTime |Out-File $log\XLS_Conversion.log -Append
    [string]$loglines= "
    ---------------------------------
    CONVERTING BEGIN
    ---------------------------------
    "
    $loglines|Out-File $log\XLS_Conversion.log -Append

##Create Excel Object
#$objExcel = new-object -comobject excel.application
##Set Excel to Visible
#$objExcel.Visible = $True
##$objExcel.Visible = $False
    
    $doc = $objExcel.WorkBooks.Open($_.FullName)
    
     #Save in new format and with new extension
     #Format codes can be found here:
     #http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
    $doc.SaveAs(“$($_.Directory)\$($_.BaseName).xlsx”,
    [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)

    $directory=$_.Directory
    $BaseName=$_.BaseName
    [string]$newfilename=$_.Directory
    $newfilename=$newfilename+'\'
    $newfilename=$newfilename+$BaseName
    $newfilename=$newfilename+'.xlsx'

    [string]$loglines= "
Converted File Name: "
    $loglines|Out-File $log\XLS_Conversion.log -Append

    $newfilename|Out-File $log\XLS_Conversion.log -Append
    [string]$loglines= "
Last Exit Code: "
    $loglines|Out-File $log\XLS_Conversion.log -Append

    $creationtime=$_.CreationTime
    $modificationtime=$_.LastWriteTime
    $accesstime=$_.LastAccessTime
    
    

#kill ou excel object/session
$doc.Close()
$objExcel.Workbooks.Close()
#$objExcel.Quit()
#$objExcel = $null
    


    $LASTEXITCODE|Out-File $log\XLS_Conversion.log -Append

        [string]$loglines= "

Moving File To XLS Repository: "
    $loglines|Out-File $log\XLS_Conversion.log -Append
    
    $restorationLocation=$xlsrepository+$_.BaseName+'.xls'

## Begin check to find if file already exists in XLS Repository
## Begin check to find if file already exists in XLS Repository
## Begin check to find if file already exists in XLS Repository
## Begin check to find if file already exists in XLS Repository
    if (Test-Path $restorationLocation) {
        #File already exists!

        #Set new Restoration Location
        $restorationLocation=$xlsrepository+$_.BaseName+'_'+$rand+'.xls'

        $rand= Get-Random
        $string = 'File already exists! Appending '+$rand+' to the end.
New Recovery Destination will be: '+$restorationLocation
        Write-Warning $string
        $string | Out-File $log\XLS_Conversion.log -Append

        #move the actual file
        Move-Item -LiteralPath $_.FullName -Destination $restorationLocation -Verbose | Out-File $log\XLS_Conversion.log -Append
        #Fix the timestamps
        Set-FileTimeStamps -path $newfilename

        $restoration_batch=$xlsrepository+$_.BaseName+'_'+$rand+'_Recovery.ps1'
        New-Item $restoration_batch -ItemType File
             $restoration_batch_contents='Copy-Item "'+$xlsrepository+$_.BaseName+'.xls" -Destination "'+$_.FullName+'" -Verbose'
        $restoration_batch_contents|Out-File $restoration_batch
        
        [string]$loglines= "FILE RESTORATION LOCATION: "
        $loglines|Out-File $log\XLS_Conversion.log -Append
        
        $restoration_batch|Out-File $log\XLS_Conversion.log -Append


        } 
        Else {

             #move the actual file
             Move-Item -LiteralPath $_.FullName -Destination $restorationLocation -Verbose | Out-File $log\XLS_Conversion.log -Append
             #Fix the timestamps
             Set-FileTimeStamps -path $newfilename

             $restoration_batch=$xlsrepository+$_.BaseName+'_Recovery.ps1'
             New-Item $restoration_batch -ItemType File
             $restoration_batch_contents='Copy-Item "'+$xlsrepository+$_.BaseName+'.xls" -Destination "'+$_.FullName+'" -Verbose'
             $restoration_batch_contents|Out-File $restoration_batch
                     
             [string]$loglines= "FILE RESTORATION LOCATION: "
             $loglines|Out-File $log\XLS_Conversion.log -Append
             
             $restoration_batch|Out-File $log\XLS_Conversion.log -Append

    }

## RESTORE TIMESTAMPS


    [string]$loglines= "
        
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________"
    $loglines|Out-File $log\XLS_Conversion.log -Append

    #


#check log filesize
    $logfile = $log+'\XLS_Conversion.log'
    if (($logfile.Length/5MB).ToString() -gt 1) {
        #File Size is under 7.5 MB - continue
        } ELSE {
            [String]$newlogname='XLS_Conversion_'+$counter+'.log'
            Move-Item $logfile -Destination $log\$newlogname -Force
            $counter += 1
            }
            

#Play a notifier sound


$player = New-Object System.Media.SoundPlayer "$env:windir\Media\LOZ_Get_Heart.wav"
$player.Play()
Start-Sleep -Milliseconds 200
$player.Play()

#Read-Host continue?



}
$objExcel.Quit()
$objExcel = $null

#$objExcel.Quit()
#$objExcel = $null
# SIG # Begin signature block
# MIIFqQYJKoZIhvcNAQcCoIIFmjCCBZYCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR
# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUopkOEXwFfPwWneoURRqcfNsT
# q+OgggNAMIIDPDCCAiigAwIBAgIQHUF1Rayo8YtAvgp96n/GoDAJBgUrDgMCHQUA
# MB4xHDAaBgNVBAMTE01pa2UgUmlzdG9uIFJvb3QgQ0EwHhcNMTUwNDAxMTI0ODQ5
# WhcNMzkxMjMxMjM1OTU5WjA0MRkwFwYDVQQLExBQT1NIIENvZGVTaWduaW5nMRcw
# FQYDVQQDEw5NaWtlIFcuIFJpc3RvbjCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC
# AQoCggEBAKX+TtJ/mm7yhbwe7FunaZRH/acuw1ZfyKB2HuiUHEwGY5i/su5NiUj5
# 6NnIsnmHziFXDkIq/r7XYXaIZYnbcBp0Tl9gfcOKLezVL6xPwuDZA9icf2lD5lC8
# serxOAoML9FBgmKH/lsBrjnwr8qC7GiAvWzmtwMzeQki8XBLeW70+EcD6Sav4WLR
# J8/ELUmCqy5BgfYsQAj5zRVWNOTIMaS4uQV2mu5wAOhlce3g1i95DJr+GEgCCqwD
# ipkMZA+zJF0AxcPCZuAvvumyD6/ZQwxJpaLxxGePCSKdfvNXG+9I2zT+zRZbyPJp
# 7ERlhxtO2kvZKPv7Sjw8Hcv/PIcR1bECAwEAAaNoMGYwEwYDVR0lBAwwCgYIKwYB
# BQUHAwMwTwYDVR0BBEgwRoAQXoMxG2FFDtwJ1AiJdt5mLqEgMB4xHDAaBgNVBAMT
# E01pa2UgUmlzdG9uIFJvb3QgQ0GCEKI/6MNLR/6KTE2mCXd6sVAwCQYFKw4DAh0F
# AAOCAQEAoGPd7PIuUc3V5HLp/ut49sT4OVGwvMHCxQuuHhMrhjPUAhG4PgqsbXd+
# hEvcDhNsV+Hf3Drd9mGvzcvz0dw+RIM+s8EdzRYGgW3HHE1vNrKRIgcgrYi+oZ1Y
# 1GowgsVNpLfDG8RvSSQQsqie8WtnjXbIilZrnQFcZCKAyvax1Su7bFTizPEmdG+r
# atsj3p7ANf03icOO29nbqe87Q0xhUOIv5najwUwpn26SFBIq2SYI70fN/XJWzzR0
# nM/lFgMdIOoI+1Y912Oa2Z9BiJNMXV7eaKkDKnO7fLoHqpGQxI4NphOltDG4y1z9
# Vw/Q4l9iUrjyJaQgMamNTBakl9FZezGCAdMwggHPAgEBMDIwHjEcMBoGA1UEAxMT
# TWlrZSBSaXN0b24gUm9vdCBDQQIQHUF1Rayo8YtAvgp96n/GoDAJBgUrDgMCGgUA
# oHgwGAYKKwYBBAGCNwIBDDEKMAigAoAAoQKAADAZBgkqhkiG9w0BCQMxDAYKKwYB
# BAGCNwIBBDAcBgorBgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAjBgkqhkiG9w0B
# CQQxFgQUwHIhOHu+ZSpk2P+F96vlWeDMAIAwDQYJKoZIhvcNAQEBBQAEggEAYgEz
# +PlASMYtZyxsoBdxHwbJa/4pDY8w18SCh7v9mQqmVbbMq9Hwvawuu9fFX2mLAgc9
# Xm2HardGLHdL/r5x1VN6byolXt1m2+CaYAQ1EaeoH53fkOfVU0K+B7eu89bkOmEK
# vlAFgKZnHXyGvNayexROi/r8suTsUeEZ22q6cT8u2DSvZFzA0o3r4lBA8EJNNBom
# EQBAdBEVtV6xc6kOmG8m9w89w5ckyfh2PTJ6Ab7rJ7li47jYxwHe3c+7VpS5Ftyn
# ur1Re9TfMRwg5geTAvg0v+VoorT/025lLWcKLxCNKIRS0lO6yLDFW+LrQbtu+N7L
# ewDwpARg9mqgoMAkKw==
# SIG # End signature block


For those of you looking at lines 221-224, this is a nice little audible cue that a file has been processed. I added this before I got all of my AutoIT scripts working so that I could continue working on other things while my computer kept the conversion running. If I didn't hear a cue for 30 seconds or so, I knew something was stuck and I had to investigate.

Just go ahead and change the .WAV path noted on line 221 to a short .WAV that you won't mind hearing every 5-15 seconds. Or just go ahead and commend out the entire block!

Hope that this helps someone out and a huge thank you to those users cited above (and any that helped influence the solution) for their insights into getting this working.

Comments

Popular Posts