<< 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 collection Application.Workbooks (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 parent 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:
none |
Return type:
none |
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:
none |
Return type:
none |
Example:
' Recalculate the first document in the Workbooks collection
pm.Workbooks(1).Calculate
Close (method)
Closes the document.
Syntax:
Close [SaveChanges]
Parameters:
SaveChanges (optional; type: Long or SmoSaveOptions) indicates whether the document should be saved or not. If you omit this parameter, the user will be asked – but only if the document was changed since it was last saved. The possible values for SaveChanges are: |
smoDoNotSaveChanges = 0 ' Don't ask, don't save
smoPromptToSaveChanges = 1 ' Ask the user
smoSaveChanges = 2 ' Save without asking
Return type:
none |
Example:
' Close the active document without saving
pm.ActiveWorkbook.Close smoDoNotSaveChanges
Save (method)
Saves the document.
Syntax:
Save
Parameters:
none |
Return type:
none |
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:
FileName (type: String): Path and file name under which the document should be saved. |
FileFormat (optional; type: Long or PmSaveFormat) determines the file format. This parameter can take the following values (left: the symbolic constants, right: the corresponding numeric values): |
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
If you omit this parameter, the value pmFormatDocument will be assumed. |
Delimiter (optional; type: String): Indicates the text delimiter (for text file formats), for example, comma or semicolon. If you omit this parameter, tabs will be used as a delimiter. |
TextMarker (optional; type: Long or PmImportTextMarker): Indicates the characters the individual text fields are enclosed with (for text file formats). The possible values are: |
pmImportTextMarkerNone = 0 ' No marker
pmImportTextMarkerApostrophe = 1 ' Apostrophe marks
pmImportTextMarkerQmark = 2 ' Quotation marks
Return type:
none |
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:
From (optional; type: Long) indicates from which page to start. If omitted, printing starts from the first page. |
To (optional; type: Long) indicates at which page to stop. If omitted, printing stops at the last page. |
Return type:
none |
Example:
' Print the current document
pm.ActiveWorkbook.PrintOut