Examples for VBScript MS-Office App Creator: Change the codeName property of a worksheet

changeCodeName.vb

The following function creates a new worksheet and changes its .codeName property to shXYZ.

It turned out that after creating a worksheet programmatically, its codename property cannot be easily changed because of some quirks with Excel. This stackoverflow answer was very helpful for me to resolve the issue.

option explicit

sub createSheet()

    dim sh as worksheet
    set sh = thisWorkbook.sheets.add
    sh.name = "Added in VBA"

  '
  ' https://stackoverflow.com/a/67904097/180275
  '
    on error resume next
    dim dummy as string
    dummy = thisWorkbook.VBProject.vbComponents(sh.CodeName).properties("codename")
    on error goto 0

    thisWorkbook.vbProject.vbComponents(sh.codeName).properties("_codeName") = "shXYZ"

end sub

Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/codeName/changeCodeName.vb

func.vb

With the changed codeName property, the respective worksheet can now be referenced by that name (here: shXYZ):

option explicit

sub someFunc() ' {
  '
  ' Use the value of the sheets codeName property to refer to the sheet in VBA.
  '
    shXYZ.cells(2,2) = "Hello..."
end sub ' }

Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/codeName/func.vb

create.wsf

Using the VBS Office-App creator to create the workbook.

<job>
<script language="VBScript" src="../../../create-MS-Office-app.vbs" />
<script language="VBScript">

   option explicit

   dim app
   dim xls
   set xls = createOfficeApp("excel", currentDir() & "created.xlsm")

   if xls is nothing then ' {
      wscript.echo("Could not create excel worksheet.")
      wscript.quit(-1)
   end if ' }

   set app = xls.application

   insertModule app, currentDir() & "changeCodeName.vb", "init", 1
   insertModule app, currentDir() & "func.vb"          , "func", 1

   app.run "createSheet"

   xls.save
   wscript.echo("The end")
 '
 ' Bring created Excel Workbook to the front:
 '
   createObject("WScript.Shell").appActivate(app.caption)

</script> </job>

Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/codeName/create.wsf

Examples for VBScript MS-Office App Creator: Change the codeName property of a worksheet

changeCodeName.vb

func.vb

create.wsf

See also