Seeking good 2D barcode add-in for Excel

[smryan]

The attached file is an excel sheet I use to generate part labels with unique serial numbers. The 1D barcode is a simple enough font feat but has only the serial number. A customer is asking if we can do a 2D DataMatrix barcode that will contain our PN, their PN & the serial number.

I've Googled aplenty and tried a few different add-ins so far. Nothing even comes close to the ease and fluidity of the 1D process. I've only found 1 that can link to a cell so it can be updated if the cell changes. I can't get any of them to read multiple cells (which is the whole point of using a 2D format; lots-o-info)

We use a special label material made to go in our standard laser printers, so a different program/printer set up isn't an option. Excel version is 2010. OS are WinXP and Win7.

If anyone has some hints, suggestions or miracles I'd be most appreciative!

 

[Stijloor]

A Quick Bump!

Can someone help?

Thank you very much!!

 

[Ron Rompen]

Not sure if it is quite what you are looking for, but we use 'Bartender' software by Seagull Scientific. I know from talking to the sales rep that it does read from a database such as yours, so it can probably do what you want. It is easily found on the web; just google 'Bartender Software' and it will pop up for you.

 

[isoalchemist]

We also used Bartender from Excel for a 3D barcode and it worked well.

I believe we also played with one called WASP. Not sure why we had two, but my guess is we had one for a project and another project bought the other without checking what was available.

 

[Darius]

You can find this interesting
QRCodeLib GNU library

and matrix

http://sourceforge.net/projects/datamatrix-dll/

 

[Darius]

I found , Although the code for Excel VBA wasn't there,

Free intbusoft.com DataMatrixEncodeSDK

you will need Microsoft Visual C++ 2010 SP1 Redistributable Package (x86)

I did change the excel file to fill the blanks, the version I can test is the 32 bits, because I don't have a 64bits computer at home.

I didn't fully understand the parameters, but I managed to make it work

This is the code for your file (for 32 bits, check the code in the file for 64 bits if you like)

Option Explicit
 Private Declare Function SetEnvironmentVariable Lib "kernel32" Alias "SetEnvironmentVariableA" (ByVal lpName As String, ByVal lpValue As String) As LongPtr
 Private Declare Function EncodeDataMatrix Lib "DataMatrixEncode.dll" _
                (ByVal EncodeText As String, ByVal EncodeSize As Integer, _
                 ByRef out_bitmap As Byte, ByRef buffer_size As LongPtr, _
                 ByVal SizeCell As Integer, ByVal Code As Integer, _
                 ByVal Mode As Integer, ByVal SizeNum As Integer) As Integer
                 
Private Sub DataMatrix_Generate(INFO As String, REC As Shape)
Dim buffer As String, s As Integer, result As Integer, i As Long, f As Integer
Dim s1 As Long, out_bitmap(20000) As Byte ' it was out_bitmap(1000000)

 buffer = INFO: s1 = 20000 'it was s1 = 1000000 in the original code, much faster this way
        
'4 is the image size
  result = EncodeDataMatrix(buffer, Len(buffer), out_bitmap(0), VarPtr(s1), 4, 0, 5, 0)
  If result = 0 Then
     f = FreeFile()
     Open Environ("TEMP") & "\QRCODE.bmp" For Binary Lock Write As #f
     For i = 0 To s1
       Put 1, i + 1, out_bitmap(i)
     Next i
     Close #f
   End If
   REC.Fill.UserPicture Environ("TEMP") & "\QRCODE.bmp"
End Sub


Sub AddMatrixCodes()
Dim WS As Worksheet, SH As Shape, i As Integer, CO As Integer, RW As Integer
  Set WS = Application.ActiveSheet
  
  If InStr(1, Environ("PATH"), ActiveWorkbook.Path, vbTextCompare) = 0 Then Call SetEnvironmentVariable("PATH", Environ("PATH") & ";" & ActiveWorkbook.Path & "\" & Chr(0))
  For Each SH In WS.Shapes
    If Left(SH.Name, 5) = "Group" Then
       i = 0
       For RW = 0 To 7
         For CO = 0 To 4
0:         i = i + 1
           If SH.GroupItems(i).Height > 100 Then GoTo 0
           DataMatrix_Generate WS.Cells(14 + RW * 5, 3 + CO * 4), SH.GroupItems(i)
         Next CO
       Next RW
    End If
  Next SH
End Sub

note: You want it as an add-in of as a macro is fine....?

 

[David-D]

I assume you've received better answers from the responses above but if you are successful in getting it to take input from a single cell, perhapse the "concatenate" function would help you. You can use it to stick together a bunch of separate strings/text/cells so if you have people enter the separate values in different cells, you can then use the concatenate function to combine them all in one cell automatically. You can also build it to interspace spaces, commas, etc between the entries.

David