CODEWITHSUNDEEP
excelvba
Cosmina Donec
Cosmina Donec

Reputation: 31

How to automatically detect tables in Excel

I have a workbook which contains approximately 40 tables. The tables are very disorganised in every file, so you never know where the table might be located in the worksheet. In some worksheets it might be that you have even 2 or 3 tables. Furthermore, the tables are very unstructured, so that you are not capable to know where will always be the rows index, or column index. I do Need to Loop trough all Sheets and extract first the data, and then to compute for every cell the cellcode which is a combination of row index, col index and the title of the table.

The desired Output shall look something like this: which means, in table 04.03.1, row005, column010 I have data: 132151300

{EF04.03.1,r005,c010}   132151300

I did that using VBA and named ranges but the problem is that every time I receive a new file, I have to define the named ranges again for all 40 tables. The new file might not have the same structure as the one I already defined so I am not able to simply transfer the ranges from one workbook to another.

Any Suggestion about how can I identify automatically the Content and then col index/row index? Or any other workaround?

Upvotes: 3

Views: 4594

Answers (1)

mooseman
mooseman

Reputation: 2015

Here is some code to iterate through each sheet in a workbook and then go through each cell in each table. The messagebox give the sheet name, table range, table name, value of the cell, row and column . You didn't state where you wanted the information to go, but this will get you the information, you just have to decide where it's going and format it to your requirement.

Sub FindAllTablesinWB()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    For Each oSh In wb.Worksheets

    For Each oLo In oSh.ListObjects
        For col = 1 To oLo.ListColumns.Count
            For rw = 2 To oLo.ListRows.Count
              MsgBox "Table: " & oSh.Name & ", " & oLo.Range.address & ", " & oLo.Name & ", " & oLo.Range.Cells(rw, col).Value & ", " & "Row " & rw & " Column " & col
            Next rw
        Next col
    Next oLo
    Next oSh
End Sub

Upvotes: 1

Related Questions