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.
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
CTRL+SHIFT+Q). In the NG series, it was pimped and placed on
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:
- 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.
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.
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
colorSpace may be one of
MONO (case insensitive); if
an empty string
"" is given,
RGB will be assumed.
transparency may be one of
WHITE; if this string is
NONE will be assumed.
NONE means no transparency.
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.
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
If you have a previous alpha version of XL Toolbox NG installed, you can update via “Check for updates”.
See the new changelog.