Seeking good 2D barcode add-in for Excel

smryan

Perspective.
#1
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) :bonk:

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!
 

Attachments

Elsmar Forum Sponsor

Ron Rompen

Trusted Information Resource
#3
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.
 
I

isoalchemist

#4
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

Quite Involved in Discussions
#6
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:D, 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)

Code:
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
:cool:

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

Attachments

Last edited:

David-D

Involved In Discussions
#7
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
 
Thread starter Similar threads Forum Replies Date
W Seeking Guidance Verification Test Strategy for Class B Medical Devices IEC 62304 - Medical Device Software Life Cycle Processes 1
M Seeking advice regarding use of off-the-shelf (OTS) batteries Other Medical Device and Orthopedic Related Topics 4
A ISMS - Seeking VAPT Consultant Food Safety - ISO 22000, HACCP (21 CFR 120) 1
M Medical Device Directive - Seeking common nonconformance write up scenarios CE Marking (Conformité Européene) / CB Scheme 2
silentmonkey Seeking efficient method to manage install base data Manufacturing and Related Processes 0
K Seeking ISO 13485 Registrar Recommendations Registrars and Notified Bodies 15
R Tapping problems - 3/8" hot roll pickled & oiled steel - seeking expertise Manufacturing and Related Processes 15
S Responsibilities between government owner and private subcontractor of a power plant seeking ISO 9001 certification ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 9
B Seeking for an example or a template of a Business Plan Service Industry Specific Topics 3
B Health Canada Recall Definition - Seeking Clarification Canada Medical Device Regulations 5
W Seeking Returned Merchandise Procedure Customer Complaints 1
QMMike New Trend of not seeking Official ISO 9001 Registration ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 33
R Clinical Evaluation according to MEDDEV 2.7.1 (Rev 4) - seeking template ISO 13485:2016 - Medical Device Quality Management Systems 6
T ISO 9001:2015 Training for Management Team in NM - Seeking Suggestions and Providers ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
D Seeking Corrective Action Process Examples Nonconformance and Corrective Action 3
M AS9102 Training - Seeking third party agency that provides online AS9102 training AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 1
F Seeking Internship/Co-op Opportunities in Regulatory Affairs/QA/Clinical Domain (USA) Career and Occupation Discussions 4
L Seeking help in Basic Maintenance Knowledge Manufacturing and Related Processes 4
M Seeking opinion for ISO14001 team activity ISO 14001:2015 Specific Discussions 3
Sean Kelley Seeking Feedback on ASQ Guide to Failure Mode and Effect Analysis FMEA and Control Plans 2
S Seeking examples of Nonconforming Materials Nonconformance and Corrective Action 5
Sean Kelley Seeking Quality Training for Quality Technicians Training - Internal, External, Online and Distance Learning 1
P Seeking Information on ISO 15504 - Information Technology Process Assessment Other ISO and International Standards and European Regulations 3
K Seeking NDT Training and Certifications Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Chennaiite Cause Analysis for Verification/Validation Failures - Seeking Opinions Problem Solving, Root Cause Fault and Failure Analysis 3
Q Seeking Examples of current ISO/TS 16949 Training Procedures IATF 16949 - Automotive Quality Systems Standard 2
Ron Rompen Seeking Test Lab which can do 1440 Hour Accelerated Weathering Characteristics Misc. Quality Assurance and Business Systems Related Topics 1
Sean Kelley Seeking Ballooned PFMEA and Control Plans for Training FMEA and Control Plans 4
B Seeking help in answering Australia Tax Withholding Declaration Coffee Break and Water Cooler Discussions 1
H ASQ CQE Exam Review Workshop - Seeking Volunteers! September 2013 ASQ, ANAB, UKAS, IAF, IRCA, Exemplar Global and Related Organizations 7
N Seeking information on use of Byotrol Cleaning Chemicals in Pharma Industry Manufacturing and Related Processes 1
M Seeking input for a process-based quality management system training course ISO 13485:2016 - Medical Device Quality Management Systems 1
B Seeking help in writing marketing message Career and Occupation Discussions 12
B Seeking help in writing an audit report that can stand on the court Internal Auditing 6
S AAMI EC57 for ECG Medical Device - Seeking 3rd Party Test Laboratory US Food and Drug Administration (FDA) 5
W Seeking a Freelance Mechanical Inspector - Source Inspection in Bangalore, India AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 1
V Seeking Internal Audit Plan form or template Internal Auditing 5
W X-ray based NDT Test Equip. Mfger. seeking CE Mark - Must X-Ray Source be included? CE Marking (Conformité Européene) / CB Scheme 5
F Quality Awareness Sessions - Seeking Ideas and Presentation Material Quality Manager and Management Related Issues 4
S Seeking to learn English Video and Suggestions - I am Chinese Coffee Break and Water Cooler Discussions 9
D Seeking material on the Construction Process in IEC 62304 IEC 62304 - Medical Device Software Life Cycle Processes 3
T I'm seeking ISO 13485:2003 Supplier Audit Checklist Supplier Quality Assurance and other Supplier Issues 1
V DIS Inspect software for our CMM Operations - Seeking Contact Information Calibration and Metrology Software and Hardware 3
R "RUO" (Research Use Only) Medical Device now seeking Clinic Use 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 5
R Seeking Medical Device Registration Process in Korea Other Medical Device Regulations World-Wide 4
D Seeking Equipment Verification Standard Procedure books/guide/links Book, Video, Blog and Web Site Reviews and Recommendations 1
H Seeking Volunteers to particpate in ASQ Exam Review Workshop in September! ASQ, ANAB, UKAS, IAF, IRCA, Exemplar Global and Related Organizations 0
K Seeking copies of CBOK and PMBOK Quality Manager and Management Related Issues 4
J Seeking advice on how to handle a Special Process Nonconformance AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 12
K Seeking help in making a decision - Project behind schedule Quality Manager and Management Related Issues 11

Similar threads

Top Bottom