excel Excel help

[DeadShotAnk]

 

Hi everyone,

I need help automating a process to print labels directly from Excel. I’ve attached a picture of my file: https://ibb.co.com/DYdNr5H and these are the labels: https://ibb.co.com/S5SRtH5
it includes serial numbers, label names, quantities, and a "Print" button.

The idea is to embed PDF/JPG/PNG  in the Excel sheet or link them so that clicking "Print" automatically sends the corresponding label to the printer. This would save me tons of time compared to manually printing each label.

Questions:
‣ Is this a good approach, or should I consider another tool/software?
‣ Can Excel handle this efficiently, especially for many labels?

Looking forward to hearing your suggestions!

[Avocado Diaboli]

It's possible to do it, but I'm not sure it's the best way to do it, since it just sends a print command to whatever default printer you have selected in Windows. I fiddled with it a bit and found that you can invoke a VBA function by following a hyperlink. The formula in this case is

 

=HYPERLINK("#Print_Label()";"Print label")

 

The first parameter is the name of the function that gets called and the second parameter is the text that gets displayed in the cell. The script that runs once the link is clicked depends on the values relative to the position of the cell the link is clicked in. It basically just offsets the selection by two to the left to get the label name and by one to the left to get the quantity.

 

To make customization easy, I added the folder path in cell C2, which the code then references, so if you ever need to change it, it's easy to do.

 

 

The folder with the files is laid out like this, again with the file names corresponding with the label name in the Excel sheet. That means adding a new file and a corresponding line in the table is pretty straightforward and should just work automatically, if you also copy the hyperlink into the new row.

 

Then you need to create a module in the VBA Editor (Alt+F11) and add this function in that module.

 

Function Print_Label()
    
    Set Print_Label = Selection
    
    Dim folder As String, fileName As String
    folder = ThisWorkbook.Sheets(1).Range("C2").Value
    fileName = Print_Label.Offset(, -2).Value & ".pdf"

    Dim quantity As Integer
    If Not IsNumeric(Print_Label.Offset(, -1).Value) Then
        MsgBox "Please enter a numeric value for the number of labels you wish to print."
        Exit Function
    Else
        quantity = Print_Label.Offset(, -1).Value
        If quantity < 1 Then Exit Function
    End If
    
    Static myShell As Object
    Dim i As Integer
    Set myShell = CreateObject("Shell.Application")
    
    For i = 1 To quantity
        myShell.Namespace(0).ParseName(folder & fileName).InvokeVerb "Print"
    Next i

End Function

[Sauron]

6 hours ago, DeadShotAnk said:

‣ Is this a good approach, or should I consider another tool/software?

Probably not a great idea, if you must use excel for some reason then sure, otherwise I'd shift towards a python or even powershell script

6 hours ago, DeadShotAnk said:

‣ Can Excel handle this efficiently, especially for many labels?

As long as you're printing them one at a time as shown by @Avocado Diaboli it will probably be fine...? But certainly nowhere near as efficient as a small program doing the same thing. If you want to batch print hundreds or thousands of labels excel likely becomes inadequate.