none
Help with VBA Looping RRS feed

  • Question

  • Hello. I have a list of items stored in a database that I export to Excel. The list is a combination of parent items & child items. It is organized like this:

    ITEMS                   ID            PARENT ITEM ID

    Cars                       1552       1549

    Fruit                      1556       1549

    Trucks                   4136       1549

    Malibu                  1749       1552

    Taurus                  1628       1552

    Cherry                  2214       1556

    Grapefruit           2240       1556

    Orange                 1550       1556

    F-150                     1557       4136

    Silverado             4144       4136

    Because of how the data is stored, I can't export it hierarchically. I'm currently using a separate Do While loop for each category, which is not efficient. Each loop copies the name of the category into column E then loops through column C to find each matching child item. The list is relatively stable so I don't necessarily have to allow for a variable number of parent or child items. Does anyone have any ideas of an efficient way to compose the list like this:

    Cars
    Malibu
    Taurus
    Fruit
    Cherry
    Grapefruit
    Orange
    Trucks
    F-150
    Silverado

    Monday, July 11, 2016 4:14 PM

Answers

  • Not clear what you mean with the above comment.  What tables do you have.  Here is some SQL that will give you a single list from what you described originally.  It is sort of ugly and you can improve on it.  I did it in Access. Table called Items

    Columns: Item, ID, PID

    SELECT Item2, PID FROM(
    SELECT '*' & Item AS ITEM2 , ID AS PID from ITEMS WHERE PID = 1549
    UNION ALL
     SELECT Item as item2,  pid  FROM Items WHERE  PID IN (SELECT  ID FROM Items WHERE PID = 1549) )
    GROUP BY PID, ITEM2





    • Edited by mogulman52 Monday, July 11, 2016 8:53 PM
    • Marked as answer by VBA3522 Wednesday, July 20, 2016 8:14 PM
    Monday, July 11, 2016 8:38 PM

All replies

  • What database are you using?  There may be a way to do this in SQL.
    Monday, July 11, 2016 6:18 PM
  • Thank you for the reply. It's an MS SQL database. The values are stored in separate lists based on the parent item ID. So there would be 1 list for the items in bold then a separate list for each set of child items.

    Monday, July 11, 2016 6:43 PM
  • Not clear what you mean with the above comment.  What tables do you have.  Here is some SQL that will give you a single list from what you described originally.  It is sort of ugly and you can improve on it.  I did it in Access. Table called Items

    Columns: Item, ID, PID

    SELECT Item2, PID FROM(
    SELECT '*' & Item AS ITEM2 , ID AS PID from ITEMS WHERE PID = 1549
    UNION ALL
     SELECT Item as item2,  pid  FROM Items WHERE  PID IN (SELECT  ID FROM Items WHERE PID = 1549) )
    GROUP BY PID, ITEM2





    • Edited by mogulman52 Monday, July 11, 2016 8:53 PM
    • Marked as answer by VBA3522 Wednesday, July 20, 2016 8:14 PM
    Monday, July 11, 2016 8:38 PM
  • Thank you mogulman52. I think I was focused on a VBA solution because I have so much other VBA code in this particular Excel workbook. I should have considered SQL a little more especially since it will be faster than using the VBA code that I currently have.
    Wednesday, July 13, 2016 12:45 PM
  • I always look for an SQL solution first unless a code solution is immediately obvious.  You can create a view to make this easy.  For complex ones stored procedures are good.  If it was helpful mark it answered.
    Wednesday, July 13, 2016 3:50 PM