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

Quote from: metuemre on November 16, 2018, 11:45:43 AM
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

Dear metuemre

I am in love with Visio because of your forum support. I got little busy but now back to my Visio project. Your cooperation gives me spark to learn more of Visio.

I have few novice questions. I must google but you can explain in a sentence. Let's discuss about my project detailed in my first post.

Q.1 For displaying bar code on top and bottom of each page you used 'rectangle' shape drawn by your script and its text contains the string for bar code. I started with a text box. Just asking, can't we manipulate a text box and its contents, properties, methods etc. in same way as we can play with rectangle in Visio VBA? Moreover possible to use text box with text strings for displaying bar code.
Although I have no issues using rectangle for bar code but wanted to know about text box manipulation only.

Q.2 When Scripting.Dictionary is populated, how can we display its contents on screen and store in an Excel or Access file?

Q.3 What is the difference among these two:
     Application.ActivePage.Name  and Visio.ActivePage.Name
Experimented few times both gives same results. Then what's the difference ?

Q.4 Here I need your support again. I want to utilize MS ACCESS to generate the random string for bar code and populate Access records with complete series. Then link Visio with this Access database and get the bar code string inserted into 'rectangle' or 'textbox' whatever is appropriate and finally run the BATCH in Visio to generate Visio/PDF files How can this be done give me hints or possibly if you can show by scripting

Many reason for using Access in between.

  • To keep record of already generated bar codes. I will record barcode string for each page in a book and for all books.

    To create user input forms to select the start, end and random portions of bar code string and give user the choice of searching previous generated series.

    More important, your script BATCH needs to be run all in one go. I have to go generate this for thousands of books. So at times I may need to divide work in small batches say 1000 copies then next. So I think if pulling strings from Access. this would not be a problem. I can generate strings in small runs in Access.

I installed Code 128 font "Libre Barcode 128" and seems working fine and simple. I will attach in post.
Font "IDAutomationSC128SDEMO" is a demo version no idea when it may stop working.

I am unable to understand the script you added in Module-2 which contains

'*****************************************************************
'  Visual Basic & VBA Functions for IDAutomation Barcode Fonts V7.10
'  © Copyright, 2000-2007 IDAutomation.com, ...............................

.........................Script continues..............

Q. 5 what is the purpose of this lengthy script you inserted in module2 ?

Hope you will give me some of your time. I am too enthusiastic about Visio now.

sunnyimran

dear metuemre you might be busy

anyone can help me, see above post please.

waiting

metuemre

Hi sunnyimran,

Let me try to answer your questions;

1- Textbox is nothing but a rectangle without borders. You can test it as well. Start macro recorder, put a textbox on a page, write something in it, stop the recorder and check the generated VBA code.

2- It's populated if generated random number is unique. You can see it in RandomNumberGenerate function (dictBarcode.Add)

3- ActivePage is a property of Application object. Full syntax would be Visio.Application.ActivePage.Name but you can just use ActivePage.Name as well. Visio fills in the blanks automatically.

4- I modified previous visio file and added Access connection. VBA code connects to an Access db, check the records in the table named "BarcodeLog" if the newly generated barcodes are unique, then writes back to db with Book Name, Page Name and Barcode. The path to db (Prop.DbPath) and table name (Prop.DbTableName) are stored in Document Shapesheet. You can access it via Developer-Show ShapeSheet-Document. I also added a Db Viewer userform to see and filter the barcodes or other fields in the db.

IDAutomationSC128SDEMO was a demo font but I modified it. You can use "Libre Barcode 128" as well but I didn't test it so I can't guarantee if everything works properly with that font.

5- Module2 contains special encoder functions by IDA Automation. I only used Code128 function but if you require another encoder in future you can use that as well.

Note that I put all the code in a stencil file which is attached. After you batch print, generated visio files will be in .vsdx format and will not contain VBA code.

sunnyimran

Dear metuemre
you are great man!!!  :)  Thanks for giving your time


This is a quick reply just after seeing your responded. I will test the attached things very soon and update you.
I appreciate your help and support for beginners like me. That's the way I do in my area of expertise.


OK Can you answer this for me:


I setup a rectangle in document master and instantiated on several pages with first name from master then 'dot' then added a number in sequence so all belong to master and with unique names. This gives me flexibility to alter the master shape and update all its instances


Q. in an opened Visio document how can I refer to a rectangle (or any other shape) on current active page or another page which is not active using its shape name instead of shape ID and insert text in it or modify its shapesheet contents?


I want to learn how to access any shape on any page using VBA and change its contents like its text or its shapesheet cells contents etc.


One more question
when I 'Save As' a visio doc to PDF by menu or in VBA, the resulting PDF has certain objects little drifted. I mean not exactly same as in Visio itself.


Then i tried Export As PDF in VBA, saved PDF was inaccurate


I then manually tried to print as PDF using MS default PDF printer. that PDF was much better
I was thinking of giving Document.Print Method in a loop for my bulk PDF generation but problem is it asks for file name and path for every iteration.


Q. is this normal behavior that PDF would have some minor inaccuracies?

Thanks a lot for your support and letting me grow in Visio


Warm regards

metuemre

Hi sunnyimran,

Below is an example to access a shape and modify its contents

Dim shp As Visio.Shape

Set shp = ActiveDocument.Pages("Page-1").Shapes("Sheet.1")
shp.Text = "Hello"
shp.Cells("Prop.Test").Formula = Chr(34) & "Test String" & Chr(34) 'It will work if there is a shape data named "Test" in the referenced shape


I didn't face a problem with "ExportAsFixedFormat" method before. If you want to use Document.Print method to save as pdf, it may be possible to automate it but it will be painful. See the sample in the link below. I wouldn't suggest that way.

http://khkonsulting.com/2013/01/prevent-the-save-dialog-when-printing-to-the-adobe-pdf-printer/

sunnyimran

Extremely thankful for your response.
your expertise is amazing, most of the work that I needed and would take me a month, you did it.
Thanks bro  :D I will not just use it, will learn from it. Nice that you added start and end number validation and randomization uniqueness.

As a novice struggles in Visio, here are some queries for you. Grateful that you responds.

Q.1 Can we use 'Shape Name' instead of Shape ID appearing in Shape Name dialog to manipulate a shape?

Q.2 If I want to draw a rectangle of width = 50 mm and height = 10 mm what should I supply to DrawRectangle(X1, Y1, X2, Y2)?
 
                  DrawRectangle(2.61811, 2.519685, 5.925197, 1.976378)
I checked in "size and pos pan" and "shapesheet" nothing matching found to the numbers supplied in DrawRectangle()

MS docs tell:      DrawRectangle(X1, Y1, X2, Y2) where X1, Y1, X2, Y2 are the coordinate of corners of the rectangle's width-height box. But how to calculate these numbers so I can get my width and height of rectangle ?


Location of the rectangle, Controlled by  PinX and PinY that I understood fine.


Q.3 Is it necessary to delete this section visSectionFirstComponent, what is the purpose of this section:
          Call vsoShape1.DeleteSection(Visio.visSectionFirstComponent)


Q.4 Facing problem in manipulating .mdb from script
I tried using script without MDB by converting all relevant VBA commands to comments and the script works just fine. The generated VSD files are script free and that will solve my bulk print problem without disturbing accuracy. Nice Job dear.


But with MDB functionality I am facing problem Here is what I did:
- Removed commented commands and made them active in VBA
- Created a blank new Access Database "mdbTestDB.mdb" in that path (using ACCESS 2016)
- Created a table "BarcodeLog" with following fields
      Fieldname          TypeID
                                 AutoNumber
      Book_Name          Long Text       
      Page_Name          Long Text       
      BarcodeLog          Long Text
(Fields are blank, no records entered.)
- Modified the path for "mdbTestDB.mdb" in doc ShapeSheet.


Now When I start the script it gives debug error:
             ERROR ->   No value given to for one or more required parameters

Does it mean I should put some initial record first in the table?

please clarify how to use MDB feature in your script

Q.5 sometimes Batch Print button sometimes does no activate VBA code.
So I have to go to VBA, Bathcprint() module and run from there. No idea why

Q.6 How to come back to original Visio file after the batch finishes?

As you know. When batch print runs, each new document is generated and then it is saved. That is mandatory. But when the last book is generated. That is the one loaded in Visio instance that time.

Although script has command to close visio but is it possible that when batch completes, Visio instance loads the main document instead of closing application?

A Novice Visio enthusiast's curiosity brings me here. I know these are simple silly questions for you. But please do respond. Thumbs up for you. I learnt a lot new things from this visio experiment and with your support

Warm Regards and best wishes for you dear.

metuemre

You are welcome buddy  ;)

1- Yes you can.

2- Width of the rectangle is x2-x1 and height of rectangle is y2-y1. But the result is in inches, so if your drawing is in metric units, result will be multiplied by 25.4 to convert to millimeters. For a rectangle of width = 50 mm and height = 10 mm, x2-x1 = 50/25.4 = 1.9685 and y2-y1 = 10/25.4 = 0.3937 . Different numbers of x1, x2, y1, y2 will change the location of rectangle.
DrawRectangle(1, 1, 2.9685, 1.3937) or DrawRectangle(2, 2, 3.9685, 2.3937) will give you the same dimensions but PinX and PinY will be different.

3- It is used to delete the lines around rectangle so it will be a text box.

4- I can check if you send me the mdb file

5- If you get an error on VBA, custom ribbon button stop working. You need to restart the visio file.

6- It creates other problems since the code is inside stencil and that stencil is part of template file. It would be better to open it manually

sunnyimran

Dear, I am unable to run this new script from stencil smoothly.
Your urgent help in this regards is highly desirable now as my project is waiting to start.

Thanks a lot for explanation. Now things are getting clearer.

But dear, I am facing issues in batch running.

Manipulating .mdb file is not successful. it gave me several errors. One of which is:
   ERROR ->   "No value given to for one or more required parameters"

Bypassed mdb part
So to bypass mdb part I converted all mdb related portion of your script into comments and saved as a new stencil in: "BatchPrintRemovedDB.vssm" Ran modified script manually from stencil's BatchPrint().

It does generate two or sometimes three target books (vsdx and PDF's). But just after that Visio's main application goes to a non-stop hang. I have to close Visio App from Task Manager.

Bypassed Ribbon and button part
I also tried to bypass the Ribbon button functionality and saved new stencil as:"BatchPrintRemovedDB&Ribbon.vssm"

Visio hangs after generating a max of two or three target books.

Inserted start/stop "*" characters
Thinking there are no start and stop characters '*' in bar code string. so I added them too but no avail.

Tried Debug mode Run
using F8, breakpoints and going line by line, I was successful in five iterations and got five books generated. But when I run the batch for five books, Visio hangs after a two or three iterations.
I think it loops somewhere inside module2 indefinitely.

Choosing correct range for start/end random numbers
I am choosing it carefully so inputted range does not fall shorter than number of pages to be generated.

Barcode 128 font is correctly installted
IDAutomationSC128SDEMO.ttf is installed and working correctly in other apps like excel etc.

Choosen Code128b so Module2 uses another encoder script
but Visio hangs as usual

I am attaching RAR pack that includes:
New "mdbTestDB.mdb"

Stencils that I modified by commenting out portions
"BatchPrintRemovedDB.vssm"
"BatchPrintRemovedDB&Ribbon.vssm"

I appreciable your quick support. Constantly checking your reply for solution
[/size]Best of Regards

sunnyimran

OK here is an update after several hours of trying:

I did following things to get the batch running at least:
Modified the mdb path in Document ShapSheet
Created a completely blank .mdb (2002-2003 database, using ACCESS-2016)
used your stencil "BatchPrint.vssm" without bypassing anything except this line I have to bypass:
           'Open Recordset'
           objMyRecordset.CursorLocation = adUseClient
           objMyRecordset.Open objMyCmd.CommandText, objMyConn, adOpenStatic
---->   'objMyRecordset.Fields("Barcode").Properties("Optimize") = True
if not bypassed, It gives me some run time error.
            "Sort order can not be applied......" (screenshot Attached)

I created completely new mdb file with no records in it. Key field 'ID' was showing (New) only.
error on MDB on first iteration (screenshot attached for new database creation and error)
            "Either BOF or EOF is TRUE or the current record has been deleted....."



Then I pressed tab key and now Key Field 'ID' field changed to '1' while I left all other fields blank (screenshot attached)


Then I ran the script again from start. generating only 1 file
It worked ;D ;D ;D [size=78%] !!!![/size]


I checked the mdb file populated records from ID=2 (means first records was kept blank, fields were populated from record number 2 and onward)




(screenshots attached in a RAR file)


Now as usual I have curiosity to know few things:
Q.1 How to create a brand new mdb database and start it from record 1 properly?


Q.2 Is there a possibility to pause/resume a running batch Or more desperately needed feature: Can I to stop the batch, close the work. Start at later time??


This is most needed dude!!!


In fact generating vssd and its PDF files takes time. I would have to generate eight thousand or more files in a given range of numbers for randomization. Batch may take several hours to complete. During the batch run Power disruption may occur or any other unpredictable situation can disturb running batch. Now since batch is not complete. I have to restart the batch. 


Once I re-start the batch over again, providing same range for random numbers, the batch can produce repeated random bar code numbers which have already been used in first run. Batch run checks for duplication and uniqueness of bar codes during its single run cycle only.


Although mdb would have a record of previous bar code strings, but manually It would be impossible for me to find out which bar code numbers have been used and how to protect them being used again on next batch run with same given bar code range.


Bar code range will be changed after one successful batch with its given bar code range has been successfully completed.


Only solution for this in my mind is to delete all generated files and start over. That would consume more and more hours of batch run without any surety of reaching to end successfully.


Any solution for this please?

You are always helpful and your support is moving my learning curve upwards in Visio and VBA.
Great support bro.

Regards

metuemre

Hi sunnyimran,

There is a problem with your database. I don't have MS Access so I couldn't check in detail. I created my testDB from Visio VBA. I attached an empty database which works on my computer. I also added macros in "Module 3" to create a new database, new table and to delete all records in a table.

I also modified the code slightly for the errors you got before.

Note that Barcode_Template.vstx file is a visio template file. If you double left click to open, it will open a copy of the template. To modify the original template, you need to right click on it and then click Open.

Once I re-start the batch over again, providing same range for random numbers, the batch can produce repeated random bar code numbers which have already been used in first run. Batch run checks for duplication and uniqueness of bar codes during its single run cycle only.
This is not correct since every time a Barcode is generated by the code, it is checked in the database if it is unique or not and if it is unique it is written to the database immediately. So don't worry about duplication.

Lets say you wanted to create 8000 copies and after 1000 copies something happened and your computer is shut down. When you run the batch print again, you will enter 7000 copies with same range and code will handle the rest to make sure that barcodes which are used in 1000 copies or previous are not used.

sunnyimran

Dear metuemre :)


Extremely Thankful for your time and support.


reading your reply seems exactly what I wanted.


Let me try that and update you soon.


I feel embarrassed that most of my work you have to do an I only need to understand it.


But that's one way of learning for a novice Visio enthusiast like me.


frankly the support and explanation I am getting here does not equal googling split material and getting more confused


great thanks. update you soon


have a nice day :D






sunnyimran

Greatly thankful. Script is now started working with me
but right after 2 or sometimes 3 copies, it gives me Error
    Error, DOS Sharing violation

screenshot attached

can't figure it out


Please help, things are on final stages now.


Best Regards



sunnyimran

Thanks metuemre  :)


Google is pointing to Windows Defender (Exclusion of Visio files)
i tested running batch on another PC where Defender is disabled, didn't got this error.


But dear, one issue still not resolving


When I give a range for random numbers (start number and end number) and the range is somewhat near the number of pages generated than
ISSUE -> After generating two or three files, Visio hangs forever

For example:
Number of books to generate = 5   (each book is 32 pages so 160 pages total)
Start range number = 100
End range number = 300
Difference is 200 that is somewhat nearer to total number of pages.
-> Script will hang after two or three iterations.



Number of books to generate = 5   (each book is 32 pages so 160 pages total)
Start range number = 500
End range number = 1000
Difference is 500
-> Script finishes iterations completely and successfully


I have to use range for Barcodes carefully, as it will be frequently used (same range will not be re-used) but in separate batches starting from lower numbers range to higher numbers ranges.


Suspecting database issues:
I have tried generating mdb from you script completely from scratch (perhaps it generates Access 97 compatible format, I have no utility to manipulate this mdb. I am on Office-2016)
Visio hanging problem exists


I have also generated mdb (Access 2000 format using Access 2016)
Visio hanging problem exists still


You supported so well that I am almost 95% done with my project. only this Visio hanging problem exists now.
Hoping you will look into this matter


Thanks dear
Regards

sunnyimran

Thanks metuemre   :)  and everyone else on this forum.


I am proceeding to my project in phases. metuemre hints and samples codes became the foundation for me and by digging out here and there and learning more new ways, I am achieving my required stuff in stages.
[/size]
[/size]This forum is really helpful. Great :D




[/size]
[/size]