PORTABLE XLS PRINTER for Microsoft Excel

What's new   Download   Home 

This utility 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, it can be sent via email, WhatsApp or transferred using a pen 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 an Excel Portable XLS is similar to printing to an Adobe 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 basically of only one file (PortblXLSPrinter.dll, PortblXLSPrinter.exe, or PortblXLSPrinter.xla) that can be installed automatically using one of the installer package files available below to download. Except for the DLL, if you choose COM Add-in, no other setting is saved in the Microsoft Windows® Register. Its setting is saved in PortblXLSPrinter.ini, PortblXLSPrinter LoadExe.xla, SendKeys.exe, and PortblXLSPrinter.xlam files that are created and maintained by the proper utility. You also can uninstall it automatically, leaving no mess on your Windows Register or disc.

Following items in this page:

Features

Download

Add-in Options to Install

Pictures

VBA fPortblXLSPrinter() Function

VBA fGetVersion() Function

An Advanced VBA Example

Features:

- 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 an Excel Portable XLS, you can immediately compress it to a zip file, further reducing the file size to port.

- When saving an Excel Portable XLS, you can immediately attach the file or its zipped version to an email message that you can verify and then send by the Microsoft Outlook® or another default email recipient in Windows. It can compose the email with default data or captures address, subject and text message in the active cell and its adjacent, looking down or right. These cells should normally be outside the defined print area that goes as attachment.

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

- The portable file, instead of XLS or XLSX, can be saved as a Word document in doc or docx format or, via Excel, as PDF, txt, csv and all other formats supported by Excel.

- 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 newer, 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 600 Kb when installed

Download:

Tested in Excel for Windows, versions 2000, 2002(XP), 2003, 2007, 2010 (32 and 64-bit), 2013 (32 and 64-bit), 2016 (32 and 64-bit), and 2019 (32 and 64-bit) in MSI-based and Click-To-Run installations of Office 365™ cloud-based services.

Add-in Options to Install:

This utility can be available to be installed in three options of add-ins: EXE Add-in, COM Add-in, or Excel Add-in. COM Add-in and Excel Add-in run on Windows in the same process or memory space of Excel and are well detailed technologies in Excel documentation and good books about VBA, while EXE Add-in is a special name here for a mix of those two technologies where the same VBA code is run in separate EXE process and a small Excel Add-in makes the connection between the two processes. Despite the differences in the technologies, this utility is fully functional in any of them. So, what can decide on your choice to install are the features below compared:

¹Temporarily unavailable.

Pictures:

VBA fPortblXLSPrinter() Function:

The Portable XLS Printer can be called directly from any VBA code through the fPortblXLSPrinter() function that has these 11 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 “”, no password). The password if the sheet in Portable XLS needs to be protected against changes. In particular, if “RandomPassword” is passed as a password, this will be interpreted as a flag to protect with a random password generated by code that will not be known to anyone, not even the person who printed it.

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

      Note: To save the portable file as Word document instead of XLS or XLSX, just inform the name of the file with the extension .doc or .docx through argument 5 above, the PortblSaveName.

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.

11 - SndKeys As String (Optional). Enables sending keys to the email message window as a last action. For example:

     "{PAUSE 1}{VK_162}{VK 83}{VK 162}{PAUSE 2}{ESC}" - Pauses, sends the CTRL+S keys, makes another pause, and finally sends ESC to the Outlook message window to save, close, and send the message as is to the Inbox or Draft.

     " {PAUSE 1}{VK_162}{VK 13}{VK 162}" - Pauses and sends the Ctrl+Enter keys to the Outlook message window to save, close, and send the message as is to the Outbox. Note: In these examples, the ALT and CTRL control keys are those on the left side of the keyboard, as those on the right side can be customized for other functions, for example, on virtual machines. To understand the syntax of the key sending codes, see here and here.

     If you have Microsoft Outlook installed, instead of keycodes, you can use SendByCode or SaveByCode flags to send the email silently via code to Outlook Outbox or save to Draftbox, but for security reasons the Outlook may occasionally ask for temporary authorization to access your contact list.

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

The fPortblXLSPrinter() function can give the following returns:

fRet = -1 (Cancelled by the user).
fRet = 0 (Successfully executed).
fRet = 1 (Failure: When trying to unprotect the sheet with the informed password in Excel versions earlier than 2007).
fRet = 2 (Failure: When trying to unprotect the spreadsheet with the informed password in any Excel version).
fRet = 3 (Failure: When trying to send email).
fRet = 101 (Failure: Workbook informed by ToPrintWorkbookName argument or no active workbook found containing sheet to print).
fRet = 102 (Failure: Sheet informed by ToPrintSheetName argument or no active sheet found to print).
fRet = 103 (Failure: The password was not informed by ToPrintSheetPasswrd argument and the sheet to print is protected).
fRet = 104 (Failure: Disk folder informed by PortblSavePath argument not found).
fRet = 105 (Failure: The filename informed by PortblSaveName argument is not valid, maybe the extension).
fRet = 106 (Failure: Option informed by AfterDoneEmail argument is invalid, maybe invalid email address).
fRet = 107 (Failure VBA code without definition, but with alert).

Examples:

1 - The code below calls the fPortblXLSPrinter() function to print active sheet to YourPortable.xlsx file in your Desktop folder and zip it.

Sub YourSub()
    Dim lRet As Long
    Dim ObjToVBA As Object
    Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")
    
    lRet = ObjToVBA.fPortblXLSPrinter(, , , "%Desktop%", "YourPortable.xlsx", , True)
    If lRet <> 0 Then MsgBox "Error Nº: " & lRet, vbCritical, "Fail!"
End Sub

2 - The code below calls the fPortblXLSPrinter() function such as the utility is called from the menu command.

Sub YourSub2()
    Dim lRet As Long
    Dim ObjToVBA As Object
    Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")
    
    lRet = ObjToVBA.fPortblXLSPrinter
    If lRet <> 0 Then MsgBox "Error Nº: " & lRet, vbCritical, "Fail!"
End Sub

3 - The code below calls the fPortblXLSPrinter() function to print active sheet to YourPortable.doc file in Desktop.

Sub YourSub3()
    Dim lRet As Long
    Dim ObjToVBA As Object
    Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")
    
    lRet = ObjToVBA.fPortblXLSPrinter(, , , "%Desktop%", "YourPortable.doc")
    If lRet <> 0 Then MsgBox "Error Nº: " & lRet, vbCritical, "Fail!"
End Sub

Notes about Early and Late Binding methods:
1 - All examples here are suitable for any installation of the types of add-ins listed above and use the Late Binding method that I think is the best practice, but, if you prefer Early Binding method, replace the line 'Dim ObjToVBA As Object' by 'Dim ObjToVBA As PortblXLSPrinter.ClassObjToVBA' and use ‘References...’ command on VBE Tools menu to reference ‘PortblXLSPrinter’ project to your VBA project. That's all. This method makes easy to see the names and positions of function's arguments at design time, but is not recommended at run time.
2 - If you install the COM Add-in type, you can ALSO use its well-known Early and Late Binding methods:
     - For Late Binding, just replace the line ‘Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")’ by ‘Set ObjToVBA = Application.COMAddIns("AddInPortblXLSPrinter.ExcelDesigner").Object’.
     - For Early Binding, replace the line 'Dim ObjToVBA As Object' by ' Dim ObjToVBA As XLSPrintObjToVBA ' and the line ‘Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")’ by ‘Set ObjToVBA = Application.COMAddIns("AddInPortblXLSPrinter.ExcelDesigner").Object’, and use ‘References...’ command in VBE Tools menu to reference ‘PortblXLSPrinter.dll’ file to your VBA project.

VBA fGetVersion() Function:

This function makes it possible to get the number of the installed version from any VBA code, returning a number in the 0.0.0 format.

The example below calls the fGetVersion() function to verify that the installed version is updated.

Example:

Sub YourSub()
    Dim sRet As String, vCallVerOld As Variant: On Error Resume Next
    Dim ObjToVBA As Object
    Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")
    sRet = ObjToVBA.fGetVersion
     If Err.Number <> 0 Then
          MsgBox "The 'Portable XLS Printer for Excel' not found!"
     Else
          vCallVerOld = Split(sRet, ".")
          vCallVerOld = vCallVerOld(0) * 10 ^ 6 + vCallVerOld(1) * 10 ^ 3 + vCallVerOld(2)
          If vCallVerOld < 2000002 Then MsgBox "The 'Portable XLS Printer for Excel' " & sRet & " found is old!"
          If vCallVerOld >= 2000002 Then MsgBox "The 'Portable XLS Printer for Excel' " & sRet & " found is updated for this code!"
     End If
End Sub

See notes about Early and Late Binding methods.

An Advanced VBA Example:

The code below that supports any type of add-in installed is an extract from a project in use professionally developed in VBA for a supermarket that has a sheet with its general list of tens of thousands of products it buys and sells, i.e. its portfolio. From this list, it calculates the necessary orders and generates customized price quotation sheets for each supplier pre-registered for bids and automatically sends them by e-mail. It then imports the quotations returned from suppliers and mounts an electronic competition to automatically indicate the winning supplier for each product. Once the competition is over, it issues and sends the order e-mail to each supplier with the products it has won.

In practice, the VBA project involves processes where filters are applied to the general sheet which is then printed to a portable XLS file and not to PDF to be sent by email to suppliers who continue the work in sheet to return and be properly imported from back to general list. Filters generate visualizations and therefore extremely customized portable sheets. Thus, from the same general list in the same competition, a supplier according to its portfolio can receive a sheet with a single product and another with thousands, even at the quotation stage.

This project shows the importance of printing to a portable XLS file and not to a PDF. In it the 'Portable XLS Printer for Excel' is usually called in two out of three steps. In the first, the printing of what is visualized after applied filters is done for an XLS or XLSX file as it would be done for a PDF, in the second step, the project reopens the saved portable XLS and inserts header according to the supplier and total and subtotal formulas and saves, and in the third step it calls the 'Portable XLS Printer for Excel' again just to send the XLS to the supplier's email as it was edited and saved, that is, it just sends the email without printing to another portable XLS, as it would eliminate the entered formulas. This way, the supplier will have all the convenience of working in a sheet with formulas and automatic sums and not in a dead PDF. Also, perhaps most importantly, the data returned by the hundreds of suppliers will be imported from sheets automatically and accurately which would be impractical to do from PDFs, DOCs or TXTs. As in this project, the ‘Portable XLS Printer for Excel' is called repeatedly, the best thing was to develop a similar fPortblXLSPrinter() function where the object made available to the VBA project is created and criticized once throughout all the processes. In it, the updated version criticism and the need or not to encode commas and use the fGetBuff() and fSetBuff() functions are already built-in.
his

Option Explicit
Sub AdvancTest()
    Dim lRet As Long
    Dim PortblSavePath As String, PortblSaveName As String, AfterDoneEmail As String, EmailSubj As String, EmailMsg As String, SndKeys As String
    PortblSavePath = ThisWorkbook.Path
    PortblSaveName = ThisWorkbook.Name & "Portble.xls"
    If Dir(PortblSavePath & "\" & PortblSaveName) <> "" Then Kill PortblSavePath & "\" & PortblSaveName

    '1st Step: Silently save the portable XLS from the active sheet.
    AfterDoneEmail = 1
    lRet = fPortblXLSPrinter(, , , PortblSavePath, PortblSaveName, , , AfterDoneEmail)   'Save the Portable XLS silently.
    If lRet <> 0 Then MsgBox "Error Nº: " & lRet, vbCritical, "Fail!": Stop

    '2nd Step: Open the portable XLS saved above, make changes such as changing cell text, inserting formulas and then saving.
    If Dir(PortblSavePath & "\" & PortblSaveName) <> "" Then    'Open to edit the title and enter sum and subtotal formulas..
        Dim PortblWb As Workbook, iMax As Long
        Set PortblWb = Workbooks.Open(PortblSavePath & "\" & PortblSaveName)
        Range("A2").Value = "CUSTOM TITLE"
        iMax = Cells(4, 1).CurrentRegion.Rows.Count: iMax = 10
        With Range("H4")
            .Offset(1).FormulaR1C1 = "=IF(RC[-4]="""","""",RC[-4]*RC[-3])"
            If iMax > 1 + 1 Then .Offset(1).Resize(1, 1).AutoFill Destination:=Range("H4").Offset(1).Resize(iMax - 1, 1), Type:=xlFillValues
            .Offset(iMax + 1, 0).FormulaR1C1 = "=SUBTOTAL(109,R[-" & iMax & "]C:R[-2]C)"
        End With
        PortblWb.Save
    End If

    '3rd Step: Send by email the portable modified above as it was saved. In other words, it doesn't generate a new portable one, it just sends it by email, keeping the formulas.
    AfterDoneEmail = "bigwholesale@server.com"
    EmailSubj = "COMPETITION 2022-06-10 - Price Quote - Supplier 7112-Big Wholesale"
    EmailMsg = "Dear supplier 7112-Big Wholesale," & vbCrLf & vbCrLf _
             & "Attached is our worksheet for price quotation for the competition named ' COMPETITION 2022-06-10 '." & vbCrLf & vbCrLf _
             & "Closing of offers: 06/19/22 at 11:30," & vbCrLf & vbCrLf _
             & "Best regards," & vbCrLf & vbCrLf _
             & "=================" & vbCrLf _
             & "Amplo Supermarkets"
    SndKeys = "{PAUSE 1}{VK_162}{VK 13}{VK 162}"   'Ctrl+Enter
    lRet = fPortblXLSPrinter(PortblWb.Name, , , , , , , AfterDoneEmail, EmailSubj, EmailMsg, SndKeys)
    If lRet <> 0 Then MsgBox "Error Nº: " & lRet, vbCritical, "Fail!": Stop
    PortblWb.Close False
    Set PortblWb = Nothing
    MsgBox "The portable XLS of active sheet was generated, reopened, changed, saved and finally emailed successfully!"
End Sub

Function fPortblXLSPrinter(Optional ToPrintWorkbookName As String, Optional ToPrintSheetName As String, Optional ToPrintSheetPasswrd As String, _
                           Optional PortblSavePath As String, Optional PortblSaveName As String, Optional PortblSheetPasswrd As String, _
                           Optional ZipPortbl As Boolean, Optional AfterDoneEmail As String, Optional EmailSubj As String, Optional EmailMsg As String, Optional SndKeys As String) As Long
    DoEvents    'Necessary in Excel SDI, 15 and earlier, to avoid an open Wb instability.
    Dim vCallVerOld As Variant, lRet As Long
    Static ObjToVBA As Object
    If ObjToVBA Is Nothing Then    'If the object is not yet created, create it and check the version, calling the PORTABLE XLS PRINTER here.
        On Error Resume Next
        Set ObjToVBA = Application.Run("PortblXLSPrinter.CreateObj")
        If Not ObjToVBA Is Nothing Then
            vCallVerOld = ObjToVBA.fGetVersion
            If vCallVerOld = "" Then Set ObjToVBA = Nothing: Err.Raise vbObjectError + 1
        Else
            Err.Raise vbObjectError + 1
        End If
 
        'Criticize the object creation and its version.
        If Err.Number <> 0 Then
            If MsgBox("'Portable XLS Printer for Excel' not found! This command requires its installation. You can download it for free for personal use from its homepage. OK?", vbOKCancel + vbQuestion, "Object not found! Portable XLS Printer for Excel") = vbOK Then ThisWorkbook.FollowHyperlink "http://cpap.com.br/orlando/PortblXLSPrinterMore.asp?IdC=Help"
            Set ObjToVBA = Nothing: fPortblXLSPrinter = -1: Exit Function
        Else
            vCallVerOld = Split(vCallVerOld, ".")
            vCallVerOld = vCallVerOld(0) * 10 ^ 6 + vCallVerOld(1) * 10 ^ 3 + vCallVerOld(2)
            If vCallVerOld < 2000002 Then
                If MsgBox("Found 'Portable XLS Printer for Excel' is old! A newer version is required for this command. You can download it for free for personal use from its homepage. OK?", vbOKCancel + vbQuestion, "Object not found! Portable XLS Printer for Excel") = vbOK Then ThisWorkbook.FollowHyperlink "http://cpap.com.br/orlando/PortblXLSPrinterMore.asp?IdC=Help"
                Set ObjToVBA = Nothing: fPortblXLSPrinter = -2: Exit Function
            End If
        End If
        On Error GoTo 0
    End If
    If ToPrintWorkbookName = "VrfObjOnly" Then Exit Function

     'Object created and criticized now calls fPortblXLSPrinter() function repassing its arguments.
    lRet = ObjToVBA.fPortblXLSPrinter(ToPrintWorkbookName, ToPrintSheetName, ToPrintSheetPasswrd, _
                                      PortblSavePath, PortblSaveName, PortblSheetPasswrd, _
                                      ZipPortbl, AfterDoneEmail, EmailSubj, EmailMsg, SndKeys)
End Function

See notes about Early and Late Binding methods.

More information

Home