Jump to content

Recommended Posts

Posted

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? 

 

Posted

$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

 

Posted

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

 

Posted (edited)

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
Posted

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

Posted

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

 

  • Moderators
Posted (edited)

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!

Posted

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

Posted

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

 

Posted

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!

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
  • Recently Browsing   0 members

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