Sign in to follow this  
Followers 0
roofninja

change a upper case to lower case in excel

5 posts in this topic

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

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites



Hi,

which column do you want to lowerCase?

Mega


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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?


RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0