VBA to code Entity Relationship diagrams DBCROW_M.vssx

[aussie_neil]

First of all thank you for letting me join your group.  I am by no means a professional programmer and most of my VBA is for Excel but I have done some VBA in Project and Outlook and I now have cause to do some programming in Visio.  I am using Visio Professional 2016.

I am looking at documenting the tables in a database, there are about 40 tables.  Some of the tables are small (meta tables containing 2 attributes) whilst others are quite large (70'ish attributes).  I did not build the database, just documenting it.  Once I have done this one I expect to be doing many others.

All the data is written to a 2D array which works fine.  I have created a number of VBA functions, they work quite well.  Some of the code I have reverse engineered from using the macro  recorder and then adapted for my use.

The first task is to create a blank table.  When this is done it creates a table with 3 attributes.  The object is made up of a number of sub-objects, since some of my tables only have 2 entities I figured it would be easier to remove all the attributes and just start building the table.  I  have written VBA to:

1.  create the "parent" table
2.  a function to identify the highest value object-id (developer|Shape Name)
3.  delete attributes by referencing the object-id
4.  rename the entity to a table name as per my 2D array

Essentially all that is left is the name of the table.  From here I intend to create an attribute and name it looping through my 2D data array.  I have used the macro recorder to create the code so that I can adapt it but this is where I am running into trouble.

Code Select Expand


Sub Macro22()

    'Enable diagram services
    Dim DiagramServices As Integer
    DiagramServices = ActiveDocument.DiagramServicesEnabled
    ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150

    Application.Windows.ItemEx("Drawing1").Activate
    Application.ActivePage.DropIntoList Application.Documents.Item("Stencil0").MasterShortcuts.ItemU("Attribute"), Application.ActivePage.Shapes.ItemFromID(7429), 2

    'Restore diagram services
    ActiveDocument.DiagramServicesEnabled = DiagramServices

End Sub



I have determined that the 7429 value is the object id, I have replaced this with a variable that matches the Developer|Shape Name ID.  The number 2 in this instances refers to it being the second row, or first attribute.  It is worth noting that re-running this macro with the same parameters fails..  If I can't re-run the macro that i have just recorded i can't really use the same code in my main function and expect it to work.. 

The error I get when re-running the macro is "invalid document identifier"

When I move the code into my main section I use:

Code Select Expand


   'Application.ActivePage.DropIntoList Application.Documents.Item("Stencil0").MasterShortcuts.ItemU("Attribute"), Application.ActivePage.Shapes.ItemFromID(7429), 2
    Application.ActiveWindow.Application.Documents.Open ("DBCROW_M.vssx")
    Application.ActivePage.DropIntoList Application.Documents.Item("DBCROW_M.vssx").MasterShortcuts.ItemU("Attribute"), Application.ActivePage.Shapes.ItemFromID(object_id - 14), attribute_number



The (object_id - 14) is a offset that matches the shape name (like 7429), attribute number is my variable (like the 2).

As I have progressed on troubleshooting I added Application.ActiveWindow.Application.Documents.Open ("DBCROW_M.vssx")
The error I am currently getting is "cannot create object".


Any suggestions most welcome!

[Surrogate]

I can't understand what do you want to do with mastershortcuts property ?

[wapperdude]

Adding to Surrogate...
1) ItemU doesn't appear to be a valid property.
2) MasterShortcuts reads, doesn't write.  See both:
         https://docs.microsoft.com/en-us/office/vba/api/visio.master
         https://docs.microsoft.com/en-us/office/vba/api/visio.mastershortcuts
3) if you want to create a shortcut see: 
         https://docs.microsoft.com/en-us/office/vba/api/visio.master.createshortcut

[aussie_neil]

I have no idea what MasterShortcut does, as I stated above it comes from a second macro that I recorded when adding an attribute.  I was hoping that the guts of the second macro would be enough to get me going into a loop with some variables but not the case.

If ItemU is an invalid property then Microsoft put it there, not me..   

I am not wedded to shortcuts, it was just the code that I was effectively given when recording the macro.  I recorded this macro many times early on when doing my drawing working out object_id's etc, they all had this syntax.. 


All I want to do is add an attribute to the table with variables that I can program in.

Suggestions are most welcome, actual code suggestions even more welcome.

[wapperdude]

Got it!  Lol.  Really strange the macro recorder gave you that.

Still a little unsure what your doing.  You have a database that has a collection of tables that you're trying to document.  Are these tables Excel files or some other software?

By documenting, do you mean that your trying to create a Visio file that represents the database and each table is being replicated as a Visio file?  So basically, your trying to import each tables data into an equivalent table in Visio?

[aussie_neil]

The tables are currently in SQL, I wrote a query that pulled the data out and changed it to look like:

tbl1   col1
tbl1   col2
tbl2   col1
tbl2   col2
tbl2   col3
   ...
tbl40  col1
etc

which I have written to a 2D array.  That bit works fine, no problems at all there.

I then create a standard table object which includes 3 attributes, I delete the attributes and rename the header tbl1.  I then need to add the attribute (hence the problem) and name it col1.  It will then go into a loop, if the tblx is the same as the previous add attribute and name it col2.

The process repeats but this time tbl2 is not the same as tbl1 so creates a new table object, delete, add, repeat.  All up i have nearly 500 attributes I need to build, which is why i would prefer to program it..


So yes basically trying to import each tables data into an equivalent table in Visio.

[wapperdude]

Ok.  That provides a better understanding.

Perhaps another forum contributor will pickup on this as I've never dealt with either SQL databases nor Entity diagrams.  Based upon my ignorance, for what it's worth, I would've loaded the info into Excel, inserted the Excel worksheet into Visio.  Perhaps a few more steps, but Excel is so easy to manipulate, for me, with my present understanding, more straightforward. 

Sorry, can be of more immediate help.  But there are other members quite knowledgeable in this area.