3

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?

Community
  • 1
  • 1
Cosmina Donec
  • 31
  • 1
  • 2
  • Is the data in actual [excel tables](https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c), or just random ranges with data hanging out in the cells? – JNevill Nov 30 '17 at 15:59
  • This is the pb, tha data is not organiseed in an actual Excel table but random ranges with data hanging out in the cells. It looks like a table but it's not a table. – Cosmina Donec Dec 01 '17 at 10:11
  • Brilliant question by the way. I plan to make a tool to solve this – yazz.com Apr 06 '18 at 18:39

1 Answers1

1

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
mooseman
  • 1,997
  • 2
  • 17
  • 29
  • Hi mooseman, thank you very much for your answer, first I wan the info in another worksheet in which i will have something like this: {EF04.03.1,r005,c010} {EF04.03.1,r005,c020} 132151300 – Cosmina Donec Dec 01 '17 at 10:20