Search notes:

Template to create a worksheet with Visual Basic for Applications (VBA)

The following code is tries to be a template that demonstrate how a workbook with a worksheet can be created in Visual Basic for Applications

call application.VBE.activeVBProject.references.addFromGuid("{00020813-0000-0000-C000-000000000046}", 1,  8)

option explicit

sub main() ' {

    dim xls as excel.application
    dim bok as excel.workbook
    dim sht as excel.workSheet

    set xls = new excel.application

    xls.visible = true

    set bok = xls.workbooks.add
    set sht = bok.worksheets.add

    sht.cells(1, 1) = "Hello world"
    sht.cells(2, 1) = "The number is:"
    sht.cells(2, 2) =  42

    sht.columns(1).autofit
    sht.columns(2).autofit

    dim fileName as string
    fileName = environ("TEMP") & "\" & "bla.xlsx"

  '
  ' Delete Excel workbook if already created
  ' in a previous run.
  '
    if dir(fileName) <> "" then kill fileName

    bok.saveAs fileName

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/misc/template.bas

When run, it produces this:

Adding reference to object model

If this example is run in a non-Excel Office environment, a reference needs to be added for the Excel Object Model.

This is best done in the immediate window of the Visual Basic Editor.

Index