Sign in to follow this  
Followers 0
Noddle

How to rename the Internal Name of a Excel Sheet

12 posts in this topic

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

SheetName.jpg

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 )?

 

SheetName.jpg

Share this post


Link to post
Share on other sites

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

code-name.gif

 

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

Share this post


Link to post
Share on other sites

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
1 person likes this

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  
Followers 0