Sheet

Sheets

ActiveWorkbook.Sheets

Returns all worksheets in the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // A collection of all Sheet objects in the Workbook.
    const sheets = await app.ActiveWorkbook.Sheets;
  }

Methods

ActiveWorkbook.Sheets.Add()

Adds a new worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Add({ Before, After, Count, Type, Name })

  expression: An application object for the document type.

• Parameters

  Property	Data type	Required	Description
  Before	String/Number	Yes	The worksheet object before which the new worksheet is added.
  After	String/Number	No	The worksheet object after which the new worksheet is added.
  Count	Number	No	The number of worksheets to add. The default value is 1.
  Type	Enum	No	The type of the new worksheet. For more information, see XlSheetType.
  Name	Name	No	The name of the new worksheet.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // The Sheets object.
    const sheets = await app.ActiveWorkbook.Sheets;
  
    // Add a worksheet.
    await sheets.Add(null, null, 1, app.Enum.XlSheetType.xlWorksheet, 'New Worksheet');
  }

Properties

ActiveWorkbook.Sheets.Count

Returns the number of worksheets in the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Count

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // The Sheets object.
    const sheets = await app.ActiveWorkbook.Sheets;
  
    // The number of worksheets.
    const count = await sheets.Count;
    console.log(count);
  }

Sheet

ActiveWorkbook.Sheets.Item(Index)

Returns a single worksheet object from the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index)

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // The Sheet object.
    const sheet = await app.ActiveWorkbook.Sheets.Item(1)
  }

Methods

ActiveWorkbook.Sheets.Item(Index).Delete()

Use the Delete() method to delete a single worksheet from the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Delete()

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    await activeSheet.Delete();
  } 

ActiveWorkbook.Sheets.Item(Index).ExportAsFixedFormat()

Exports a worksheet as a PDF file or an image and returns the URL of the exported file.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).ExportAsFixedFormat({ Type })

  expression: An application object for the document type.

• Parameters

  Property	Data type	Required	Description
  Type	Enum	No	The type of the exported file. For more information, see XlFixedFormatType. Note Currently, you can only export to image and PDF formats.

• Return value

  Property	Data type	Description
  url	string	The URL of the exported file.

• Examples

  • Export to PDF

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Export the entire worksheet.
      const workbookPdfUrl = await app.ActiveWorkbook.Sheets.Item(1).ExportAsFixedFormat();
      console.log(workbookPdfUrl);
    }

  • Export to an image

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Export the entire worksheet.
      const workbookPdfUrl = await app.ActiveWorkbook.Sheets.Item(1).ExportAsFixedFormat();
      console.log(workbookPdfUrl);
    }

ActiveWorkbook.Sheets.Item(Index).Protect()

Protects the specified worksheet from being modified.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Protect({ Password, DrawingObjects, Scenarios, AllowSelLockedCells, AllowSelUnlockedCells, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables })

  expression: An application object for the document type.

• Parameters

  Property	Data type	Default value	Required	Description
  Password	String		No	A case-sensitive string that specifies the password for the worksheet or workbook. If you omit this parameter, you can unprotect the worksheet or workbook without a password. Otherwise, you must specify the password to unprotect it. If you forget the password, you cannot unprotect the worksheet or workbook.
  DrawingObjects	Boolean	false	No	If set to true, shapes are protected.
  Scenarios	Boolean	true	No	If set to true, scenarios are protected. This parameter applies only to worksheets.
  AllowSelLockedCells	Boolean	true	When the value is set to true, you cannot select locked cells.
  AllowSelUnLockedCells	Boolean	true	If set to `true`, unlocked cells cannot be selected.
  AllowFormattingCells	Boolean	false	No	If set to true, allows users to format any cell on a protected worksheet.
  AllowFormattingColumns	Boolean	false	No	If set to true, allows users to format any column on a protected worksheet.
  AllowFormattingRows	Boolean	false	No	If set to true, allows users to format any row on a protected worksheet.
  AllowInsertingColumns	Boolean	false	No	If set to true, allows users to insert columns on a protected worksheet.
  AllowInsertingRows	Boolean	false	No	If set to true, allows users to insert rows on a protected worksheet.
  AllowInsertingHyperlinks	Boolean	false	No	If set to true, allows users to insert hyperlinks on a protected worksheet.
  AllowDeletingColumns	Boolean	false	No	If set to true, allows users to delete columns on a protected worksheet, provided that every cell in the columns to be deleted is unlocked.
  AllowDeletingRows	Boolean	false	No	If set to true, allows users to delete rows on a protected worksheet, provided that every cell in the rows to be deleted is unlocked.
  AllowSorting	Boolean	false	No	If set to true, allows users to sort on a protected worksheet. Every cell in the sort range must be unlocked or unprotected.
  AllowFiltering	Boolean	false	No	If set to true, allows users to set filters on a protected worksheet. Users can change filter criteria but cannot enable or disable an AutoFilter. Users can set filters on an existing AutoFilter.
  AllowUsingPivotTables	Boolean	false	No	If set to true, allows users to use pivot tables on a protected worksheet.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    activeSheet.Protect('123456');
  }

ActiveWorkbook.Sheets.Item(Index).Unprotect()

Removes protection from a specified worksheet. This method has no effect if the worksheet is not protected.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Unprotect({ Password })

  expression: An application object for the document type.

• Parameters

  Property	Data type	Required	Description
  Password	String	No	The user-defined password.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    activeSheet.Unprotect('123456');
  }

ActiveWorkbook.Sheets.Item(Index).Activate()

The Activate() method switches to a specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Activate()

  Or expression.Sheets(Index).Activate()

  expression: An application object for the document type.

• Parameters

  Property	Data type	Required	Description
  Index	Number	Yes	The index of the sheet.

• Examples

  • Example 1

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Make the specified worksheet the active worksheet.
      const sheetIndex = 2; // The ordinal number of the sheet, starting from 1.
      app.ActiveWorkbook.Sheets.Item(sheetIndex).Activate();
    }

  • Example 2

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Make the specified worksheet the active worksheet.
      const sheetIndex = 2; // The ordinal number of the sheet, starting from 1.
      app.Sheets(sheetIndex).Activate();
    }

ActiveWorkbook.Sheets.Item(Index).Move()

You can call the Move() method to move a worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Move({ Before, After })

  expression: An application object for the document type.

• Parameters

  Property	Data type	Required	Description
  Before	number	No	The ID of the worksheet before which the target worksheet is moved. You cannot specify Before if you specify After.
  After	number	No	The ID of the worksheet after which the target worksheet is moved. You cannot specify After if you specify Before.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    await app.ActiveWorkbook.Sheets(1).Move({
      Before: null,
      After: await app.ActiveWorkbook.Sheets(2).Id,
    })
  }

Properties

ActiveWorkbook.Sheets.Item(Index).Type

Returns the type of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Type

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // The Sheet object.
    const sheetIndex = 2; // The ordinal number of the sheet, starting from 1.
    const sheet = await app.ActiveWorkbook.Sheets.Item(sheetIndex)
  
    // The worksheet type.
    const Type = await sheet.Type;
    console.log(Type);
  }

ActiveWorkbook.Sheets.Item(Index).Columns

Returns all columns on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Columns

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    const chartObjects = await activeSheet.Columns;
  }

ActiveWorkbook.Sheets.Item(Index).Range

You can use the Range property to obtain a cell or a cell area.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Range

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // The Sheet object.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // A cell or a range of cells.
    const Range = await Sheet.Range;
  
    // Specify a cell.
    const range = await Range.Item("D2");
    // Or range = await Range("D2")
  
    // Select cell D2.
    await range.Select();
  }

ActiveWorkbook.Sheets.Item(Index).Cells

You can use the Cells property to retrieve all cells in a specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Cells

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // The Sheet object.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // The Cells object.
    const cells = await Sheet.Cells;
  
    // Specify a cell.
    const cell = await cells.Item(1);
    // Or cell = await cells(1)
  
    // Select the first cell.
    await cell.Select();
  }

ActiveWorkbook.Sheets.Item(Index).Name

Returns the name of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Name

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    const name = await app.ActiveWorkbook.Sheets.Item(1).Name;
    console.log(name);
  }

ActiveWorkbook.Sheets.Item(Index).Names

Returns all the worksheet-specific names.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Names

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    const names = await activeSheet.Names;
  }

ActiveWorkbook.Sheets.Item(Index).Rows

Returns all rows on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Rows

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // The Sheet object.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // The Rows object.
    const Rows = await Sheet.Rows;
  
    // Specify a row.
    const row = await Rows.Item(1);
    // Or row = await Rows(1)
  
    // Select the first row.
    await row.Select();
  }

ActiveWorkbook.Sheets.Item(Index).UsedRange

Returns the used range on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).UsedRange

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // The Sheet object.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // The used range on the worksheet.
    const UsedRange = await Sheet.UsedRange;
  
    // Select the used range.
    UsedRange.Select();
  }

ActiveWorkbook.Sheets.Item(Index).Index

Returns the index number of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Index

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const index = await app.ActiveWorkbook.Sheets.Item(1).Index;
    console.log(index);
  }

ActiveWorkbook.Sheets.Item(Index).Shapes

Returns all shape objects on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Shapes

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    const shapes = await activeSheet.Shapes;
  }

ActiveWorkbook.Sheets.Item(Index).Visible

Use the Visible property to set or retrieve the visibility of a specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Visible = Boolean

  expression: An application object for the document type.

  If Boolean is set to true, the specified worksheet is visible. If it is set to false, the worksheet is hidden.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const visible = await app.ActiveWorkbook.Sheets.Item(1).Visible;
    console.log(visible);
  
    app.ActiveWorkbook.Sheets.Item(1).Visible = false;
  }

ActiveWorkbook.Sheets.Item(Index).Id

Returns the ID of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Id

  expression: An application object for the document type.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const id = await app.ActiveWorkbook.Sheets.Item(1).Id;
    console.log(id);
  }