Alpha 9 with new Import CSV and Quit commands and VBA API

The ninth iteration of the ‘alpha’ developmental series evolved while I was grant-writing. I missed a couple of power-user features in Excel, so I quickly added them to the XL Toolbox. In fact, this is the way the entire add-in came into existence back in 2008.

In addition, I have implemented a very basic API for VBA that exposes the graphic export function to VBA projects – see below.

The ribbon of Alpha 9

Changes since Alpha 8

CHANGE: Add a visual ‘resize grip’ to Sheet Manager.
FIX: Display tooltip describing export preset in single export combobox.
FIX: Do not allow transparency with CMYK color space.
FIX: EMF export no longer exports PNG, but actual EMF file.
FIX: Enable or disable CMYK depending on selected file type.
FIX: Export preset editor can be closed with ESC.
FIX: Export preset name with color management.
FIX: Last directory and file name of graphic export are properly remembered now.
FIX: Make sure confirmation and notification dialogs are not hidden by other windows.
FIX: Properly save export presets.
NEW: Feature to quit Excel, closing all workbooks, saving as needed.
NEW: One-click CSV import feature.
NEW: Programming interface (API) for VBA code.

New feature: Quit Excel

The new ‘Quit Excel’ command is actually not new – it was present in the older versions of the XL Toolbox, albeit only accessible with a keyboard shortcut (CTRL+SHIFT+Q). In the NG series, it was pimped and placed on the Ribbon.

Quit Excel is especially useful if you have many workbooks open at once, and want to close the application at once. Rather than closing one workbook at a time, the Toolbox’ Quit Excel command offers three choices:

Quit Excel command

Close every workbook and ask if changes should be saved as needed.
Save every edited workbook and close them all at once.
Discard any changes and close all workbooks at once.

The latter choice may be useful if Excel automatically recalculates your workbooks as you open them – this will set the ‘changed’ flag on all of them, even if you did not modify anything.

New feature: CSV Import

The new CSV Import command facilitates opening comma-separated value (CSV) files with a single click of a mouse button. Bypassing Excel’s own CSV import wizard, it permits rapid opening of CSV files. By using the command “Open CSV with params…” from the drop-down menu, the field separator, decimal separator, and thousands separator that are to be used for import can be adjusted.

Quick settings for CSV import

This command is especially useful if you happen to run Excel with a non-English locale that uses commas as decimal separators, and have to import files that contain periods as decimal separators.

API

If you want to automate graphic export from Excel, you can use the XL Toolbox NG API in Visual Basic for Applications. You need to create an API object (see example below), which provides the following function:

function ExportSelection(
    fileName as string,
    dpi as long,
    colorSpace as string,
    transparency as string) as long

fileName must include the file extension, which must be one of .tif, .tiff, .png or .emf.

colorSpace may be one of RGB, GRAY, or MONO (case insensitive); if an empty string "" is given, RGB will be assumed.

transparency may be one of NONE, CANVAS, WHITE; if this string is empty, NONE will be assumed. NONE means no transparency. CANVAS draws the graphic on a transparent canvas (if a chart has a chart area filled with solid white, no transparency may be seen!). WHITE makes all whites in the graphic transparent.

Return codes are:

0 if successful;
1 if unknown file type;
2 if unknown color space;
3 if unknown transparency type.

Example:

Private Sub ExportSelectionUsingXLToolboxNG()
    Dim addin As Office.COMAddIn
    Dim apiObject As Object
    Set addin = Application.COMAddIns("XLToolboxForExcel")
    Set apiObject = addin.Object
    Debug.Print apiObject.ExportSelection("test.png", 300, "gray", "white")
End Sub