Displaying flat file data in a specific hierarchical format in XL

[Rank Am]

Hi not sure whether I should post this in XL or VBA forum

I have attached an XL file, this shows a randomly ordered List of TAG Nos and the TAGS Parent TAG. This is in an MDB file in a single table. The two columns are PK_TAG - Unique index of TAG Nos and Primary Key of the Table. FLD_PARENT shows and individual TAG immediate parent item and is a lookup value from PK_TAG.
Columns D to H on the XL sheet show how the client wants this displaying on the worksheets.

I should know how to do this but can't get this working. I can transfer the data from Access to XL via copyfromrecordset method but cannot get the coding right to show this in the hierarchical format required once in XL.

Does anyone have a procedure that can acheive this for me

Regards

Jon

 

[gemma-the-husky]

if you have to get this into excel like that, then i think you either need to try and construct an access query that looks like this - or maybe populate a temporary table using several access queries.

put it the other way - if you cant achieve it in access, you wont do it in excel either

===========
another thought - try producing a report with no headers/footers etc, and see if you can export the report data

 

[Rank Am]

Thanks Dave,
I got round this by cheating, I used a Treeview control on a form embedded on an XL worksheet.
The following procedure worked to recursively fill the treeview. The table has 14000 records so it takes a few seconds to populate.

Public Sub PopulateTheTreeview()
'This sub Recursively populates the treeview control
Const strTable = "BUILDER"
Dim db As DAO.Database
Dim RS As DAO.Recordset

Set db = CurrentDb
Set RS = db.OpenRecordset(strTable, dbOpenDynaset, dbReadOnly)

AddBranch RS:=RS, strPointer:="PARENT", strID:="PK_TAG", strTextField:="DESCRIPTION"

End Sub

Private Sub AddBranch(RS As DAO.Recordset, strPointer As String, strID As String, strTextField As Variant, Optional varParent As Variant)
'On Error Resume Next
Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, varText As String, strKey As String
Dim nodParent As Node, bk As String
Set objTree = Me.TRV_AREG.Object

If IsMissing(varParent) Then    'Root Branch
    strCriteria = strPointer & " Is Null"
Else                            'Search for records relating to the parent node
    strCriteria = BuildCriteria(strPointer, RS.Fields(strPointer).Type, "=" & varParent)
    
       Set nodParent = objTree.Nodes(CStr(varParent))
End If

'finf the first item under the parent
RS.FindFirst strCriteria
Do Until RS.NoMatch
    varText = RS(strID) & "----" & "(" & RS(strTextField) & ")"
    strKey = RS(strID)
    If Not IsMissing(varParent) Then    'add new node to the parent
        Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, varText)
    Else                                'add new node to the root
        Set nodCurrent = objTree.Nodes.Add(, , strKey, varText)
    End If
    bk = RS.Bookmark
    AddBranch RS, strPointer, strID, strTextField, RS(strID)
    RS.Bookmark = bk
    RS.FindNext strCriteria
Loop

End Sub

I think the client wants to see what the hierarchy will look like when populated in their system so hopefully they will be happy with this. If the client is adamant about having it populated in the spreadsheets. I am pretty certain that I could acheive this by adding in some code to write to a variant array as it is populating the treeview and then copying the array to an XL sheet. Not very efficient but will only need to be used once - I reckon it would be very awkward to do this using SQL.
BTW I would like to take credit for dreaming this up but I got the bones of the code from the msdn website.

Regards

Jon