how to display barcode of a unique number using vba

[aman]

Hi guys

I have written the code in access that will transfer values into excelsheet from access form and prints off that sheet. Now everything works fine except one thing which my manager now asked me to do.that is I have to print barcode of the unique number(i) on the sheet as well.

Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    With xlApp
        Set xlWB = .Workbooks.Open("J:\WilliamsLea-AIMM\s1.xls")
    End With
    xlWB.Sheets("sheet1").Cells(2, 6).Value = i
   [COLOR=darkred] xlWB.Sheets("sheet1").Cells(2, 9).Value = barcode(i)
[/COLOR]    xlWB.Sheets("sheet1").Cells(7, 5).Value = Format(DateSerial(Combo6, Combo4, Combo2), "mm/dd/yyyy")
    xlWB.Sheets("sheet1").Cells(9, 5).Value = Format(DateSerial(Combo12, Combo10, Combo8), "mm/dd/yyyy")

Can anyone please help me to figure it out please.

Thanks
Aman

 

[Julian]

Hello Aman,

I did this a long time ago. It was not worth the trip. Took far too much time to do as the specifications were tight and the output was a little flakey as Access has not been developed for graphical output. Also you need to know what type of barcode you are using e.g. EAN-128, UPC-A, UPC-E, MSI, EAN-8, EAN-13, Code 11, Code 93 etc.

I recommend purchasing proprietory barcode software unless you you have nothing better to do with your time!

Apologies but I have no recommendations I can't remember what was used in the end, but try googling:

"barcode software" or "barcode active X"

 

[bulrush]

In the Excel cell you will have to change the font to the correct barcode font. But that means the cell height must also change, manually or automagically, to correctly show a scannable barcode.

The other thing is, different barcode schemes have different prefix characters. If I want to barcode a number in a UPC barcode, the prefix and suffix characters are the star (*). So if the UPC is 12345 66666 then the barcoded string is: *12345 66666*

(I think that's correct. Maybe UPC uses check digits, and Code 39 uses the star.)

I hope this gets you started.

My suggestion: Make an Access report. It should handle barcodes better than Excel will because fields can stretch vertically automatically in Access.

 

[aman]

Hi bulrush

Thanks for your reply. but don't know exactly how to start with this. If I make an access report rather than transfering the information into excelsheet then how can i proceed with printing the barcode of a number on the top of the access report.

Thanks Again
Aman

 

[Atomic Shrimp]

In the Excel cell you will have to change the font to the correct barcode font. But that means the cell height must also change, manually or automagically, to correctly show a scannable barcode.

The other thing is, different barcode schemes have different prefix characters. If I want to barcode a number in a UPC barcode, the prefix and suffix characters are the star (*). So if the UPC is 12345 66666 then the barcoded string is: *12345 66666*

(I think that's correct. Maybe UPC uses check digits, and Code 39 uses the star.)

Code 3of9 uses delimiter characters (* I think).

UPC/EAN codes are more complex and cannot be generated by simply changing the font - they do include a check digit (but that's usually part of the number stored in the table, and easy to calculate, so not a problem), but the symbology also includes grouping algorithms with parity checks- which means that, say, if the number 4 appears twice in the string, it may not be represented by the same pattern of bars in both cases.

 

[aman]

Hi guys

I am thinking of doing something like this . In the following code,the excelsheet will store value of text1 on the top twice and then other values.So the code will open up an excelsheet with some details.

Private Sub Command1_Click()
Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
        Set xlWB = .Workbooks.Open("C:\s3.xls")
    End With
    xlWB.Sheets("sheet1").Cells(2, 6).Value = Text1.Value
   [COLOR=darkslategray] [/COLOR][COLOR=darkgreen]xlWB.Sheets("sheet1").Cells(4, 6).Value = Text1.Value
[/COLOR]    xlWB.Sheets("sheet1").Cells(5, 4).Value = Combo54.Value
    xlWB.Sheets("sheet1").Cells(6, 4).Value = Combo58.Value
    xlWB.Sheets("sheet1").Cells(7, 4).Value = Combo62.Value
    xlApp.ActiveWorkbook.Saved = True

Now the value of text1 is present twice in the sheet and also in the sheet there is print button,Now i want to write a code on that print button in the excel sheet that will convert the second text1 value into barcode before printing.And the when the sheet prints off then it should close the excelsheet.
e.g the excelsheet has Unique ID,Barcode number,Submitted by.
e.g Unique ID=A12345
Barcode=A12345
Submitted By=Aman
and there is present print button the sheet,What I want is when the person clicks on print button then it should print off the sheet by converting A12345 into barcode and in the sheet that prints off should have the following information:
Unique ID=A12345
Barcode=||||.. actual barcode of A12345
Submitted By=Aman
I hope you understand my question. Please let me know if anything is unclear.
Thanks
Aman

 

[bulrush]

Why change the 2nd number to a barcode only when printing? Why not always have it be a barcode, since the actual value of the barcode is always present anyway?

So, the number is already appearing twice, once as a number, once as a barcode. There's no logical reason to change the 2nd number to a barcode only when the user clicks your custom Print button. The second copy of your number should always be a barcode.

 

[aman]

Hi bulrush

My problem is how to print second number as a barcode always on the sheet.When the user gets the printed copy then there should be the unique number and its barcode on the top of the sheet.

I have no idea how it can be possible.

Thanks
Aman

 

[Atomic Shrimp]

Just download and install a free 3of9 barcode font and format the cell in that font.

As Bulrush said, you might need to enclose the value in delimiter characters such as *

 

[aman]

Thanks for all help guys. Its working fine now .

Thanks
Aman