+ Reply to Thread
Results 1 to 12 of 12

Create hierarchy using VBA objects

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Create hierarchy using VBA objects

    Hi,

    this is continuity from here:

    https://www.excelforum.com/excel-pro...atributes.html

    I have 3 objects, parking, cars and drivers on diagram.

    And how to build hierarchy using for example dictionary?

    Parking is the highest object and can have multiple childs objects like cars. And each car can have one or more drivers assigned.

    Parking --> car --> Driver

    And now in code i will be looping through all objects on specific diagram.
    1. Starting from drivers i will find car attached to specific driver
    2. NExt i will Find parking assigned to this car
    2. Next i will loop though specific parking and get all cars.

    So in different loops i have to have mechanism which will check if specific object is already assigned. And place it properly within hierarchy.

    How to avoid repeating data and use objects like dictionary to check if specific car was already used.
    And if new driver is found check if it is already assigned to specific car, and if yes do not assigned it. If it is assigned to other car, add new car to dictionary and specific driver for it.

    I proposed dictionary but maybe there is other way...

    Please help,
    Best,
    Jacek
    Last edited by jaryszek; 12-03-2018 at 09:02 AM.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Create hierarchy using VBA objects

    How is your data laid out? We need a workbook really

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create hierarchy using VBA objects

    problem is that i have this data in Visio document.

    And this is universal problem but there on visio forum there is not help and smart guys like here

    Best,
    Jacek

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Create hierarchy using VBA objects

    I know nothing about the Visio object model, if, as you say you’re looking for something generic; this is the principle https://www.excelforum.com/excel-pro...ml#post4971507

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create hierarchy using VBA objects

    hi Guys,

    thank you very much for help and sorry for my late answer.

    Screenshot_6.png

    i want to use example as attached to show you what i mean.
    I changed cars and parking to student and classes to make it easier to understand.

    So i have Entity (table), Attribute and Connetion (relation) between tables.
    These are standards objects for relational database.

    Ok, steps:

    1. Loop through all connections in "CONNECTIONS TABLE".
    2. For each connection loop through "ENTITY TABLE" and get all other Attributes. Avoid duplication of attribute here.
    And get Constraint , if exists, (like primary key) to each attribute.
    3. Output all into different sheet as pattern in attached Excel.

    What problem i have that i do not know how to build hierarchy here.
    So have entity-->attribute-->relationship but how to avoid duplicates and if some attribute was find add constraint to dictionary for it.

    I can have dictionary with Attributes and dictionary with Entities and for each Attribute i would add Constraints but what about Source Entity.
    Maybe i should have 3 dictionary to add only source and target attributes together with relationship table (connections table) to it?

    Or i am thinking to much and should have 3 dictionaries separately:
    One for Entities
    Second for Atributes
    Third for Connetions

    but this seems to easy and not optimal way.

    Please help,
    Jacek
    Attached Files Attached Files
    Last edited by jaryszek; 12-10-2018 at 05:24 AM.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Create hierarchy using VBA objects

    You haven't really given us enough to go on. Your hierarchy of objects will likely be dictated by what you want to do afterwards - so what's the goal here, what do you want to do when you have this object model?

    Another observation, this is likely to get very complex very quickly, you've posted a very simple entity relationship, but in reality relationships are much more complex. For example, how should the model look when you have a one-to-one or a many-to-many or even an entity that references itself so has a one-to-many back to itself, you'll have to set recursion limits etc.

    This isn't a simple task and nowhere near as simple as most people ask about. Visual Studio does this with vast amounts of code, look at the T4 code generation templates to convert edmx files into POCOs - it's a bit scary and it's starting from xml which is much easier than multiple Excel tables.

    AFAIK visio will generate database models from the schema of a database if that's all you're trying to do
    Last edited by Kyle123; 12-10-2018 at 05:35 AM.

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create hierarchy using VBA objects

    Kyle123 i see that you are a very smart guy, thank you!

    Goal is to loop through visio object and get the hierarchy.
    And after that i want to output result into Excel like you see.

    Another observation, this is likely to get very complex very quickly, you've posted a very simple entity relationship, but in reality relationships are much more complex. For example, how should the model look when you have a one-to-one or a many-to-many or even an entity that references itself so has a one-to-many back to itself, you'll have to set recursion limits etc.
    Good point. If you have more than one type of relationship it will be caught in first loop through relationships and simple add one more row.

    Screenshot_7.png

    Please Login or Register  to view this content.
    Can you help with more details?

    Yes i am trying to move Access database to visio and i was doing this manually.

    Best,
    Jacek
    Attached Files Attached Files

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Create hierarchy using VBA objects

    Have a look here: https://support.office.com/en-us/art...c-d1b35719fc93

    or here: https://support.office.com/en-us/art...fice_2016-2019

    I don't use visio so I can't advise on the licencing
    Last edited by Kyle123; 12-10-2018 at 08:26 AM.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create hierarchy using VBA objects

    ok thank you Kyle123,

    i will look at this.
    I am looking for Crow's foot diagram method.

    But returning to topic,
    still i have to find work a way to output data into Excel as hierarchy data using dictionaries or other objects.

    So please help,
    Best,
    Jacek

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Create hierarchy using VBA objects

    Ok, I fear we are wasting our time here. I very much doubt anyone can help you without the source (visio?) file - I certainly cannot.

    Ultimately I have no idea what you are trying to do. Excel is not a drawing tool, Visio is, visio will create entity diagrams from an existing database. You haven't articulated what you want to do with this object model, or indeed where Excel comes in.

    As I understand it, you have a visio document and are trying to create a series of tables in Excel - what does this have to do with creating an object model?

    You need to do 2 things to get any help:

    1. Provide the source data - I think this is your visio file
    2. Provide the actual output you want from this visio file

    Even if you provide these, due to the reasons I posted above, I think it's unlikely you'll find someone to help due to the complexity of the task, they'd need to be well versed in VBA and the visio object model

  11. #11
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create hierarchy using VBA objects

    Yes,

    it will be difficult to find someone's who knows Visio and Excel but this is more how to create objects to achieve what i want using Excel.
    Not Visio.

    Goal is to output Visio Diagram to Excel to have clean view how database model is looking.
    And in next step to upload Excel file to Visio again with modifications.

    But here we have only first part of task.
    I know how to get to objects in Visio which i attached to sample Excel.
    It is no different where you have objects.

    In attached Excel you see how loops are going and i need only technical solution how to do it.

    1. Looping through relationships
    2. Finding attribute for this relationship in source object atribute
    3. Loop through specific entity for source object atribute and add to dictionary only not existing attributes.
    4. Find relationship in target object
    5. Loop through entity and add to dictionary where attribute it and not duplicated

    So checking relationship-->attibute-->entity to avoid duplicates and add them properly.

    Best,
    Jacek
    Last edited by jaryszek; 12-10-2018 at 08:54 AM.

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create hierarchy using VBA objects

    Anyone?

    How to organize dictionary?

    Like Dic Parent --> Dic Child 1 --> Dic Child 2?

    Best,
    Jacek

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do I convert column hierarchy to flat file hierarchy
    By tbucki1 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-21-2016, 09:33 PM
  2. how to create (and use) an hierarchy
    By apolloni in forum Excel General
    Replies: 8
    Last Post: 04-09-2015, 07:56 AM
  3. Formula or VBA to fill down a hierarchy to create a flat file
    By bmb163 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2012, 09:34 AM
  4. Hierarchy Indented List, Table, Column and Value - need to create uniqueid column
    By mcolli01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2012, 01:09 PM
  5. Is it possible to create sub for the same event but for several objects??
    By etminasm in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-15-2010, 04:21 AM
  6. How to create selction in an Hierarchy?
    By dreams in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-11-2009, 06:46 PM
  7. Create Hierarchy in Excel
    By rajeshkamath27 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-19-2008, 11:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1