IT

List Group Level (MS Excel)

(30 Aug 2018)
Aims to get the Group level within a worksheet in different MS Excel files under a same foler

Sub listGroup()
    Dim mRef As Worksheet, wbMaster As Workbook
    Set wbMaster = ThisWorkbook
    Set mRef = wbMaster.Worksheets("Ref")
    
    'A worksheet that contain a number of data address
    Dim fPath As String, fFile As String
    Dim nAna As Integer, anaRow As Integer, anaCol As Integer
    fPath = mRef.Cells(4, 2)
    anaCol = mRef.Cells(6, 2)
    anaRow = mRef.Cells(7, 2)
    nAna = mRef.Cells(5, 2) + anaRow - 1

    For f = anaRow To nAna
        fFile = fPath & "\" & mRef.Cells(f, anaCol)
        Workbooks.Open (fFile)
        For i = 6 To 50
            Cells(i, 1).Value = ActiveSheet.Rows(i).OutlineLevel
        Next i
        'Save the File without asking
        ActiveWorkbook.Close Savechanges:=True
        'Clear the Variable
        fFile = ""
    Next f

End Sub