Insert random bar code 39 in more than thousand Visio drawings files

Started by sunnyimran, November 08, 2018, 11:14:54 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

sunnyimran

Hello, My first post here.
Sorry if it is in wrong board

I have designed a Visio 2016 drawing which has eight A3 landscape pages.

I need to insert bar Codes in Code 39 format at four specific places on each A3 sheet. Each bar code will have few characters as static and few generated at random within a series of given numbers. Human readable text is also required at a separate box right under each bar code. That finishes printing of one Visio drawing.

Then I need to generate and insert a new set of bar codes as per the scheme stated above in my Visio file and then get it printed. Similarly I need to get more than thousand copies each with bar codes inserted as per the scheme stated above.

I am trying to match concept similar to MS office mail merge. But mail merge directly would would not work in Visio.

There are numerous ready made (PAID) bar code Add-ins or other stuff. But I want to solve it in-house.

Please if someone can hint how to open a completed Visio drawing, insert code 39 bar codes in it and save/print each copy uniquely.

waiting for you suggestions.

Regards

metuemre

You can use a free Code 39 barcode font like this one https://www.barcodesinc.com/free-barcode-font/.

It is also possible to automate all the things you want. It will be better if you can provide a sample visio file with the locations and content of each barcode.

sunnyimran

Thanks for responding metuemre

I have attached a sample Visio file. When printed and center stapled, it turns into an A4 portrait book having 32 pages printed front and back. In Visio file, each page is A3 landscape having center fold in the center which visually divides A3 Page into two A4 portrait pages. On each A3 page there are four specific locations where bar codes 39 has to be inserted. The bar codes placed in sample are generated outside of Visio just to create the sample.

I had to remove following pages from sample to overcome file upload size limit of 500KB
P20 and P13   Deleted
P22 and P11   Deleted
P12 and P21   Deleted

On a batch run, I need to open Visio file, insert bar codes as per given scheme, save (with a different name so original in intact) or print or both. Then open the main Visio file again insert new bar codes on set locations as per given scheme then again save/print. Similarly more than thousand copies of this 32 paged book are required.

Bar code 39 scheme explanation
Bar codes at the top of A4 pages and bottom of the page are same. Except that at bottom of page under barcode there is readable text at a given distance from bar code.

Each A4 page will have a new unique bar code according to the following scheme:
Bar Code Scheme:
SAMPLE BAR CODE TEXT( Code 39):   * 01-MS0123456789-A1*
Start and end character asterisk '*' is required to be included in bar code but optional to display in readable text.

Some barcode examples Page wise:
----------- START of Book -----------
Page:1      01-MS0123456789-A1
Page:2      02-MS0123456789-A2
Page:3      03-MS0123456789-01
Page:4      04-MS0123456789-02
Page:5      05-MS0123456789-03
.
.
.
Page:30   30-MS0123456789-28
Page:31   31-MS0123456789-29
Page:32   32-MS0123456789-A3
----------- END of Book -----------

format for one 32 paged book is as follows:
Bar Code text digits:
First two digits :                 numbers from 01 to 32
Third digit:                         hyphen sign '-'
Fourth and fifth digits:       characters (in sample 'MS')
Sixth to fifteenth digits:     numbers (select randomly from a given start number and end number set)
sixteenth digit:                  hyphen sign '-'
seventeen and eighteenth digits:   
                                        On first page:                  A1
                                        On second page              A2
                                        On third page:                 01
                                        On fourth page:               02
                                         .
                                         .
                                         .
                                        On page 31:                     29
                                        On page: 32:                    A3

More than thousand books print out will follow above scheme
In all cases bar code remains unique everywhere.
User may ask to changes in bar codes static and random series parts in future.

Now please guide my direction for an in-house solution.

Waiting for response

metuemre

Hi sunnyimran,

I made a sample application as per your explanations. First you need to install fre3of9x.ttf file which is a free Barcode 39 font. Then you can open Barcode Template visio file. I added a custom Batch Print tab and button in the ribbon. You will just enter the number of copies to create and select a location to save the files. Rest will be handled automatically. You can adjust the location of barcodes in the code if necessary.

You can also move the code to a visio stencil if you dont want to spread the macro to anyone else.

Feel free to ask if you face any issues.

Regards
Emre

sunnyimran

metuemre, you are great. Thanks for the time you have given to my task.

I will test it out.
But here comes a little change in bar code format. Now instead of Code 39, I am required to use Code 128.
I also need to squeeze the height of bar code to adjust inside top and bottom margins along with other stuff. I hope there are VBA commands for setting the height of the object.

Bar code string: 01-MS0123456789-A1
At this time my string to code is alphanumeric with only one symbol '-' hyphen. 0 to 9 numbers and uppercase letters. For making requirements of the main string future safe , I believe 128 B (Code Set B) would be the right choice:
it includes upper and lower case letters and symbols. While Code A 128 has only upper case with other things.

Code 128 B (Code Set B) – ASCII characters 32 to 127 (0–9, A–Z, a–z), special characters, and FNC 1–4

More info about Code 128
https://en.wikipedia.org/wiki/Code_128

I value your time, please if you can modify the template for Code 128 that would be great.
or otherwise give me some hints on how to do that now. I hope it requires minor changes. While I would spend days you can do it in minutes.

Thanks for the favor

Regards


metuemre

sunnyimran, you are welcome buddy.

I had some free time today so attached is the Code 128 version of the template. First you need to install IDAutomationSC128SDEMO.ttf font file. You can play with the font size of the barcodes to change their heights if required.

Feel free to ask if you face any issues.

Regards
Emre

sunnyimran

That's very great from you.  :)
I will give it a deep test to solve my situations and update you about it.

Can I ask you a question please?

Can we create an object in Visio 2016 like a text box and import its contents from Access database repeatedly?
Just for my situation I got this idea to create bar code strings in Access and import them in visio inside their specific text boxes. This will be very helpful to adjust be barcode string in access by using VBA. As a result, VBA script in Access will display UI to choose and customize the string. While the format of string will remain same as static parts and random parts.

That would be a bonus for my situation if it works. Because by using Access I can keep record of previously used strings and selecting new ones would be easy through UI.  For random part of the string I will be told for example print 1,500 copies of the 32 paged book with random bar code range starting from: 0123456789 to 01234568289
next time I will be given another range. to print said number of copies.

So possible inserting Access stuff in Visio somehow?

and How do we store bar code themselves in Access? I found no datatype except in picture format.

Many thanks for my work. great Visio Guy.

Regards

metuemre

Anything is possible if you know how to do it ;) There are many examples of importing access data to excel with VBA. Below are two examples that shows the basics of connection strings to an access database. You can use them as starting point and develop the rest of your code for your needs.

https://www.askeygeek.com/vba-code-to-import-access-tablequery-data-to-excel/
https://www.exceltip.com/import-and-export-in-vba/import-data-from-access-to-excel-ado-using-vba-in-microsoft-excel.html

sunnyimran

Thanks for all the help.
i will checkout the links you provided for Access connectivity.

Visguy as you may know I am not at comfortable level in Visio. So I need to ask you simple questions time to time.

So here is a simple question:

Q. How can we create a shape / text box /Ruler guide line or any other object which is one our first page of the Visio drawing to all subsequent pages in one go. I mean all pages have exact same shape, on exact same location with same dimensions as on first page.

I tried several times using macro but failed :(

Please guide.






metuemre

You can use selection.copy paste method. Select all the shapes that you want to duplicate on other pages and run the macro below. It will copy all the shapes and paste them on other pages and keep their location same as the original.

Sub CopyPaste()
Dim pag As Visio.Page
Dim vsoSel As Visio.Selection

Set vsoSel = Application.ActiveWindow.Selection
vsoSel.Copy visCopyPasteNoTranslate

For Each pag In ActiveDocument.Pages
    If pag.Index <> vsoSel.ContainingPage.Index Then
        pag.Paste visCopyPasteNoTranslate
    End If
Next

End Sub

sunnyimran


wapperdude

Depending upon how you use these shapes, you can move them to a background page, and then have each drawing page reference that background.  This would greatly reduce file size.

Wapperdude
Visio 2019 Pro

sunnyimran

ok another good tip.
I will create a page with all the static shapes (that should appear in each page) and save it as background. Then attach this background to all other pages.

Can I include text boxes for bar codes in background page? I think No since the value string will change on every page.
So is it possible to put all bar code related objects in a separate layer and still be able to automate?

Regards



Yacine

Quote from: sunnyimran on November 19, 2018, 05:24:29 PM
Can I include text boxes for bar codes in background page? I think No since the value string will change on every page.
Actually, yes you can. Watch this post: http://visguy.com/vgforum/index.php?topic=1717.0
Yacine

sunnyimran

Thanks guys for your work and suggestions

I will update you about my project's progress in few days

This is really a helpful forum :)

Regards