Rename Excel sheet 1

[Fractured]

Ok, so im working on a project where I have to rename Sheet1 of an excel workbook. I looked in the excel.udf wiki and it says to use this line of code :  

$oSheet.Name = "Name of the sheet"

The only problem is I dont always know what the name of Sheet1 is....(im assuming that is what "$oSheet.Name" is referring to. So how would I go about renaming sheet 1? 

 

[water]

$oWorkbook.Sheets(1).Name = "xyz"

[water]

I have updated the wiki

[Fractured]

Thanks! Works like a champ!

 

[Fractured]

Okay, brain has fallen out...if I want to remove qoutes (") from the cell I know I cant us """, can I use the ascii code? or <">?

>> Never mind...figured it out...use single quote around double quote.     '"'

 

Edited October 15, 2019 by Fractured

[Fractured]

Finding im Excel challenged...To left justify text in sheet, wiki gives the $oRange.HorizontalAlignment = $xlLeft command. I can reason the $oRange is probly what I want to left justify, which is columns A and B....so I tried "$oRange.HorizontalAlignment = $xlLeft" with $oRange = "A:B",  then I tried placing "A:B" after $oRange ($oRange."A:B".)

Now im just lost!! The basics have been very easy to understand in the help file, but the wiki is actually no use since the commands seem very cryptic without a basic example of use beyond the one line it shows...especially since im just learning the excel modification side of autoit! Dang it Jim! Im a doctor, not a programming guru!!!!

[Fractured]

p.s. this is the modification's im making so far...

;Format Excel Sheet 
    $oWorkbook.Sheets(1).Name = "MAIN"                          ; Rename sheet
    $oRange.HorizontalAlignment = $xlLeft                       ; Horizontal left justify
    _Excel_RangeReplace ( $oWorkbook, "MAIN", Default, "{", "") ; Remove {
    _Excel_RangeReplace ( $oWorkbook, "MAIN", Default, "}", "") ; Remove }
    _Excel_RangeReplace ( $oWorkbook, "MAIN", Default, '"', "") ; Remove }
    _Excel_RangeReplace ( $oWorkbook, "MAIN", Default, ' ', "") ; Remove leading spaces
    
    $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete  ;  Remove blank rows

 

[JLogan3o13]

Forum search, along with the help file, are your best friends as this gets asked a lot when working with Excel. This works just fine for me:

#include <Excel.au3>

$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Justify.xlsx")
$oWorkbook.ActiveSheet.Columns("A:B").HorizontalAlignment = $xlLeft

 

Edited October 15, 2019 by JLogan3o13

[Fractured]

Thanks   I had been searching, but got tasked with an RF unit to test, so had to run away! The only other thing now, and im searching and think I found the answer with StringStripWS, but hoping there is a quicker way with the _Excel_RangeReplace.....deleting leading spaces....

Some of the entries are formated like this "    unit", I would like it to be "unit"...was hoping the justify would work but whom ever entered the data actually used the space bar for the spaces, so it dosent shift fully left....

Will I have to read through the excel sheet and use the Strip whit Space command (about 123,000 rows, but only 2 columns) or is there a way for the range replace to remove the leading spaces? The method in the above posted code does not seem to work

[FrancescoDiMuro]

@Fractured

Read the data fron Excel sheet with _Excel_RangeRead(), loop through the array returned, replace the value of the elements in the array using StringStripWS() with the parameter $STR_STRIPLEADING, and write the array back to the Excel sheet using _Excel_RangeWrite

[Fractured]

Thanks @FrancescoDiMuro, I was hopping to not have todo that...I loaded up the excel.udf and read through how the _Excel_RangeReplace worked and came up with a string that worked!! 

 

_Excel_RangeReplace ( $oWorkbook, "MAIN", Default, " ?", "."); Remove leading spaces
    _Excel_RangeReplace ( $oWorkbook, "MAIN", Default, ".", "")

_Excel_RangeReplace ( $oWorkbook, "MAIN", Default, " ?", "."); Remove leading spaces
_Excel_RangeReplace ( $oWorkbook, "MAIN", Default, ".", "")

It changes all the leading spaces to periods, then removes the periods..alas ill have to check the work excel files to see if periods were used and possibly change them to another character, but atleast it worked on the test XLSX that I am using!!

 

[Fractured]

switch that...

_Excel_RangeReplace ( $oWorkbook, "MAIN", Default, "~ ", ""); Remove leading spaces

This works to remove leading spaces... the ~ tells it to literally look just for the <space> and remove it!