Accessing and changing Excel sheet names

[dgendrud]

These statements to access and change Excel sheet names seem to be correct based on Wiki and Forum searches.  They are not working for me.  These seem to be Excel VBA type statements.  In general, do all VBA type of statements work in an AutoIt script?  I have been using several for cell formatting and they have been working.  Some other VBA statements have not been working for me, this is a couple of examples of that.   What, in general, are the guidelines for using these type of statements in scripts?

Thanks

Doug

 

CopyTest2.au3

[mistersquirrle]

When posting code like this is probably best to use the code formatting option in the post editor: 

 

 

As to  your question, I don't have Excel to test, but I was looking in the helpfile to see if there was a rename option for sheets. It doesn't look like it, and the closest I found to naming a sheet was _Excel_SheetAdd. Looking that the function, the format it uses is:

$oWorkbook.WorkSheets($iIndex2).Name = $aName[$iIndex1]

So give this a try: 

#include <Excel.au3>

Global $oExcel
Global $IPSnglWLs = "C:\TRADING_IP\IP_WATCHLISTS.xlsx"

; Create application object
Global $oWls = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Sleep(500)

;OPEN THE IP_WATCHLISTS FILE
Global $IPSnglwlst = _Excel_BookOpen($oWls, $IPSnglWLs)
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 - XXX_WL file")
    Exit
EndIf
MsgBox(0, "status", "IP_WATCHLISTS opened")

;Trying to access and change sheet names
Global $Sh1Name = $IPSnglwlst.WorkSheets(1).Name        ;Actual name of sheet1 is HG-g;  This statement is returning "sheet1"
MsgBox(0, "Sh1Name", $Sh1Name)

$IPSnglwlst.WorkSheets(2).Name = "HG"        ;This statement is not changing the name of Sheet2 on the workbook

AutoIt is using a COM object from Excel to interact with it. That means that you should be able to use anything documented on: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet (for the most part, at least. I'm not super familiar with COM things).

Specifically: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.name

What you were using (Sheets) is a bit different, and does not appear to have the "Name" property: https://learn.microsoft.com/en-us/office/vba/api/excel.sheets

[dgendrud]

Thank you, I will try this.  Code formatting option: didn't know about that but will use it next time.

[dgendrud]

Update:

Unfortunately,  switching from Sheets to Worksheets and using Global declarations did not make any difference.

[ioa747]

the script of @mistersquirrle working . I have test it with a empty   IP_WATCHLISTS.xls in @ScriptDir

try this if it working for you, with a new workbook

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Create a new workbook with only 2 worksheets
_Excel_BookNew($oExcel, 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$oExcel.WorkSheets(1).Name = "HG1"
$oExcel.WorkSheets(2).Name = "HG2"

 

[dgendrud]

OK, I ran this script and it's working for me.  Not sure what the issue with my script is, yet,  but now that I have a working script I will be able to figure this out.

Many thanks for quick responses!

 

[ioa747]

maybe with "C:\TRADING_IP\IP_WATCHLISTS.xlsx" is something ...