N. Lund N. Lund - 1 year ago 173
PowerShell Question

Powershell and Excel, case-sensitivity with .replace

I'm fairly new to Powershell scripting, so forgive me if the code and/or explanation is sloppy. I'm trying to create a script that opens up an Excel spreadsheet and replaces all special letters (æöý and so on) with standard characters.
Now, the script works, but it turns all of the upper case characters in the spreadsheet into lowercase characters (eg. "Ålborg" becomes "alborg"), which I would prefer if it didn't. I've been led to believe that the .replace method is case sensitive, although that doesn't seem to be the case here.

Now I've tried a couple of things, double quotes instead of single quotes, both double and single quotes, -creplace instead of .replace which just spits out


without actually changing anything in the spreadsheet (I think $objRange is an array, is that why?), adding a .replace for both the uppercase and lowercase letter, but nothing works. Anyone able to point out my mistake?


$excelFile = New-Object system.windows.forms.openfiledialog
$excelFile.InitialDirectory = 'Q:\Address_convert'
$excelFile.MultiSelect = $false

$excelFile = $excelFile -replace "System.Windows.Forms.OpenFileDialog: Title: , FileName: ", ""

if ($excelFile -like '*.xlsx*') {

$excel = New-Object -comObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$workBook = $excel.WorkBooks.Open($excelFile)

$oReturn = [Microsoft.VisualBasic.Interaction]::MsgBox("Replace special characters in $excelFile ?", 'YesNo,MsgBoxSetForeground,Question', 'Address Replace')
switch ($oReturn){
"Yes" {
For($i = 1 ; $i -le $workBook.Sheets.count ; $i++)
$workSheet = $workBook.Sheets.item($i)
$objRange = $workSheet.UsedRange
$objRange.Replace('á', 'a')
$objRange.Replace('à', 'a')
$objRange.Replace('â', 'a')
$objRange.Replace('ã', 'a')
$objRange.Replace('ä', 'a')
$objRange.Replace('å', 'a')
$objRange.Replace('æ', 'ae')
$objRange.Replace('ç', 'c')
$objRange.Replace('é', 'e')
$objRange.Replace('è', 'e')
$objRange.Replace('ê', 'e')
$objRange.Replace('ë', 'e')
$objRange.Replace('í', 'i')
$objRange.Replace('ì', 'i')
$objRange.Replace('î', 'i')
$objRange.Replace('ï', 'i')
$objRange.Replace('ð', 'd')
$objRange.Replace('ð', 'd')
$objRange.Replace('ñ', 'n')
$objRange.Replace('ó', 'o')
$objRange.Replace('ò', 'o')
$objRange.Replace('ô', 'o')
$objRange.Replace('õ', 'o')
$objRange.Replace('ö', 'o')
$objRange.Replace('ø', 'o')
$objRange.Replace('ú', 'u')
$objRange.Replace('ù', 'u')
$objRange.Replace('û', 'u')
$objRange.Replace('ü', 'u')
$objRange.Replace('ý', 'y')
$objRange.Replace('ÿ', 'y')
$objRange.Replace('ß', 'ss')
$objRange.Replace('þ', 'th')
if (!$workBook.saved) {$workBook.save()}

[Microsoft.VisualBasic.Interaction]::MsgBox("Replacement completed", 'OkOnly,MsgBoxSetForeground,Information', 'Address Replace')
"No" {
if (!$workBook.saved) {$workBook.save()}

[Microsoft.VisualBasic.Interaction]::MsgBox("Replacement aborted!", 'OkOnly,MsgBoxSetForeground,Exclamation', 'Address Replace')
Else {[Microsoft.VisualBasic.Interaction]::MsgBox("Specified path ($excelFile) does not contain an .xlsx file!", 'OkOnly,MsgBoxSetForeground,Exclamation', 'Address Replace')}

Answer Source

Try the replacement operations like so

$optionalParameter = [System.Type]::Missing
$objRange.Replace('á', 'a', $optionalParameter, $optionalParameter, $true)

so that the first two optional parameters are not used and then $true is passed to indicate to match the case - see here for the signature

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download