如何在Excel中自动检测表格 [英] How to automatically detect tables in Excel

问题描述

我有一本工作簿，其中包含约40张桌子.这些表在每个文件中都非常混乱，因此您永远不知道表在工作表中的位置.在某些工作表中，可能甚至有2或3个表.此外，表的结构非常混乱，因此您无法知道行索引或列索引始终在何处.我确实需要遍历所有工作表并首先提取数据，然后为每个单元格计算单元格代码，该单元格代码是行索引，col索引和表标题的组合.

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.

所需的输出应如下所示:这意味着，在表04.03.1的行005列010中，我有数据:132151300

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

我使用VBA和命名范围来做到这一点，但是问题是每次我收到一个新文件时，我都必须再次为所有40个表定义命名范围. 新文件的结构可能与我已经定义的文件不同，因此我不能简单地将范围从一个工作簿转移到另一个工作簿.

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.

关于如何自动识别内容"和"col索引/行索引"的任何建议?或任何其他解决方法?

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

推荐答案

下面是一些代码，用于遍历工作簿中的每个工作表，然后遍历每个表中的每个单元格.该消息框提供了工作表名称，表范围，表名称，单元格的值，行和列.您没有说明要去哪里，但是可以获取信息，您只需确定要去往何处并将其格式化为您的要求即可.

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

这篇关于如何在Excel中自动检测表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！