Export Selection API control

[impactnoise]

Hello,

I am seeking some assistance with using the API to streamline a few “Export Selection” operations.

I am relatively new to writing VBA code to extend excel functionality and automate certain tasks, so apologies if my limited capabilities prove to be a limiting variable here. I can google my way through a problem fairly well, but this is a little out of my league and wonder if someone can shed some light.

A little background:
I have created a spreadsheet which I use to print dated operator licenses for the various forklift equipment at my place of employment. The name-cells (F4, M4, F24, M24) are drop downs which summon dates from a matrix of sorts into cells that are formatted to dimensions ready to print. Right now, I use the export selection function to save each range to a PNG file, which is then printed by a PVC badge printer.

My goal includes:
I am looking to automate the process with VBA and some kind of button/form control. Where I am lost is in executing the below sequence of events:

-If F4 is not blank, then select E3:H18
-Export Selection: Preset = Png, 600DPI, RGB, White Canvas; Output = Width 144 Height 277.25 Unit pt
-Save As: licence###.png 'not critical, but the # should increase by one each time a licence is created

-If M4 is not blank, then select L3:O18
-Export selection (operation repeats)

-If F24 is not blank, then select E23:H38
-Export selection (operation repeats)

-If M24 is not blank, then select L23:O38
-Export selection (operation repeats)

I examined the following post, but I am not sure how to apply it to my situation:

Using API to export chart into .PNG file

It’s fairly simple. I attach a sample workbook with some VBA code in it that shows how to do it. Here’s the pertinent code: [code2=visual_basic]Sub TestAPI() ’ Tests the XL Toolbox API ’ To make this work, you need to add “Daniel’s XL Toolbox” ’ to the VBA project’s references (Visual Basic Editor, Menu “Extras”, ’ command “References”). Dim objSheet As Worksheet Dim lngRes As Long ' If the active sheet is a chart sheet, the ' following assignment will cause a run-time error Set objSheet…

Thank you for any assistance that might be provided.

[Daniel]

While I’m not able to provide you with a full-fledged application, here’s a little code that might get you started:

Option Explicit

' Static counter variable.
' There are other ways to do this (e.g., use a custom class),
' but this serves the purpose and is very simple to implement.
Private mCounter As Long

' Entry point for the API demonstration.
' Use this with ActiveX control elements and the like.
Public Sub Execute()
    ExportIfBlank "F4", "E3:H18"
    ExportIfBlank "M4", "L3:O18"
    ExportIfBlank "F24", "E23:H38"
    ExportIfBlank "M24", "L23:O38"
End Sub

Private Sub ExportIfBlank(checkRange As String, selectRange As String)
    If Not IsEmpty(checkRange) Then
        ActiveWorksheet.range(selectRange).Select
        ExportSelectionUsingXLToolboxNG
    End If
End Sub

' Helper function that provides the active worksheet.
' NB: This will crash if the active sheet is a chart!
Private Function ActiveWorksheet() As Worksheet
    Set ActiveWorksheet = ActiveSheet
End Function

Private Function IsEmpty(address As String) As Boolean
    Dim range As range
    Set range = ActiveWorksheet.range(address)
    ' CountA computes the number of cells that are not empty
    IsEmpty = (WorksheetFunction.CountA(range) = 0)
End Function

' Export the current selection with custom settings.
' This code is taken from https://www.xltoolbox.net/api
' with adjustments for custom parameters
' See also:
' https://bovender.github.io/XLToolbox/classXLToolbox_1_1Vba_1_1Api.html
' for a list of parameters.
' The exported graphic will be saved in the same folder as
' the current workbook.
Private Sub ExportSelectionUsingXLToolboxNG()
    Dim addin As Office.COMAddIn
    Dim apiObject As Object
    Set addin = Application.COMAddIns("XL Toolbox NG")
    Set apiObject = addin.Object
    mCounter = mCounter + 1
    apiObject.ExportSelection _
        "LICENCE" & Format(mCounter, "000") & ".PNG", _
        600, "RGB", "CANVAS" ' last parameter may also be "WHITE"
End Sub

The attached workbook contains this code and an ActiveX button that calls the ‘Execute’ method:

api-demo.xlsm (25.7 KB)

It’s not possible to set the height and width of the exported graphic with the API call. You’ll have to adjust your rows and column sizes as needed.

However, without knowing your specific circumstances, it seems to me that there might be other ways to print those licences; ways that are possibly less time-consuming to implement and more maintainable in the long run:

• Create a Word (or LibreOffice/OpenOffice) document, set the page size to your licence format, and use the Mail Merge feature to fill fields with licence information, e.g. from an Excel file.
• If you must send a graphic file to your printer, have a look at SVG files. These are graphic files that can be written by humans. It’s a bit like HTML for web pages. There are tons of resources to be found on the internet; I think the tutorial by Mozilla might be useful. Because SVG files are basically text files, they are easy to produce by hand or programmatically, and you can convert SVG files to other graphics formats as needed.

Hope any of this helps.

[impactnoise]

Wow, thank you so much for this outstanding information! You’ve provided a lot to work with, and I appreciate the alternative suggestions. Even if I take on your recommendation to make use of SVG files or mail merge, the coding bit definitely improves my understanding towards implementing the API content.