Jump to content

Rename Excel sheet 1


Recommended Posts

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? 

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I have updated the wiki :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by Fractured
Link to comment
Share on other sites

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!!!!

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

  • Moderators

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 by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

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 :(

Link to comment
Share on other sites

@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 :)

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

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!!

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...