Search notes:

Examples for VBScript MS-Office App Creator: insertSheet

This is an example for the VBS command line creator for Office Apps that demonstrates the function insertSheet.
insertSheet inserts a worksheet, names that sheet and assigns a name to the sheet's code name. Thus, it is possible to use insertModule with the code name's value which is for example used for worksheet events.

Code names and 'global' variables

The following two pictures show the main features of insertSheet that is procuced when executing create.wsf.
The inserted sheet's code names value is set (here: shOne and shTwo):
The sheet name itself is assigned

create.wsf

This is the script to be executed on the commandline with script.exe to produce the example: 
<job>
<script language="VBScript" src="..\..\..\TF\DWH_DM_RISKFINANCE\lib\VBA\VBScript-App-Creator\create-MS-Office-App.vbs" />
<script language="VBScript">

option explicit

dim wb
set wb = createOfficeApp("excel", currentDir() & "codeNameExample.xlsm")

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

dim app  : set app = wb.application
dim sh_1 : set sh_1 = insertSheet(wb, "first sheet" , "shOne")
dim sh_2 : set sh_1 = insertSheet(wb, "Second sheet", "shTwo")

insertModule app, currentDir() & "shOne.vb", "shOne", 1
insertModule app, currentDir() & "shTwo.vb", "shTwo", 1
insertModule app, currentDir() & "func.vb" , "func" , 1

wb.save

if not compileApp(app) then
   wscript.echo("! compilation failed !")
end if

app.run "main"

' app.quit

</script> </job>
Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/insertSheet/create.wsf

func.vb

This is an VBA module that uses the variables shOne and shTwo: 
option explicit

sub main() ' {
  '
  ' Use "global variables" shOne and shTwo to
  ' refer to the corresponding sheets.
  '
    shOne.cells(2,2) = "This value was inserted by main()"
    shTwo.cells(2,2) = "The name of shOne is " & shOne.name

end sub ' }
Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/insertSheet/func.vb

shOne.vb and shTwo.vb

These two modules serve as the sheet's module. 
option explicit

private sub worksheet_activate()
    debug.print "Sheet one was activated"
end sub
Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/insertSheet/shOne.vb
option explicit

private sub worksheet_activate()
    debug.print "Second sheet was activated"
end sub
Github repository VBS-MS-Office-App-Creator, path: /examples/Excel/insertSheet/shTwo.vb

Index