Father forgive me.  It has been two weeks since my last blog post.  I apologize everyone for the long interval but things have been crazy.  In addition to some long nights at my day job, I am in the process of releasing a new version of InspectionXpert, the Quality Inspection Sheet generating software from my company Extensible CAD.  It is a rewarding endeavor but also very demanding.

 Anyways… not so long ago I posted a Macro & tutorial on how to register for SolidWorks events.  One application of that technique that I mentioned was to automatically generate document numbers for your SolidWorks document and insert them into SolidWorks “auto-magically.”  Well, not too long thereafter I was bombarded with emails asking me to post that little Macro and I promised to do so.  This post is the fullfillment of that promise.  I’m sorry it took so long but I think you guys will like it.  I feel it is “auto-magically delicious” myself : )

 You can download the database and the macro from here.

Keep reading to see a flash video of the macro in action…

 The Macro In Full Effizact

Disclaimer:  As I am pretty pressed for time, as usual, I have not added any error handling to this macro.  You will have to do that yourselves.  At some point in the future (here I go making promises again), I will add a post on error handling with VBA. 

What this Macro does:  If you didn’t understand the movie then (please check your home’s water for lead contamination) this macro registers with SolidWorks to receive notification (aka ‘callback’) when a new file is created in SolidWorks (i.e. File, New).  The official Event is ‘FileNewNotify2′ and when fired, the macro will determine the type of file (e.g. SLDASM, SLDDRW or SLDPRT) and then use ActiveX Data Objects, ADO, to contact an MS Access database to find out the next available document number for that document type.  The document number is then written to the new document title, in essence the filename, and then the number in the database is incremented by one so that next time the document will have a unique number.

 How Do You Set It Up?

1.  Create a database (I used VBA and MS Access because they are friendlier for newbs but SQLServer Express would be more appropriate for a production solution) that contains three colums;  a primary key (i.e. unique identifier for each record), a lookup key (we used file extension) and a column to hold the next number in sequence for each record-type.  In other words, we have a number series for each file type and the number gets incremented by one everytime a number is assigned.
2. Determine the connection string to connect to the database.  The easiest way to do this is with a little trick I picked up from an awesome SQL Server book (name escapes me right now). 
   1. First you create a new text file and then you change the extension from .txt to .udl
   2. Double-click on the file to open it and you will be prompted with a dialog that you can use to test and obtain the connection.  It is a completely un-intuitive GUI but it is better than the typing it by hand.

   Getting the Conection String To The Database
   Here is a link to a flash video showing how to get the connection string.  I tried to add it to the post but I can only have one flash in the same post with the flash video player that I am using.  FYI – I tried a few flash players but I’m not that happy with any of them.  If anyone is aware of a stellar flash plugin for wordpress, then please suggest it. 

   3. Write a macro that connects to the database using the connection string gained from step #2.  To get the connection string, you just open the .udl file in notepad and copy out the text in the last row.  The macro should obtain an ADO Recordset object from the document numbers table.  I used a query as my filter but you there are other ways.  The query is the simplest.  I designed my query in Access’ query builder GUI and copy/pasted the text from there to the macro.
   4. Test the macro and verify that it is retrieving the correct value (i.e. from the correct row and column) and that it is correctly incrementing the value for next time.
   5. Once you are correctly accessing the database and obtaining the correct information, write a SolidWorks macro to register for notification of the File, New event.  It is officially known as the FileNewNotify2 event.  If you don’t know how to do this, then see my post on registering your macro to receive event notifications from SolidWorks.
   6. Once your new macro is getting called reliable for the FileNewNotify2 event, copy the code from the first macro (the database macro) into the new macro.  Add the database function so that it is called by your FileNewNotify2 event handler.  The new macro should then copy the document number returned from the database function to the new document’s title.

   That’s it.  That wasn’t too bad now was it?

   Note:  If anyone wants to turn this into a SolidWorks Add-in then you can post it here for the masses.  I will give you full credit.  I am too busy right now to do that (unless it was for money :  )

If you enjoyed this post, make sure you subscribe to my RSS feed!