change a upper case to lower case in excel

[roofninja]

I am trying to search an excel sheet for a column and then change all upper case letters to lower case except the "L". The box will have numbers and letters. I have most of it written(I think) but I can't seem to figure out where I am going wrong. If you think that I am going at this the wrong way, please correct me. I am out of practice.

I have attached a test file to use.

#include <Excel.au3>
#include <Array.au3>

$wkfile = FileOpenDialog ("Please select the file you want to convert.", @ScriptDir, "(*.xls)")
$wkfile = _ExcelBookOpen($wkfile,0, False);this will open the excel file.
$exlist = _ExcelSheetList($wkfile)

For $i = $exlist[0] To 1 Step -1 ;Work backwards through loop
    _ExcelSheetActivate($wkfile, $exlist[$i]) ;Using the String Name returned in the Array Elements
    If $exlist[$i] = "Discards" Then ;do nothing
    ElseIf $exlist[$i] = "Network Printers" Then ;do nothing
    ElseIf $exlist[$i] = "Macs" Then ;do nothing
    Else;do all of this
        $wkarray = _ExcelReadSheetToArray ($wkfile)
        If $wkarray[0][0] = "" Then ;do nothing
        Else;do the rest of this
local $row, $column = 0
        $row = _ArraySearch($wkarray,"ServiceTag",$column) ;column 8
        MsgBox(64,"row and column","Your Row is:_"&$row&" _Your column number is:_"&$column)

        If $row = "-1"  and @error = "1" Then ;do nothing
            Else
            _ArrayDisplay($wkarray,"spreadsheet array for:_"&$exlist[$i])
            $low = StringLower ($exlist[$i])
        EndIf ; nested colum loop
        EndIf ;nested wkarray loop
    EndIf ;nested exlist loop
Next ;end of for loop
_ExcelBookClose($wkfile)
Exit

[Xenobiologist]

Hi,

which column do you want to lowerCase?

Mega

[Xenobiologist]

Hi,

which column do you want to lowerCase?

Mega

ServiceTag?

#include <Excel.au3>
#include <Array.au3>

$column = "ServiceTag"
$targetColumn = 0
$sFilePath1 = @ScriptDir & "\Test2_inv.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

$aArray = _ExcelReadSheetToArray($oExcel)

For $i = 0 To $aArray[0][1]
    If StringInStr($aArray[1][$i], $column) <> 0 Then $targetColumn = $i
Next

For $i = 0 To $aArray[0][0]
    $aArray[$i][$targetColumn] = StringReplace(StringLower($aArray[$i][$targetColumn]), 'l', 'L')
Next
_ExcelWriteSheetFromArray($oExcel, $aArray)
_ExcelBookSaveAs($oExcel, @ScriptDir & "\Test3_inv.xls", "xls", 0, 1) ; Now we save it into the temp

Mega

[roofninja]

Mega,

Thanks for the rewrite. It is simple. It shows how much I am out of practice.

Now that it is working, how do I go to other tabs and do the same thing?

[Xenobiologist]

Mega,

Thanks for the rewrite. It is simple. It shows how much I am out of practice.

Now that it is working, how do I go to other tabs and do the same thing?

You need to add some error checking!

But this should show how it works.

#include <Excel.au3>
#include <Array.au3>

$column = "ServiceTag"
$targetColumn = 0
$sFilePath1 = @ScriptDir & "\Test2_inv.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

$SheetList_A = _ExcelSheetList($oExcel)
For $y = 1 To UBound($SheetList_A) - 1
    _ExcelSheetActivate($oExcel, $y)

    $aArray = _ExcelReadSheetToArray($oExcel)

    For $ii = 0 To $aArray[0][1]
        If StringInStr($aArray[1][$ii], $column) <> 0 Then $targetColumn = $ii
    Next

    For $i = 0 To $aArray[0][0]
        $aArray[$i][$targetColumn] = StringReplace(StringLower($aArray[$i][$targetColumn]), 'l', 'L')
    Next
    _ExcelWriteSheetFromArray($oExcel, $aArray)
Next

_ExcelBookSaveAs($oExcel, @ScriptDir & "\Test3_inv.xls", "xls", 0, 1)

Mega