|
PxPlus Excel Object |
The PxPlus Excel object simplifies the use of the Excel.Application extended object. This object consists of various properties and methods that make it easier to interact with Excel workbooks and worksheets. See Properties and Methods below.
When a new Excel file is created, a workbook is created with a given number of worksheets (Sheet1, Sheet2, Sheet3, etc.), depending on your default template. The Excel object allows more than one workbook to be opened at one time, each containing various worksheets.
(The PxPlus Excel object was added in PxPlus 2017.)
Instantiating the Excel Object
To instantiate the Excel object using the handle excel_obj (where excel_obj could be any numeric variable), enter the following command:
excel_obj=NEW("*obj/excel")
To access any of the available properties and/or methods, the Excel object handle (excel_obj) is used, followed by an ' (apostrophe) and the property or method (with the desired parameters).
Examples:
cw=excel_obj'ACTIVE_SHEET
retVal=excel_obj'OpenWorkbook("C:/Application/Spreadsheet Location/test.xlsx")
When an Error is Encountered
In case of method failure, the ERROR object can be used to get additional information about the last error encountered.
Example:
If Excel is unable to open the workbook in the previous example because the "C:/Application/Spreadsheet Location/test.xlsx" file does not exist, retVal will be equal to '0'. Checking the Description$ property of the ERROR object may also provide more information:
PRINT excel_obj'ERROR'Description$
'C:\Application/Spreadsheet Location/test.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved or deleted.
Global Named Constants
The Excel Application makes use of hundreds of global named constants. If required, these constants may be accessed by using the PVXCONSTANTS object, which has been defined as a property called ECONSTANTS. See *CONSTANTS.
Example:
PRINT excel_obj'ECONSTANTS'xlNoChanges (should return 4)
The properties used by the Excel object are listed below.
|
Property |
Description |
|
(Read Only) Handle to the active range object (selected cells) for the active worksheet. Used in conjunction with setting fonts/colors, reading and writing when not specifying a range. This property corresponds to the EXCEL'Selection value and defaults to the selected range when the worksheet was last saved (regardless of whether the save was done using this Excel Object or from within Excel itself). See SetRange Methods. Note: | |
|
(Read Only) Handle to the active worksheet object. This is the active worksheet on the active workbook and corresponds to the EXCEL'ActiveSheet value. Defaults to the active worksheet when the workbook was last saved. See SetWorksheet method. | |
|
(Read Only) Handle to the active workbook object. This property corresponds to the EXCEL'ActiveWorkbook value. See SetWorkbook method. | |
|
Defaults to '0' for case insensitive searches. Set to '1' for case sensitive searches. See FindReplaceAll method. | |
|
Allows suppression of Excel message boxes. Defaults to Off ('0'). | |
|
ECONSTANTS |
(Read Only) Handle to the Excel CONSTANTS object. |
|
ERROR |
(Read Only) Handle to the Excel ERROR object. Can be used to display the last error message via the Description$ property. |
|
EXCEL |
(Read Only) Handle to the main Excel object. Note: |
|
Set to a non-zero value to keep the Excel application active and visible when the PxPlus Excel Object is dropped. Has no effect if the VISIBLE property has not been set. Defaults to Off ("0"). (The KEEP_VISIBLE property was added in PxPlus 2020.) | |
|
LAST_COLUMN |
(Read Only) Column number of the last column of data in the active spreadsheet (ACTIVE_SHEET). (The LAST_COLUMN property was added in PxPlus 2019.) |
|
LAST_ROW |
(Read Only) Row number of the last row of data in the active spreadsheet (ACTIVE_SHEET). (The LAST_ROW property was added in PxPlus 2019.) |
|
Defaults to '0' for partial matches within cell contents. Set to '1' to force matches on the entire cell contents. See FindReplaceAll method. | |
|
Delimiter used on various Read$ and Write methods to separate individual cell values. Defaults to the standard PxPlus field delimiter SEP ($8A$). See Cell Values Methods. (The SEP$ property was added in PxPlus 2018.) | |
|
VISIBLE |
Allows the Excel application to be visible. Defaults to Off ('0'). |
|
WORKBOOKS_COUNT |
(Read Only) Number of workbooks opened. This property corresponds to the EXCEL'WORKBOOKS'Count value. |
The methods used by the Excel object are listed below. To make it easier to locate information for a particular method, this list has been broken into the following smaller groups:
|
|
For creating, opening, setting, saving and closing a workbook | |
|
|
For creating, printing, setting and hiding/showing a worksheet | |
|
|
For setting a range | |
|
|
For finding, replacing, reading and writing cell values | |
|
|
For setting text color, fill (background) color, font, and number format | |
|
|
For setting a numeric format | |
|
|
For deleting and inserting columns | |
|
|
For deleting and inserting rows |
Common Parameters
The following is a list of some of the common parameters used by multiple methods of the Excel object:
|
Parameter |
Description |
|
workbook |
1-based index number of the Excel workbook. |
|
workbook_name$ |
Simple workbook name without the full path (e.g. "test_workbook.xlsx"). |
|
path$ or workbook_path$ |
Full path name for the workbook (e.g. "c:/application/test_workbook.xlsx"). |
|
cells$ |
Range of cells to be considered. Example: To use a previously named range, cells$ would be set to the name of the range itself (e.g. "header_row"). |
|
Workbook Methods |
Description | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
CloseWorkbook(workbook) CloseWorkbook(workbook_name$) |
Closes the workbook specified by either an index number or a simple workbook name. If no workbook is specified, the active workbook will be closed. Note:
| ||||||||||||||||||||||||||||||||||||||||||||||||||
|
CloseWorkbooks( ) |
Closes all open workbooks. This method is called automatically when the Excel object is dropped. Note:
| ||||||||||||||||||||||||||||||||||||||||||||||||||
|
CreateWorkbook(path$) CreateWorkbook(path$,overwrite_flag) |
Creates a new Excel workbook (path$) according to the default Excel template. The path$ parameter may be either a simple or full pathname. The new workbook is opened and becomes the ACTIVE_WORKBOOK. ACTIVE_SHEET will default to the first worksheet, and ACTIVE_RANGE will default to the first cell. If the path$ parameter is null, the workbook is named according to the default Excel template (e.g. Book1.xlsx). It is not advisable to use a null name when running multiple sessions on the same machine as only one user will be able to open the default template file at the same time. Note:
| ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Opens the Excel workbook located at pathname path$. After opening a workbook, the ACTIVE_WORKBOOK property is set to the workbook object corresponding to the workbook just opened. The ACTIVE_SHEET property is set according to the value when the workbook was last saved. Returns '1' if successful. Returns '0' if the path specified was invalid or if Excel was unable to open the workbook. | |||||||||||||||||||||||||||||||||||||||||||||||||||
|
SaveAsWorkbook(workbook_path$) SaveAsWorkbook(workbook_path$,workbook) SaveAsWorkbook(workbook_path$,workbook_name$) |
Used to save a workbook to a different location or with a different file format. Saves the workbook specified (by either an index number or a simple workbook name) with the pathname provided. If no workbook is specified, the active workbook is saved. If a full pathname is not provided, the workbook will be saved in the default Excel folder. The format used to save the workbook is determined by the extension specified in the workbook_path$. Supported extensions are listed below (in alphabetical order).
Returns '1' if successful. Returns '0' if the pathname is invalid, already exists, or if Excel was unable to save the workbook. (Support for other file formats was added in PxPlus 2018.) | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
SaveWorkbook( ) SaveWorkbook(workbook) SaveWorkbook(workbook_name$) |
Saves the workbook specified by either an index number or a simple workbook name. If no workbook is specified, the active workbook is saved. Returns '1' if successful. Returns '0' if Excel was unable to locate or save the workbook. | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
SaveWorkbooks( ) |
Saves the values in all currently opened workbooks. Returns '1' if successful. Returns '0' if Excel was unable to save any of the workbooks. Returns '-1' if Excel was only able to save some of the workbooks successfully. | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
SetWorkbook(workbook_name$) |
Sets the active workbook object ACTIVE_WORKBOOK to the workbook specified by either an index number or a simple workbook name (e.g. "1234.xlsx"). Note:
|
|
Worksheet Methods |
Description |
|
CreateWorksheet(worksheet_name$) CreateWorksheet(worksheet_name$,tab_index) |
Creates a new worksheet (worksheet_name$) in the ACTIVE_WORKBOOK. If no tab_index is specified, the new worksheet will be inserted before the ACTIVE_SHEET. If a tab_index is specified, the worksheet will be inserted as the worksheet index specified. Specifying a tab_index higher than the current number of worksheets will create the new worksheet as the last worksheet. If the worksheet_name$ parameter is null, the default worksheet name will be used (i.e. 'SheetX' where X is an incremental number to avoid worksheet name conflicts). Returns '1' if successful. Returns '0' if Excel was unable to add the worksheet. (The CreateWorksheet(worksheet_name$,tab_index) method was added in PxPlus 2018.) |
|
PrintWorksheet( ) PrintWorksheet(worksheet) PrintWorksheet(worksheet_name$) |
Prints the worksheet specified by either an index number or a simple worksheet name. If no worksheet is specified, the active worksheet is printed. Note:
|
|
SetWorksheet(worksheet_name$) |
Sets the active worksheet object ACTIVE_SHEET to the worksheet specified by either an index number or a simple worksheet name (e.g. "Sheet1"). Returns '1' if successful. Returns '0' if Excel was unable to locate the worksheet. Returns '-1' if the worksheet exists but is hidden. (Support for hiding the worksheet was added in PxPlus 2021 Update 1.) |
|
WorksheetHide(worksheet) WorksheetHide(worksheet_name$) |
Hides the specified worksheet, which the user can make visible via menu. Returns '1' if successful. Returns '0' if Excel was unable to locate the worksheet. (The WorksheetHide method was added in PxPlus 2021 Update 1.) |
|
WorksheetVeryHide(worksheet) WorksheetVeryHide(worksheet_name$) |
Hides the specified worksheet so that the user cannot make the object visible via menu. Returns '1' if successful. Returns '0' if Excel was unable to locate the worksheet. (The WorksheetVeryHide method was added in PxPlus 2021 Update 1.) |
|
WorksheetVisible(worksheet) WorksheetVisible(worksheet_name$) |
Makes the specified worksheet visible (not hidden). Returns '1' if successful. Returns '0' if Excel was unable to locate the worksheet. (The WorksheetVisible method was added in PxPlus 2021 Update 1.) |
|
Range Methods |
Description |
|
SetRange(cells$,worksheet) SetRange(cells$,worksheet_name$) |
Sets the active range object ACTIVE_RANGE. When no worksheet is specified, the range will be set on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define a range on a different worksheet. Specify the range in the cells$ variable (e.g. "A12" or "A2:D5" or range_name$). Returns '1' if successful. Returns '0' if Excel was unable to locate the worksheet or set the range. (The capability to specify a range_name$ was added in PxPlus 2018.) |
|
SetRangeColumn(col,worksheet) SetRangeColumn(col,worksheet_name$) SetRangeColumn(col$) SetRangeColumn(col$,worksheet) SetRangeColumn(col$,worksheet_name$) |
Sets the active range object ACTIVE_RANGE to a particular column number (col) or letter (col$). Note:
|
|
SetRangeRow(row,worksheet) SetRangeRow(row,worksheet_name$) |
Sets the active range object ACTIVE_RANGE to a particular row (row). Note:
|
|
AddNamedRange(name$,cells$,worksheet) AddNamedRange(name,cells$,worksheet_name$) |
Creates a named range with the specified name$ and range (cells$). Note:
|
|
DeleteNamedRange(name$,worksheet) DeleteNamedRange(name$,worksheet_name$) |
Deletes a previously created named range (name$). Note:
|
|
Cell Values Methods |
Description |
|
FindReplaceAll(find$,replace$) FindReplaceAll(find$,replace$,worksheet) FindReplaceAll(find$,replace$,worksheet_name$) |
Finds cells with the value find$ and replaces all occurrences with the string replace$. When only the find$ and replace$ parameters are passed, the Find/Replace is performed on the active worksheet. Pass either a worksheet index number or a simple worksheet name to perform the Find/Replace on a worksheet other than the active worksheet without changing the ACTIVE_SHEET property. To perform the Find/Replace on all opened worksheets, pass a worksheet_name$ of 'ALL' (case insensitive). Searches will be case insensitive by default. For case sensitive searches, set the CASE_SENSITIVE_SEARCH property to '1'. By default, the find$ value will be replaced if it is located anywhere within a cell value. Set the MATCH_ENTIRE_CELL property to '1' to replace text only if the entire cell contents is equal to the find$ value. Returns '1' if successful. Returns '0' if Excel was unable to find/replace on any of the worksheets specified or if the find$ value was not found. When finding/replacing on all worksheets: Returns '-1' if Excel was only able to perform find/replace on some of the worksheets successfully. |
|
Read$( ) Read$(cells$) Read$(cells$,worksheet) Read$(cells$,worksheet_name$) |
Reads data from a worksheet. When no worksheet is specified, the data will be read from the active worksheet. Pass either a worksheet index number or a simple worksheet name to read data from a different worksheet without changing the ACTIVE_SHEET property. When no range is specified in cells$, data is read from the active range in the active worksheet. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE property. When the cells$ value corresponds to a single cell in the worksheet, a string value is returned. When the cells$ value corresponds to a range of 2 or more cells, a SEP$ delimited string is returned. If a problem is encountered, a null string is returned. (The capability to specify a range_name$ was added in PxPlus 2018.) |
|
Write(cell_values$) Write(cell_values$,cells$) Write(cell_values$,cells$,worksheet) Write(cell_values$,cells$,worksheet_name$) |
Writes data to a spreadsheet. When no worksheet is specified, the data will be written to the active worksheet. Pass either a worksheet index number or a simple worksheet name to write data to a different worksheet without changing the ACTIVE_SHEET property. When no range is specified in cells$, data is written to the active range in the active worksheet. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE property. When writing data to a single cell, cell_values$ should contain a string value. When writing to a range of cells, cell_values$ should contain a string value delimited with a delimiter corresponding to the current value in the SEP$ property. Example: When SEP$=SEP: Returns '0' if the data was not written. Returns '1' if the data was written successfully and the number of specified cell values (cell_values$) matched the number of cells in the specified range (cells$). Returns '-1' if the data was written but the number of specified cell values (cell_values$) did not match the number of cells in the specified range (cells$). In this case, one of the following instances can occur: • If the number of cell values (cell_values$) is greater than the number of cells in the specified range (cells$), the remaining cell values not written to a cell will be ignored. • If the number of cell values (cell_values$) is fewer than the number of cells in the specified range (cells$), null values will be written into the remaining cells in that range to which no cell values were assigned. (The capability to specify a range_name$ was added in PxPlus 2018.) |
|
Cell Formatting Methods |
Description |
|
SetColor(color_index) SetColor(color$) SetColor(color_index,cells$) SetColor(color$,cells$) SetColor(color_index,cells$,worksheet) SetColor(color$,cells$,worksheet) SetColor(color_index,cells$,worksheet_name$) SetColor(color$,cells$,worksheet_name$) |
Sets the text color for a range of cells in a given worksheet. When no worksheet is specified, the text color will be set on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define the text color on a different worksheet without changing the ACTIVE_SHEET property. When no range is specified in cells$, the text color will be set in the active range of the specified worksheet. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE property. Note:
|
|
SetFillColor(color_index) SetFillColor(color$) SetFillColor(color_index,cells$) SetFillColor(color$,cells$) SetFillColor(color_index,cells$,worksheet) SetFillColor(color$,cells$,worksheet) SetFillColor(color_index,cells$,worksheet_name$) SetFillColor(color$,cells$,worksheet_name$) |
Sets the fill (background) color for a range of cells in a given worksheet. When no worksheet is specified, the fill color will be set on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define the fill color on a different worksheet without changing the ACTIVE_SHEET property. When no range is specified in cells$, the fill color will be set in the active range of the specified worksheet. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE property. Note:
|
|
SetFont(font$,cells$) SetFont(font$,cells$,worksheet) SetFont(font$,cells$,worksheet_name$) |
Sets font information (font name, font size, font style) for a range of cells in a given worksheet. When no worksheet is specified, the font will be set on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define the font on a different worksheet without changing the ACTIVE_SHEET property. When no range is specified in cells$, the font will be set in the active range of the specified worksheet. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE property. Note:
|
|
Number Formatting Methods |
Description | ||||||||||||||||||||||||||||
|
SetNumFormat(format$,cells$) SetNumFormat(format$,cells$,worksheet) SetNumFormat(format$,cells$,worksheet_name$) |
Sets a numeric format for a specified range of the worksheet specified by either a worksheet index number or a simple worksheet name. If no worksheet is specified, the active worksheet is used. If no range is specified (cells$), the active range is used. The format$ can be defined as a custom value or as a value corresponding to the following standard formats available in Excel:
Returns '1' if successful. Returns '0' if Excel is unable to locate the specified worksheet or range. (The SetNumFormat method was added in PxPlus 2023.) |
|
Column Methods |
Description |
|
DeleteColumn(col) DeleteColumn(col,worksheet) DeleteColumn(col,worksheet_name$) DeleteColumn(col$) DeleteColumn(col$,worksheet) DeleteColumn(col$,worksheet_name$) |
Deletes column number col (or column letter col$) in the worksheet specified by either a worksheet index number or a simple worksheet name. If no worksheet is specified, the column is deleted from the active worksheet. Returns '1' if successful. Returns '0' if Excel is unable to locate the worksheet. |
|
InsertColumn(col) InsertColumn(col,worksheet) InsertColumn(col,worksheet_name$) InsertColumn(col$) InsertColumn(col$,worksheet) InsertColumn(col$,worksheet_name$) |
Inserts a new blank column number col (or column letter col$) in the worksheet specified by either a worksheet index number or a simple worksheet name. If no worksheet is specified, the column is inserted into the active worksheet. Returns '1' if successful. Returns '0' if Excel is unable to locate the worksheet. |
|
SetColumnWidth(width,cells$) SetColumnWidth(width,cells$,worksheet) SetColumnWidth(width,cells$,worksheet_name$) |
Sets the width of all columns in a specified range of the worksheet specified by either a worksheet index number or a simple worksheet name. If no worksheet is specified, the active worksheet is used. If no range is specified (cells$), the active range is used. Returns '1' if successful. Returns '0' if Excel is unable to locate the specified worksheet or range. (The SetColumnWidth method was added in PxPlus 2018.) |
|
Row Methods |
Description |
|
DeleteRow(row) DeleteRow(row,worksheet) DeleteRow(row,worksheet_name$) |
Deletes row number row in the worksheet specified by either a worksheet index number or a simple worksheet name. If no worksheet is specified, the row is deleted from the active worksheet. Returns '1' if successful. Returns '0' if Excel is unable to locate the worksheet. |
|
InsertRow(row) InsertRow(row,worksheet) InsertRow(row,worksheet_name$) |
Inserts a new blank row number row in the worksheet specified by either a worksheet index number or a simple worksheet name. If no worksheet is specified, the row is inserted into the active worksheet. Returns '1' if successful. Returns '0' if Excel is unable to locate the worksheet. |
An Example program is provided below.
! Test excel object
!
! instantiate object
x=NEW("*obj/excel")
!
! Open workbook
path$="c:\spreadsheets\2759.xlsx"
wb=x'OpenWorkbook(path$)
!
! Open another workbook
Path2$="c:\spreadsheets\another.xlsx"
wb2=x'OpenWorkbook(path2$)
!
! Set Worksheet by name
ws=x'SetWorksheet("sheet1")
!
! Read a value in cell C2 of active worksheet
c$=x'read$("C2")
!
! Write a value to cell C3 in active worksheet
retVal=x'write("*THIS HAS BEEN CHANGED!*","C3")
!
! Read row 3 in worksheet 2
r=x'SetRange("A3:E3",2)
range$=x'read$()
!
! Write to row 3 (active range in worksheet 2)
new_range$="*Column 1*"+SEP+"*Column 2*"+SEP+"*Column3*"+SEP+"*Column 4*"+SEP+"*Column 5*"+SEP
retVal=x'write(new_range$)
!
! Read column 4 (D) in worksheet 1
row$=x'read$("D1:D10",1)
!
! Switch back to first workbook
retVal=x'SetWorkbook(1)
!
! Set a range
r=x'SetRange("A1:D4")
!
! Set Font for active range and worksheet
retVal=x'SetFont("Arial,16,Bold")
!
! Create a 'named' range for the first row
retVal=x'AddNamedRange("Headers","A1:Z1")
!
! Set active range text colour to red
retVal=x'SetColor(3)
!
! Insert a row #5 in active worksheet
retVal=x'InsertRow(5)
!
! Set the fill color to yellow for a different range in the third worksheet
retVal=x'SetFillColor("light yellow","B3:F8",3)
!
! Set the text for column "C" to blue, Courier New
r=x'SetRangeColumn("C")
ret1=x'SetColor(5)
ret2=x'SetFont("Courier New")
!
! Set the numeric format for cell G9 to percentage format
r=x'SetNumFormat("percentage","G9")
!
! Set the numeric format for cell M7 to custom date format
r=x'SetNumFormat("dd/mm/yyyy","M7")
!
! Save workbook named "another.xlsx"
retVal=x'SaveWorkbook("another.xlsx")
!
! Save current workbook as a web page
retVal=x'SaveAsWorkbook(path$+".htm")
!
! Save changes for all workbooks
retVal=x'SaveWorkbooks()
!
! Close workbook 1
retVal=x'CloseWorkbook(1)
!
! Drop object
DROP OBJECT x
END
PxPlus Word Object
Google Workspace Objects