<< Click to Display Table of Contents >> Workbook (object)

Access paths:

▪Application à Workbooks à Item

▪Application à ActiveWorkbook

▪Application à Windows à Item à Workbook

▪Application à ActiveWindow à Workbook

 1  Description

A Workbook object represents one individual document opened in PlanMaker.

For each document there is its own Workbook object. If you open or close documents, the respective Workbook objects will be created or deleted dynamically.

 2  Access to the object

The individual Workbook objects can be accessed in the following ways:

▪All open documents are managed in the Application.Workbooks collection (type: Workbooks):

' Show the names of all open documents

For i = 1 To pm.Application.Workbooks.Count

 MsgBox pm.Application.Workbooks.Item(i).Name

Next i

▪The active document can be accessed through Application.ActiveWorkbook:

' Show the name of the current document

MsgBox pm.Application.ActiveWorkbook.Name

▪Workbook is the Parent of the Sheets object, a collection of all worksheets in a document:

' Show the name of the current document in an indirect way

MsgBox pm.Application.ActiveWorkbook.Sheets.Parent.Name

▪The Window object includes an object pointer to the document that belongs to it:

' Access the active document through the active document window

MsgBox pm.Application.ActiveWindow.Workbook.Name

 3  Properties, objects, collections and methods

Properties:

▪Name R/O (default property)

▪FullName R/O

▪Path R/O

▪Saved

▪ReadOnly

▪EnableCaretMovement

▪ManualApply

▪ScreenUpdate

▪Calculation

▪CalculateBeforeSave

▪CalculateBeforeCopying

▪CalculateBeforePrinting

▪DisplayCommentIndicator

▪FixedDecimal

▪FixedDecimalPlaces

▪Iteration

▪MaxIteration

▪MaxChange

▪ShowGuideLinesForTextFrames

▪ShowHiddenObjects

▪RoundFinalResults

▪RoundIntermediateResults

 

Objects:

▪ActiveSheet → Sheet

▪ActiveWindow → Window

▪BuiltInDocumentProperties → DocumentProperties

▪Application → Application

▪Parent → Workbooks

 

Collections:

▪Sheets → Sheets

 

Methods:

▪Activate

▪Calculate

▪Close

▪Save

▪SaveAs

▪PrintOut

Name (property, R/O)

Data type: String

Returns the name of the document (e.g. "Smith.pmdx").

FullName (property, R/O)

Data type: String

Returns the path and name of the document (e.g., "c:\Documents\Smith.pmdx").

Path (property, R/O)

Data type: String

Returns the path of the document (e.g. "c:\Documents").

Saved (property)

Data type: Boolean

Gets or sets the Saved property of the document. It indicates whether a document was changed since it was last saved:

▪If Saved is set to True, the document was not changed since it was last saved.

▪If Saved is set to False, the document was changed since it was last saved. When closing the document, the user will be asked if it should be saved.

ReadOnly (property)

Data type: Boolean

Gets or sets the ReadOnly property of the document.

If the property is True, the document is protected against user changes. Users will not be able to edit, delete, or add content.

If you set this property to True, the EnableCaretMovement property (see there) will be automatically set to False. Therefore, the text cursor cannot be moved inside the document anymore. However, you can always set the EnableCaretMovement property to True if you want to make cursor movement possible.

EnableCaretMovement (property)

Data type: Boolean

Gets or sets the EnableCaretMovement property of the document. This property is sensible only in combination with the ReadOnly property (see there).

If EnableCaretMovement is True, the text cursor can be moved freely inside a write-protected document. If it is set to False, cursor movement is not possible.

ManualApply (property)

Data type: Boolean

Gets or sets the setting whether formatting changes made by your Basic script should be applied instantly or not.

By default, this property is set to False, causing formatting commands like Range.Font.Size = 12 to be applied instantly.

If you would like to apply a large number of formattings, you can set the ManualApply property to True. In this case, PlanMaker accumulates all formatting commands until you invoke the Range.ApplyFormatting method (see there). This leads to a speed advantage.

ScreenUpdate (property)

Data type: Boolean

Gets or sets the setting whether PlanMaker should update the display after each change.

If you set this property to False and then change the contents or formatting of cells, these changes will not be shown on the screen until you set the property to True again. This can have a speed advantage if you change many cells at once.

Calculation (property)

Data type: Long (PmCalculation)

Gets or sets the setting whether the document should be recalculated automatically or manually. The possible values are:

pmCalculationAutomatic   = 0 ' Update calculations automatically

pmCalculationManual      = 1 ' Update calculations manually

CalculateBeforeSave (property)

Data type: Boolean

Gets or sets the setting whether the document should be recalculated when it is saved.

This property has an effect only if the document is set to be recalculated manually. If the Calculation property (see there) is set to pmCalculationAutomatic, all calculations will always be kept up-to-date anyway.

CalculateBeforeCopying (property)

Data type: Boolean

Gets or sets the setting whether the document should be recalculated before copying or cutting cells.

This property has an effect only if the document is set to be recalculated manually. If the Calculation property (see there) is set to pmCalculationAutomatic, all calculations will always be kept up-to-date anyway.

CalculateBeforePrinting (property)

Data type: Boolean

Gets or sets the setting whether the document should be recalculated before printing.

This property has an effect only if the document is set to be recalculated manually. If the Calculation property (see there) is set to pmCalculationAutomatic, all calculations will always be kept up-to-date anyway.

DisplayCommentIndicator (property)

Data type: Long (PmCommentDisplayMode)

Gets or sets the mode in which comments are shown. The possible values are:

pmNoIndicator          = 0 ' Show neither comments nor yellow triangle

pmCommentIndicatorOnly = 1 ' Show only a yellow triangle

pmCommentOnly          = 2 ' Show comments, but no yellow triangle

pmCommentAndIndicator  = 3 ' Show both comments and triangle

FixedDecimal (property)

Data type: Boolean

Gets or sets the setting whether the decimal separator should be automatically shifted after the input of numbers.

The number of positions to shift the decimal separator is specified by the FixedDecimalPlaces property (see there).

Example:

' Move the decimal separator 2 positions to the left after input

pm.ActiveWorkbook.FixedDecimal = True

pm.ActiveWorkbook.FixedDecimalPlaces = 2 ' 4235 will become 42.35

 

' Move the decimal separator 2 positions to the right after input

pm.ActiveWorkbook.FixedDecimal = True

pm.ActiveWorkbook.FixedDecimalPlaces = -2 ' 42 will become 4200

FixedDecimalPlaces (property)

Data type: Boolean

Gets or sets the number of positions to shift the decimal separator after the input of the numbers.

Note: This has no effect unless the FixedDecimal property (see there) is set to True.

Iteration (property)

Data type: Boolean

Gets or sets the setting "Use iterations" on the Calculate tab in the dialog box of the ribbon command File | Properties.

If you enable this property, you should also specify values for the MaxChange and MaxIteration properties (see there).

MaxIteration (property)

Data type: Long

Gets or sets the setting "Maximum iterations" on the Calculate tab in the dialog box of the ribbon command File | Properties. This only has an effect if the Iteration property (see there) is set to True.

MaxChange (property)

Data type: Long

Gets or sets the setting "Maximum change" (in iterations) on the Calculate tab in the dialog box of the ribbon command File | Properties. This only has an effect if the Iteration property (see there) is set to True.

ShowGuideLinesForTextFrames (property)

Data type: Boolean

Gets or sets the setting "Guidelines for text frames" on the Options tab in the dialog box of the ribbon command File | Properties.

ShowHiddenObjects (property)

Data type: Boolean

Gets or sets the setting "Show hidden objects" on the Options tab in the dialog box of the ribbon command File | Properties.

RoundFinalResults (property)

Data type: Boolean

Gets or sets the setting "Round final result" on the Calculate tab in the dialog box of the ribbon command File | Properties.

RoundIntermediateResults (property)

Data type: Boolean

Gets or sets the setting "Round intermediate results" on the Calculate tab in the dialog box of the ribbon command File | Properties.

ActiveSheet (pointer to object)

Data type: Object

Returns the currently active Sheet object that you can use to access the active worksheet.

ActiveWindow (pointer to object)

Data type: Object

Returns the currently active Window object that you can use to access the active document window.

BuiltInDocumentProperties (pointer to object)

Data type: Object

Returns the DocumentProperties collection that you can use to access the document infos (title, subject, author, etc.).

Application (pointer to object)

Data type: Object

Returns the Application object.

Parent (pointer to object)

Data type: Object

Returns the superordinate object, i.e. Workbooks.

Sheets (pointer to collection)

Data type: Object

Returns the Sheets collection, a collection of all worksheets in the document.

Activate (method)

Brings the document window to the front (if Visible is True for the document) and sets the focus to the document window.

Syntax:

 Activate

Parameters:

Return type:

Example:

' Bring the first document in the Workbooks collection to the front

pm.Workbooks(1).Activate

Calculate (method)

Recalculates the document (corresponds to the ribbon command Formulas | Update group | Update data | Update calculations in PlanMaker).

Syntax:

 Calculate

Parameters:

Return type:

Example:

' Recalculate the first document in the Workbooks collection

pm.Workbooks(1).Calculate

Close (method)

Closes the document.

Syntax:

 Close [SaveChanges]

Parameters:

 smoDoNotSaveChanges = 0      ' Don't ask, don't save

 smoPromptToSaveChanges = 1   ' Ask the user

 smoSaveChanges = 2           ' Save without asking

Return type:

Example:

' Close the active document without saving

pm.ActiveWorkbook.Close smoDoNotSaveChanges

Save (method)

Saves the document.

Syntax:

 Save

Parameters:

Return type:

Example:

' Save the active document

pm.ActiveWorkbook.Save

SaveAs (method)

Saves the document under a different name and/or path.

Syntax:

 SaveAs FileName, [FileFormat], [Delimiter], [TextMarker]

Parameters:

 pmFormatDocument         =  0 ' PlanMaker document

 pmFormatTemplate         =  1 ' PlanMaker document template

 pmFormatExcel97          =  2 ' Excel 97/2000/XP

 pmFormatExcel5           =  3 ' Excel 5.0/7.0

 pmFormatExcelTemplate    =  4 ' Excel document template

 pmFormatSYLK             =  5 ' Sylk

 pmFormatRTF              =  6 ' Rich Text Format

 pmFormatTextMaker        =  7 ' TextMaker (= RTF)

 pmFormatHTML             =  8 ' HTML document

 pmFormatdBaseDOS         =  9 ' dBASE database with DOS character set

 pmFormatdBaseAnsi        = 10 ' dBASE database with Windows character set

 pmFormatDIF              = 11 ' Text file with Windows character set

 pmFormatPlainTextAnsi    = 12 ' Text file with Windows character set

 pmFormatPlainTextDOS     = 13 ' Text file with DOS character set

 pmFormatPlainTextUnix    = 14 ' Text file with ANSI character set for UNIX, Linux, FreeBSD

 pmFormatPlainTextUnicode = 15 ' Text file with Unicode character set

 pmFormatdBaseUnicode     = 18 ' dBASE database with Unicode character set

 pmFormatPlainTextUTF8    = 21 ' Text file with UTF8 character set

 pmFormatMSXML            = 23 ' Excel 2007 and later

 pmFormatPM2008           = 26 ' PlanMaker 2008 document

 pmFormatPM2010           = 27 ' PlanMaker 2010 document

 pmFormatPM2012           = 28 ' PlanMaker 2012 document

 pmFormatPM2012Template   = 29 ' PlanMaker 2012 document template

 pmImportTextMarkerNone       = 0 ' No marker

 pmImportTextMarkerApostrophe = 1 ' Apostrophe marks

 pmImportTextMarkerQmark      = 2 ' Quotation marks

Return type:

Example:

' Save the current document under a new name in Excel 97 format

pm.ActiveWorkbook.SaveAs "c:\docs\test.xls", pmFormatExcel97

PrintOut (method)

Prints the document.

Syntax:

 PrintOut [From], [To]

Parameters:

Return type:

Example:

' Print the current document

pm.ActiveWorkbook.PrintOut