Excel VBA - 检测单元格边框或备用解决方案

I am having a logic issue and I think the only way to deal with it is by detecting cell borders. Is there a way to do this in Excel VBA?

我有一个逻辑问题,我认为处理它的唯一方法是检测细胞边界。有没有办法在Excel VBA中执行此操作?

background

I am working on a project that pastes a Word table into an excel sheet. However, I have found that line breaks in the Word table separate the text into separate cells merged together. VBA does not like merged cells so I've added a line of code to unmerge all cells in the sheet. The result is that some cells from the Word table have been spread out over multiple cells, adding new rows here and there. I've been writing VBA code to consolidate the separated cells back into one using a one line "ID" column key from the Word document as a marker to section off the actual table cells.

我正在开发一个将Word表格粘贴到Excel工作表中的项目。但是,我发现Word表中的换行符将文本分成合并在一起的单独单元格。 VBA不喜欢合并的单元格,所以我添加了一行代码来取消合并工作表中的所有单元格。结果是来自Word表格的一些单元格已遍布多个单元格,在此处和那里添加新行。我一直在编写VBA代码,使用Word文档中的一行“ID”列密钥将分离的单元格合并为一个,作为标记来分割实际的表格单元格。

the issue

When I paste a table with another table nested within a cell, it adds a number columns and rows corresponding to that inner table. I can't add images yet, so i'll draw it out here (sorry). Each separate line is a new row.

当我将一个表与另一个嵌套在单元格中的表粘贴时,它会添加与该内部表对应的数字列和行。我还不能添加图片,所以我会在这里画出来(对不起)。每个单独的行都是一个新行。

"Header1"                    l    "No Header"            l   "Header2"
"Cell text prior to table"   l    "(blank)"              l
"Table (1,1) text"           l    "Table (1,2) text"     l
 Problem text 1              l    Problem text 2         l
"Table (2,1) text"           l    "Table (2,2) text"     l
"Cell text after table"      l    "(blank)"              l

Edit: I should clarify that I want the consolidated cell to show

编辑:我应该澄清我想要整合的单元格显示

Cell text prior to table

表格之前的单元格文本

Table (1,1) contents Table (1,2) contents

表(1,1)内容表(1,2)的内容

Table (2,1) contents Table (2,2) contents

表(2,1)内容表(2,2)内容

Cell text after table

表后的单元格文本

Etc...

I cannot identify whether or not "Problem text 1" and "Problem text 2" were originally part of (1,1) and (1,2) separated by a line break, or if they are an entire new row in the table. The only way i can think of is to identify cell borders, (as the formatting for the nested table is carried over from word) and write a million nested "if clauses" to identify where cells need to have their values added.

我无法确定“问题文本1”和“问题文本2”是否原来是(1,1)和(1,2)的一部分由换行符分隔,或者它们是否是表中的整个新行。我能想到的唯一方法是识别单元格边框(因为嵌套表的格式是从单词转移)并写入一百万个嵌套的“if子句”来标识单元格需要添加其值的位置。

1 个解决方案

#1

I did it with

我做到了

If RCon.Offset(columnOffset:=-1).Borders(xlEdgeBottom).LineStyle <> xlNone Then
ElseIf RCon.Offset(columnOffset:=-1).Borders(xlEdgeBottom).LineStyle = xlNone Then
       For Each RCon2 In .Range(.Cells(RCon.Offset(1).Row, CCount.Offset(columnOffset:=-1).Column), .Cells(.Cells(Rows.count, CCount.Offset(columnOffset:=-1).Column).End(xlUp).Row, CCount.Offset(columnOffset:=-1).Column))
              If RCon2.Borders(xlEdgeBottom).LineStyle <> xlNone Then
                    If RCon2.Value <> "" And RCon2.Value <> 0 Then
                          RCon.Offset(columnOffset:=-1).Value = RCon.Offset(columnOffset:=-1).Value & vbNewLine & RCon2.Value
                          RCon2.ClearContents
                    End If
                    Exit For
              ElseIf RCon2.Borders(xlEdgeBottom).LineStyle = xlNone And RCon2.Value <> "" And RCon2.Value <> 0 Then
                    RCon.Offset(columnOffset:=-1).Value = RCon.Offset(columnOffset:=-1).Value & vbNewLine & RCon2.Value
                    RCon2.ClearContents
              End If
       Next RCon2
End If

#1