PORTABLE XLS PRINTER for Microsoft Excel
This utility is a COM add-in or add-in (.xla) programmed in Microsoft® Visual Basic® for Application (VBA) that makes it possible to generate an XLS (or XLSX) file from a sheet in an another XL file opened in Microsoft Excel® that summarizes only the printable data in the set print area. The generated XLS and the original look the same in print as they do on the screen, but the generated XLS is more portable, because it contains only the data necessary to produce the print-out. And, like any other file, you can email or transfer it using a CD, DVD, USB drive, or network connection. It is also easy to share, because you can view it on any computer or cloud computing environment with a recent XLS viewer installed, even if the same Excel version used to create the original XL file is unavailable, avoiding the complications of formulas, filters, links, macros, etc. This solution also avoids the unnecessary sending of copyrighted data or data hidden or filtered in the original XL file that may require privacy as a whole.
The impression here to a Portable XLS is similar to printing to a PDF, but with some advantages:
- Programs able to visualize basic XLS files are more widely installed than PDF viewers.
- It's most faithful to the original, because it is still a spreadsheet.
- Because it is spreadsheet, it allows greater editing flexibility if any changes or additions are necessary such as formatting, including text and formulas before being forwarded to a recipient who, in turn, can make changes and return with response data that can be captured to the original XL. All this made by user interface or by VBA code. See the pictures below.
It consists of only one file, the PortblXLSPrinter.dll, which is about 190 kb in size and can be installed automatically using one of the installer package files available below to download. Except for the DLL, no other setting is saved in the Microsoft Windows® Register. Its setting is saved in PortblXLSPrinter.ini and PortblXLSPrinter.xlam files that are created and maintained by the proper utility. You also can uninstall it automatically using the same installer file, leaving no mess on your Windows Register or disc.
Following items in this page:
- The utility makes a special copy of a sheet, capturing only the data necessary to produce an identical printout or screen visualization of the original sheet.
- By capturing only printable data, file size is reduced, and the transmission of confidential data, formulas, links, or authorial secrets is avoided. The print setup options for the special sheet will be the same as those of the original sheet, in terms of paper size, orientation, header, footer, margins, etc. These are the great advantages of this utility compared to the direct solutions offered by Excel for sending sheets or print areas by email. For more information on these solutions, see Ron de Bruin’s excellent study: http://www.rondebruin.nl/sendmail.htm
- The created sheet can optionally be protected against changes by a default password created randomly in each run or by a password that you enter.
- The utility saves the workbook of the created sheet as an XLS or XLSX file, here referred to only as a Portable XLS, regardless of which is the extension.
- It supports to open a previously saved Portable XLS and add new sheets. Thus, a single Portable XLS may contain printable views of all the sheets of one or more workbooks.
- In one run, it is possible to print any of the sheets in any of the workbooks that are already open in Excel, including macro sheets, to one or more Portable XLS files.
- When saving a Portable XLS, you can immediately compress it to a zip file, further reducing the file size to port.
- When saving a Portable XLS, you can immediately attach the file or its compressed version to an email message in Microsoft Outlook®, which you may verify and then send.
- When saving a Portable XLS, you can immediately save it as a web page (htm, html). Thus, your original XL, with all the possible complexities introduced by formulas, filters, links, macros, etc., can be shown on the web just as if it were printed or viewed on your computer screen. Sending only the data necessary for this view saves bandwidth and avoids sending unnecessary data that may be copyrighted, hidden, or filtered in the original XL file that require privacy as a whole.
- A Portable XLS is useful whenever the data necessary to produce an identical printed view is only a portion of the full content of the sheet, particularly when sending sheets containing autofilters that filter thousands of lines down to some tens of lines, or when there is information in the original that is confidential. in parts or when seen all in all.
- In sending a file for direct printing, a Portable XLS has a great advantage in size over sending a larger graphical (e.g. TIF) file format or a print (e.g. PRN) file format. Also, the recipient can still use Excel to view the sheet and edit it, if it is not protected, and print it on different types of printers.
- After installed, the utility is called from the Excel File Menu > Portable XLS Printer... Command or, in Excel 2007, from Office Button > Portable Printer Split Button > Portable XLS Printer Command or, in Excel 2010 or 2013, from File Menu > File Printer Tab > Portable XLS Printer Command.
- Can be called directly from any VBA code through the fPortblXLSPrinter() and fGetVersion() functions. See, soon below the pictures, a detail description of these functions and examples of the VBA applications that use them
- Occupies less than 190 Kb when installed
Tested in Excel for Windows, versions 2000, 2002(XP), 2003, 2007, 2010 (32-bit), 2013 (32-bit), and 2016 (32-bit) in MSI-based and Click-To-Run installations of Office 365™ cloud-based services.
The Portable XLS Printer can be called directly from any VBA code through the fPortblXLSPrinter() function that has these seven optional arguments:
1 - ToPrintWorkbookName¹ as String (Optional. Default active workbook name). The name of a workbook already opened in Excel.
2 - ToPrintSheetName as String (Optional. Default active sheet name). The name of a sheet in ToPrintWorkbookName workbook to print to the Portable XLS file.
3 - ToPrintSheetPasswrd as String (Optional. Default=""). Sheet password if ToPrintSheetName sheet is protected.
4 - PortblSavePath as String (Optional. Default the same path of ToPrintWorkbookName). The path where the Portable XLS file is or will be saved.
5 - PortblSaveName as String (Optional. Default an auto name based in the name of ToPrintWorkbookName). The name for the Portable XLS file.
6 - PortblSheetPasswrd as String (Optional. Default password created randomly). The password if the sheet in Portable XLS needs to be protected against changes. If “” is passed, it will not be protected.
7 - ZipPortbl As Boolean (Optional. Default=False). If True, the Portable XLS file created or updated will be immediately compressed to a zip file.
8 - AfterDoneEmail¹ As String (Optional. Default=0). A command setting the action to be performed after the Portable XLS file is ready and saved. It must be one of these four options:
"0" - To issue success alert.
"1" - Not to issue success alert.
"An email address" - To send the Portable XLS file attached to an email to the given address (Can be more than one address separated by ";").
"SaveAs" - To show the Excel Standard Save As dialog box making it possible to save the Portable XLS file in other formats supported by Excel as html, txt, pdf, etc..
9 - EmailSubj As String (Optional). The subject of the email to send with the Portable XLS file as an attachment.
10 - EmailMsg As String (Optional). The message of the email to send with the Portable XLS file as an attachment.
¹ If you inform only the ToPrintWorkbookName argument and the AfterDoneEmail argument passing email, the file you informed in ToPrintWorkbookName will be sent immediately as is. This allows changes to be made to a portable XLS already made before sending it as an attachment to informed email. That is, fPortblXLSPrinter() can be called twice in a three-step process. In the first step, it is called to print a complex XL on a portable XLS. In the second step, changes are made in the portable XLS such as formatting, including text and formulas as it remains a spreadsheet, a big advantage over print to PDF. And finally, in the third step, it is called again to send it by email with the changes you made.
1 - The code below calls the fPortblXLSPrinter() function to print active sheet to YourPortable.xlsx file in C:\YourTest folder.
2 - The code below calls the fPortblXLSPrinter() function such as the utility is called from the menu command.
The fPortblXLSPrinter() function can give the following returns:
-1 (Cancelled by the user).
This function makes possible to get the number of the installed version from any VBA code, returning a number in the 0.0.0 format.
1 - The code below calls the fGetVersion() function to verify that the installed version is updated.