PxPlus COM Support

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")

Note:
If you are familiar with the Excel.Application extended object, all existing properties and methods will continue to be accessible by referencing the EXCEL property.

Example:

C=excel_obj'EXCEL'WORKBOOKS'Count

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)

Properties and Methods

The properties used by the Excel object are listed below.

Property

Description

ACTIVE_RANGE

(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:
Since it is not possible to know the active range on a newly-opened worksheet, it is good practice to either set the range (i.e. SetRange, SetRangeColumn or SetRangeRow) before assuming the default range on subsequent method calls or specify the range to use (cells$) in each individual method call.

ACTIVE_SHEET

(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.

ACTIVE_WORKBOOK

(Read Only) Handle to the active workbook object. This property corresponds to the EXCEL'ActiveWorkbook value. See SetWorkbook method.

CASE_SENSITIVE_SEARCH

Defaults to '0' for case insensitive searches. Set to '1' for case sensitive searches. See FindReplaceAll method.

DISPLAY_ALERTS

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:
This property cannot be set but is provided to allow access to all of the properties and methods in the Excel Application.

KEEP_VISIBLE

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.)

MATCH_ENTIRE_CELL

Defaults to '0' for partial matches within cell contents. Set to '1' to force matches on the entire cell contents. See FindReplaceAll method.

SEP$

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:

 

Workbook Methods

For creating, opening, setting, saving and closing a workbook

 

Worksheet Methods

For creating, printing, setting and hiding/showing a worksheet

 

Range Methods

For setting a range

 

Cell Values Methods

For finding, replacing, reading and writing cell values

 

Cell Formatting Methods

For setting text color, fill (background) color and font

 

Column Methods

For deleting and inserting columns

 

Row Methods

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:

   "A12" would specify a single cell.
   "A2:D5" would denote a group of cells from column A, line 2 to column D, line 5.

To use a previously named range, cells$ would be set to the name of the range itself (e.g. "header_row").

Workbook Methods

Workbook Methods

Description

CloseWorkbook( )

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:
Any unsaved changes in a workbook are not saved if the DISPLAY_ALERTS property is set to '0' (zero), the default value. To prompt the user about saving the changes, set DISPLAY_ALERTS to '1'. To ensure that all changes are saved and minimize user intervention, be sure to save the workbook prior to closing.

Returns '1' if successful.

Returns '0' if Excel was unable to locate or close the workbook.

CloseWorkbooks( )

Closes all open workbooks. This method is called automatically when the Excel object is dropped.

Note:
Any unsaved changes in the workbooks are not saved if the DISPLAY_ALERTS property is set to '0' (zero), the default value. To prompt the user about saving the changes, set DISPLAY_ALERTS to '1'. To ensure that all changes are saved and minimize user intervention, be sure to save the workbooks prior to closing.

Returns '1' if successful.

Returns '0' if Excel was unable to close the workbooks.

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:
In case of a pre-existing file named path$, set the overwrite_flag parameter to '1' to overwrite the file without displaying a message box to the user.

Returns '1' if successful.

Returns '0' if Excel was unable to add the workbook or if an existing workbook with the same name is already in use.

OpenWorkbook(path$)

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).

 

.csv

CSV (comma delimited)

.xlam

Excel Add-In

 

.dif

DIF Data Interchange Format

.xls

Excel 97 2003 Workbook

 

.htm, .html

Web Page

.xlsb

Excel Binary Workbook

 

.mht, .mhtml

Single File Web Page

.xlsm

Excel Macro-Enabled Workbook

 

.ods

OpenDocument Spreadsheet

.xlsx (or null)

Excel Workbook

 

.pdf

PDF

.xlt

Excel 97 2003 Template

 

.prn

Formatted Text (space delimited)

.xltm

Excel Macro-Enabled Template

 

.slk

SYLK (Symbolic Link)

.xltx

Excel Template

 

.txt.

Text (tab delimited)

.xml

XML Data

 

.xla

Excel 97 2003 Ad-In

.xps

XPS Document

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)

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:
Setting a workbook will also reset the ACTIVE_SHEET value to the value corresponding to the new worksheet.

Returns '1' if successful.

Returns '0' if Excel was unable to locate the workbook.

Worksheet Methods

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:
No printer dialog will be displayed. The worksheet will be printed to the default Excel printer with the default settings.

Returns '1' if successful.

Returns '0' if Excel was unable to locate the worksheet.

SetWorksheet(worksheet)

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

Range Methods

Description

SetRange(cells$)

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)

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:
An Excel column contains 1,048,576 rows. If this range is used to subsequently set a font, a text color or a fill color, only the first 16,385 cells in the range will be considered.

When no worksheet is specified, the range will be set to a column on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define a column range on a different worksheet.

Returns '1' if successful.

Returns '0' if Excel was unable to locate the worksheet or set the range.

SetRangeRow(row)

SetRangeRow(row,worksheet)

SetRangeRow(row,worksheet_name$)

Sets the active range object ACTIVE_RANGE to a particular row (row).

Note:
An Excel row includes 16,384 columns.

When no worksheet is specified, the range will be set to a row on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define a row range on a different worksheet.

Returns '1' if successful.

Returns '0' if Excel was unable to locate the worksheet or set the range.

AddNamedRange(name$,cells$)

AddNamedRange(name$,cells$,worksheet)

AddNamedRange(name,cells$,worksheet_name$)

Creates a named range with the specified name$ and range (cells$).

Note:
A range name (name$) cannot contain any spaces.

When no worksheet is specified, the named range will be created on the active worksheet. Pass either a worksheet index number or a simple worksheet name to define the named range on a different worksheet.

Once a named range is created, the name of the range (name$) may be used to refer to the range in any method where a range is specified (cells$).

Returns '1' if successful.

Returns '0' if Excel is not able to create the named range with the parameters provided.

(The AddNamedRange method was added in PxPlus 2018.)

DeleteNamedRange(name$)

DeleteNamedRange(name$,worksheet)

DeleteNamedRange(name$,worksheet_name$)

Deletes a previously created named range (name$).

Note:
A range name (name$) cannot contain any spaces.

When no worksheet is specified, the named range will be deleted from the active worksheet. Pass either a worksheet index number or a simple worksheet name to delete the named range from a different worksheet.

Returns '1' if successful.

Returns '0' if Excel is not able to delete the named range with the parameters provided.

(The DeleteNamedRange method was added in PxPlus 2018.)

Cell Values Methods

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:

   cell_values$ = "value one" + SEP + "value two" + SEP + "value three" + 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

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:
If the active range contains more than 16,385 cells (e.g. an entire column), the text color will only be set for the first 16,385 cells in the range.

The text color may be passed as one of the following:

     A color index (numeric)
     An RBG value in the format "RGB(rrr,ggg,bbb)" (string)
     One of the 16 basic PxPlus named colors such as 'Red', 'Dark Blue' or 'Light Cyan' (string)

(RGB values were added in PxPlus 2019.)

Note:
An Excel color index is an integer value ranging from 1 to 56.

Returns '1' if successful.

Returns '0' if Excel is unable to determine the worksheet or range.

(The capability to specify a range_name$ was added in PxPlus 2018.)

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:
If the active range contains more than 16,385 cells (e.g. an entire column), the fill color will only be set for the first 16,385 cells in the range.

The fill color may be passed as a color index (numeric), an RGB value in the format "RGB(rrr,ggg,bbb)" (string), or as one of the 16 basic PxPlus named colors such as 'red', 'dark blue' or 'light cyan' (string). (RGB values were added in PxPlus 2019.)

Note:
An Excel color index is an integer value ranging from 1 to 56.

Returns '1' if successful.

Returns '0' if Excel is unable to determine the worksheet or range.

(The capability to specify a range_name$ was added in PxPlus 2018.)

SetFont(font$)

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:
If the active range contains more than 16,385 cells (e.g. an entire column), the font will only be set for the first 16,385 cells in the range.

The font$ variable can pass font name, font size and font style (Bold, Italic, etc.), separated by commas.

Example:

font$
="Arial" sets only the font name.
font$="Arial,12" sets the font name and size.
font$="Arial,12, Italic" sets the font name and size and adds italics to the text in the range.
font$="Arial,12, Bold, Underline-" sets the font name and size, adds bolding and removes any underlining.
font$="Arial,12, Regular" sets the font name and size and removes any bolding, italics and underlining.

Note:
Valid fontstyle$ values are 'Regular', 'Bold+', 'Bold-', 'Bold' (same as 'Bold+'), 'Italic+', 'Italic-', 'Italic' (same as 'Italic+'), 'Underline+', 'Underline-', 'Underline' (same as 'Underline+') or any combination of these.

Returns '1' if successful.

Returns '0' if Excel is unable to determine the worksheet or range.

(The capability to specify a range_name$ was added in PxPlus 2018.)
(Support for removing bolding, italics and underlining was added in PxPlus 2021.)

Column Methods

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)

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

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.

Example Program

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")
!
! 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

See Also

PxPlus Word Object
Google Workspace Objects