How to rename the Internal Name of a Excel Sheet

[Noddle]

Hi,

I can rename the "ActiveSheet.Name" ( $oExcel.ActiveSheet.Name = $_NewSheetName ), but not the internal name of the sheet by it's "ActiveSheet.Name",

in the picture,  the Green Box I can rename easly,  but I want also to rename the red box,  the same as the Green box

Thanks for anyhelp

#include <Excel.au3>

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

; Create a new spreadsheet
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = true
$oExcel = _ExcelBookNew()

; Rename Current Sheet      ( Green Box in picture )
$_NewSheetName = "Example"
$oExcel.ActiveSheet.Name = $_NewSheetName

; Reaname "Sheet1"          ( Red Box in picture to $_NewSheetName )
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
For $oModule in $oModules
    if  $oModule.Name = "Sheet1" then     ;  how can I search for $_NewSheetName   "Example"     and not use "Sheet1"
        $oModule.Name = $_NewSheetName    ;  "Example"
        ExitLoop
    endif
next


Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

[water]

Why do you need to rename the "internal" name?

[Noddle]

Why do you need to rename the "internal" name?

 

It relates back to this thread of mine,

'?do=embed' frameborder='0' data-embedContent>>

since I'm injecting dynamic VB code into the "Sheet", I can only select the sheet only via the "internal name", because I don't know how else to do it,

so I want the internal name, and sheet name to be the same,

$ModuleName = "Sheet1"
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

For $oModule in $oModules
    If $oModule.Type = 100 And $oModule.Name = $ModuleName Then
        $oModule.CodeModule.AddFromString ( _
                                            'Private Sub Worksheet_Change(ByVal Target As Range)' & @CRLF & _
                                            'Select Case Target.Address' & @CRLF & _
                                            '    Case "$E$3"' & @CRLF & _
                                            '        If UCase(Range("E3").Value) = "N" Then' & @CRLF & _
                                            '            Beep'  & @CRLF & _
                                            '        End If' & @CRLF & _
                                            'End Select' & @CRLF & _
                                            'End Sub' _
                                            )
        ConsoleWrite ("Writing Code")
    EndIf
Next

[Noddle]

Is there a way to get the internal sheet name,  from the "external name" ?

ie,  get the name of the red box,   by using a process to query from the green box name ( the name you see on the tab in excel )?

 

[Bert]

Have you tried using the macro recorder in Excel to see how it records the name change? If you can get that then it should be easy to code.

[Noddle]

Have you tried using the macro recorder in Excel to see how it records the name change? If you can get that then it should be easy to code.

 

From my playing with the macro recorder,  it can not be done,

also I found out that what I was to do, to rename the "sheet's code name" programmatically,  it can not be done, 

http://www.techrepublic.com/blog/10-things/10-ways-to-reference-excel-workbooks-and-sheets-using-vba/

but I have found another way to do what I want,  But i'm not sure how to do it in Autoit, it's near the bottom of this page,

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

here a snippit of what it says,

In the screen shot , the CodeName for the sheet with a tab name of Budget is Sheet3. A sheets CodeName is always the name not inside the parenthesis when looking in the Project Explorer. We can reference this sheet with VBA code in the Workbook by using: Sheet3.Select as apposed to Sheets("Budget").Select or Sheets(3).Select

 

I know how to do this, Sheets("Budget").Select  and Sheets(3).Select,

$oExcel.ActiveWorkbook.Sheets("Budget").Select()

$oExcel.ActiveWorkbook.Sheets(3).Select()

but I don't know how to do this Sheet3.Select

$oExcel.ActiveWorkbook.Sheet3.Select()

I get this error

err.number is:     -2147352570

err.windescription:    Unknown name.

anyone have a solution for me ?

Nigel

[Noddle]

Wouldn't you know it,   I think I found what I need, but it's in VB,

http://www.cpearson.com/excel/RenameProblems.aspx

under the topic Using Code Names

could someone translate it to AutoIT  for me please,

Function GetWorksheetFromCodeName(CodeName As String) As Worksheet
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then
            Set GetWorksheetFromCodeName = WS
            Exit Function
        End If
    Next WS
End Function

Dim WS As Worksheet
Set WS = GetWorksheetFromCodeName("Sheet3")
Debug.Print WS.Name

[Bert]

nice find!

[jguinch]

Changing the CodeName seems to be difficult...

Here is an example to get the CodeName and Name values for each sheet :

#Include <Excel.au3>

$oExcel = _ExcelBookOpen(@ScriptDir & "\myFile.xls", 0)


For $i = 1 To $oExcel.Sheets.Count
    $CodeName = $oExcel.Worksheets($i).CodeName
    $name = $oExcel.Worksheets($i).Name
    MsgBox(0, "", "Worksheets(" & $i & ").Name = " & $name & @CRLF & "Worksheets(" & $i & ").CodeName = " & $CodeName)
Next

But now, for a modification of the CodeName, there are some examples using VBA, but I did not found only one workable with AutoIt.

[Noddle]

Changing the CodeName seems to be difficult...

Here is an example to get the CodeName and Name values for each sheet :

#Include <Excel.au3>

$oExcel = _ExcelBookOpen(@ScriptDir & "\myFile.xls", 0)


For $i = 1 To $oExcel.Sheets.Count
    $CodeName = $oExcel.Worksheets($i).CodeName
    $name = $oExcel.Worksheets($i).Name
    MsgBox(0, "", "Worksheets(" & $i & ").Name = " & $name & @CRLF & "Worksheets(" & $i & ").CodeName = " & $CodeName)
Next

But now, for a modification of the CodeName, there are some examples using VBA, but I did not found only one workable with AutoIt.

 

Thank you for this,

I can make this do what I need now  :-)

Nigel

[water]

Unfortunately you can't set the CodeName property using COM. The property is read-only.

[Bert]

At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.