Column Excel Workbook Info

[lxp]

This add-in creates columns showing the number of sheets, worksheets, and charts in Excel files.

function OnInit(initData) {
    initData.name = 'ExcelWorkbook';
    initData.version = '2022-06-29';
    initData.url = 'https://resource.dopus.com/t/column-excel-workbook-info/41595';
    initData.desc = 'Display Info about Exel Workbooks in columns';
    initData.default_enable = true;
    initData.min_version = '12.0';
}

function OnAddColumns(addColData) {
    var col = addColData.AddColumn();
    col.name = 'SheetsCount';
    col.label = 'SheetsCount';
    col.header = 'SheetsCount';
    col.justify = 'left';
    col.multicol = true;
    col.autogroup = true;
    col.type = 'number';
    col.method = 'OnColumn';
    
    var col = addColData.AddColumn();
    col.name = 'WorksheetsCount';
    col.label = 'WorksheetsCount';
    col.header = 'WorksheetsCount';
    col.justify = 'left';
    col.multicol = true;
    col.autogroup = true;
    col.type = 'number';
    col.method = 'OnColumn';

    var col = addColData.AddColumn();
    col.name = 'ChartsCount';
    col.label = 'ChartsCount';
    col.header = 'ChartsCount';
    col.justify = 'left';
    col.multicol = true;
    col.autogroup = true;
    col.type = 'number';
    col.method = 'OnColumn';
}

var excelApp = new ActiveXObject('Excel.Application');

function OnColumn(scriptColData) {
    if (excelApp == null) return;

    var item = scriptColData.item;
    if (item.is_dir) return;
    if (item.ext.substring(0, 3) != '.xl') return;
    if (item.name_stem.substring(0, 1) == '~') return;

    var wb = excelApp.Workbooks.Open(String(item.realpath));

    scriptColData.columns('SheetsCount').value = wb.Sheets.Count;
    scriptColData.columns('WorksheetsCount').value = wb.Worksheets.Count;
    scriptColData.columns('ChartsCount').value = wb.Charts.Count;

    wb.Close();
}

To toggle the columns, use

Set COLUMNSTOGGLE="scp:ExcelWorkbook/SheetsCount(!,a,0)"
Set COLUMNSTOGGLE="scp:ExcelWorkbook/WorksheetsCount(!,a,0)"
Set COLUMNSTOGGLE="scp:ExcelWorkbook/ChartsCount(!,a,0)"

Button as XML

<?xml version="1.0"?>
<button backcol="none" display="both" label_pos="right" textcol="none">
	<label>Toggle ExcelWorkbook</label>
	<icon1>#office</icon1>
	<function type="normal">
		<instruction>Set COLUMNSTOGGLE=&quot;scp:ExcelWorkbook/SheetsCount(!,a,0)&quot;</instruction>
		<instruction>Set COLUMNSTOGGLE=&quot;scp:ExcelWorkbook/WorksheetsCount(!,a,0)&quot;</instruction>
		<instruction>Set COLUMNSTOGGLE=&quot;scp:ExcelWorkbook/ChartsCount(!,a,0)&quot;</instruction>
	</function>
</button>

ColumnExcelWorkbook.js.txt (1.8 KB)

---

How to use buttons and scripts from this forum

This post explains how to add buttons to toolbars and menus, and also applies to raw commands for things like standalone hotkeys. For how to use Script Add-Ins, please scroll down to see the post below this one. Much of this assumes Directory Opus…

[Micky]

@lxp

Set COLUMNSTOGGLE="scp:ExcelWorkbook/SheetsCount(!,a,0)"
Set COLUMNSTOGGLE="scp:ExcelWorkbook/WorksheetsCount(!,a,0)"
Set COLUMNSTOGGLE="scp:ExcelWorkbook/ChartsCount(!,a,0)"

From a programmers view, I would be interested how these lines trigger anything in the above JS? I guess those lines are not triggering something but rather filter for newly defined columns in the JS, right?

So, IMO, scp: is an abbreviation for script, ExcelWorkbook is related to the line initData.name = 'ExcelWorkbook'; and SheetsCount, WorksheetsCount, ChartsCountto the new columns?

And the last few characters (!,a,0) stand for what action?

[lxp]

@Micky

I guess those lines are not triggering something but rather filter for newly defined columns in the JS, right?

Right, these are Opus' own commands, completely unrelated to JScript. You get (almost) the same from picking the columns manually in the command editor:

@Micky

and the last few characters (!,a,0) stand for what action?

The column's position will be unchanged (or it will be appended), its width will be auto and unlimited.

https://www.gpsoft.com.au/help/opus12/index.html#!Documents/Set.htm

(See Set COLUMNSADD)

[Micky]

Genius and thanks for your reply!