根据其他单元格的内容编辑Word表格中的单元格内容

huangapple go评论284阅读模式
英文:

Edit cell contents in table in Word, based on other cell contents

问题

I have an Excel file containing a very large table, in which I have a macro that copies the table into a Word file.

I want the macro to check the top left cell of each page in the Word file and - if it's empty - populate it with the contents of the last populated cell above it (i.e. on the previous page) with " cont'd" added to the end.

The result should look like the below (ignore the three rows at the top of the second page - they are in the header):

根据其他单元格的内容编辑Word表格中的单元格内容

I produced the below. I'm aware that my approach is flawed as if you have a single entry spanning multiple pages, then working from the beginning will result in subsequent pages having extra " cont'd" added, so it may make more sense to work back from the end.

  1. ' Get the Word table
  2. Set wordTable = objDoc.Tables(1)
  3. ' Loop through the table cells
  4. For Each cell In wordTable.Range.Cells
  5. ' Check if it's the top-left cell of a new page
  6. If cell.Range.Information(3) = 1 Then ' wdFirstCellOnPage
  7. currentPage = cell.Range.Information(2) ' wdActiveEndAdjustedPageNumber
  8. ' Check if the cell is blank and not on the first page
  9. If IsEmpty(cell.Value) And currentPage > 1 Then
  10. ' Get the corresponding cell above
  11. Set previousCell = wordTable.cell(cell.RowIndex - 1, cell.ColumnIndex)
  12. ' Copy the contents of the cell above and add " cont'd"
  13. previousCell.Range.Copy
  14. cell.Range.Paste
  15. cell.Range.InsertAfter " cont'd"
  16. End If
  17. End If
  18. Next cell
英文:

I have an Excel file containing a very large table, in which I have a macro that copies the table into a Word file.

I want the macro to check the top left cell of each page in the Word file and - if it's empty - populate it with the contents of the last populated cell above it (i.e. on the previous page) with " cont'd" added to the end.
The result should look like the below (ignore the three rows at the top of the second page - they are in the header):
根据其他单元格的内容编辑Word表格中的单元格内容

I produced the below. I'm aware that my approach is flawed as if you have a single entry spanning multiple pages, then working from the beginning will result in subsequent pages having extra " cont'd" added, so it may make more sense to work back from the end.

  1. ' Get the Word table
  2. Set wordTable = objDoc.Tables(1)
  3. ' Loop through the table cells
  4. For Each cell In wordTable.Range.Cells
  5. ' Check if it's the top-left cell of a new page
  6. If cell.Range.Information(3) = 1 Then ' wdFirstCellOnPage
  7. currentPage = cell.Range.Information(2) ' wdActiveEndAdjustedPageNumber
  8. ' Check if the cell is blank and not on the first page
  9. If IsEmpty(cell.Value) And currentPage > 1 Then
  10. ' Get the corresponding cell above
  11. Set previousCell = wordTable.cell(cell.RowIndex - 1, cell.ColumnIndex)
  12. ' Copy the contents of the cell above and add " cont'd"
  13. previousCell.Range.Copy
  14. cell.Range.Paste
  15. cell.Range.InsertAfter " cont'd"
  16. End If
  17. End If
  18. Next cell

答案1

得分: 0

OK,您可以通过将光标放在previousCell中来测试我的代码,然后观察是否符合您的要求。

如果符合要求,您只需根据您的配置进行调整。

GetTopleftCellPagepreviousCellvalueRef是关键点。

  1. Sub VBA_Macro_to_edit_cell_contents_in_table_in_Word_based_on_other_cell_contents()
  2. Dim objDoc As Word.Document, cell As Word.cell, previousCell As Word.cell
  3. Dim valueRef As String, wordTable As Word.Table, ur As UndoRecord
  4. Set ur = Word.Application.UndoRecord
  5. ur.StartCustomRecord "VBA_Macro_to_edit_cell_contents_in_table_in_Word_based_on_other_cell_contents"
  6. Set objDoc = ActiveDocument
  7. ' 在*previousCell*中测试我的代码
  8. If Not Selection.Information(wdWithInTable) Then ' 仅用于测试
  9. MsgBox "必须将光标放在单元格中以尝试!", vbCritical
  10. Exit Sub
  11. End If
  12. Set previousCell = Selection.Cells(1) ' 仅用于测试
  13. previousCell.Range.Text = "01-007" '' 仅用于测试
  14. valueRef = Replace(previousCell.Range.Text, Chr(13) & Chr(7), "")
  15. ' 获取Word表格
  16. 'Set wordTable = objDoc.Tables(1) '您的文档中只有一个表格吗?
  17. Set wordTable = previousCell.Range.Tables(1)
  18. Set cell = GetTopleftCellPage(previousCell)
  19. Do Until cell Is Nothing
  20. If VBA.Len(cell.Range.Text) = 2 Then 'Chr(13) & Chr(7)
  21. cell.Range.Text = valueRef + " cont'd"
  22. ' 仅用于测试观察
  23. cell.Range.Select
  24. End If
  25. Set cell = GetTopleftCellPage(cell)
  26. Loop
  27. ur.EndCustomRecord
  28. MsgBox "完成!", vbInformation
  29. ' 通过表格单元格循环
  30. ' For Each cell In wordTable.Range.Cells
  31. ' ' 检查是否是新页面的左上角单元格
  32. ' If cell.Range.Information(3) = 1 Then ' wdFirstCellOnPage
  33. ' currentPage = cell.Range.Information(2) ' wdActiveEndAdjustedPageNumber
  34. '
  35. ' ' 检查单元格是否为空且不在第一页
  36. ' If IsEmpty(cell.Value) And currentPage > 1 Then
  37. '
  38. ' ' 获取上面的对应单元格
  39. ' Set previousCell = wordTable.cell(cell.RowIndex - 1, cell.ColumnIndex)
  40. '
  41. ' ' 复制上面单元格的内容并添加 " cont'd"
  42. ' previousCell.Range.Copy
  43. ' cell.Range.Paste
  44. ' cell.Range.InsertAfter " cont'd"
  45. ' End If
  46. ' End If
  47. ' Next cell
  48. End Sub
  49. Property Get GetTopleftCellPage(ByVal cell As cell) As Word.cell
  50. Dim r As Long, rng As Range, tb As Word.Table
  51. Dim firstVisibleLine As Long, currentPage As Long
  52. Dim firstCellOnPage As cell
  53. Set tb = cell.Range.Tables(1)
  54. r = cell.RowIndex
  55. Set rng = cell.Range
  56. currentPage = rng.Information(wdActiveEndPageNumber)
  57. Do While currentPage = rng.Information(wdActiveEndPageNumber)
  58. r = r + 1
  59. Set rng = tb.cell(r, 1).Range
  60. If r >= tb.rows.Count Then
  61. Exit Property
  62. End If
  63. Loop
  64. Set GetTopleftCellPage = rng.Cells(1)
  65. End Property

结果演示:
根据其他单元格的内容编辑Word表格中的单元格内容

跟随上一行

当我仔细查看您的屏幕截图时,看起来您只想将上一列的值赋给下一列。如果是这样,只需运行以下代码:

  1. Dim rowCell As Long
  2. rowCell = previousCell.Row + 1
  3. If rowCell <= tb.Rows.Count Then
  4. Set cell = tb.Cells(rowCell, previousCell.ColumnIndex)
  5. cell.Range.Text = valueRef + " cont'd"
  6. End If

或者

或者,您可以通过GetTopleftCellPage获取每页的顶部单元格,然后获取其上一行中的对应单元格。所有这些应该已经包含在我提供的代码中。

英文:

OK, you can test my code by placing the cursor in your previousCell, and watching if this is what you want to be.

If it is, you just have to adjust to the configuration you need.

The GetTopleftCellPage, previousCell, valueRef are the point.

  1. Sub VBA_Macro_to_edit_cell_contents_in_table_in_Word_based_on_other_cell_contents()
  2. Dim objDoc As Word.Document, cell As Word.cell, previousCell As Word.cell
  3. Dim valueRef As String, wordTable As Word.Table, ur As UndoRecord
  4. Set ur = Word.Application.UndoRecord
  5. ur.StartCustomRecord &quot;VBA_Macro_to_edit_cell_contents_in_table_in_Word_based_on_other_cell_contents&quot;
  6. Set objDoc = ActiveDocument
  7. Rem Test my code by placing the cursor in the *previousCell*
  8. If Not Selection.Information(wdWithInTable) Then &#39;just for test
  9. MsgBox &quot;must placing the cursor in a cell to try!&quot;, vbCritical
  10. Exit Sub
  11. End If
  12. Set previousCell = Selection.Cells(1) &#39;just for test
  13. previousCell.Range.Text = &quot;01-007&quot; &#39;&#39;just for test
  14. valueRef = Replace(previousCell.Range.Text, Chr(13) &amp; Chr(7), &quot;&quot;)
  15. &#39; Get the Word table
  16. &#39;Set wordTable = objDoc.Tables(1) &#39;YOU GOT ONLY ONE TABLE in a document??
  17. Set wordTable = previousCell.Range.Tables(1)
  18. Set cell = GetTopleftCellPage(previousCell)
  19. Do Until cell Is Nothing
  20. If VBA.Len(cell.Range.Text) = 2 Then &#39;Chr(13) &amp; Chr(7)
  21. cell.Range.Text = valueRef + &quot; cont&#39;d&quot;
  22. Rem just for watch in testing
  23. cell.Range.Select
  24. End If
  25. Set cell = GetTopleftCellPage(cell)
  26. Loop
  27. ur.EndCustomRecord
  28. MsgBox &quot;done!&quot;, vbInformation
  29. &#39; &#39; Loop through the table cells
  30. &#39; For Each cell In wordTable.Range.Cells
  31. &#39; &#39; Check if it&#39;s the top-left cell of a new page
  32. &#39; If cell.Range.Information(3) = 1 Then &#39; wdFirstCellOnPage
  33. &#39; currentPage = cell.Range.Information(2) &#39; wdActiveEndAdjustedPageNumber
  34. &#39; &#39; Check if the cell is blank and not on the first page
  35. &#39; If IsEmpty(cell.Value) And currentPage &gt; 1 Then
  36. &#39; &#39; Get the corresponding cell above
  37. &#39; Set previousCell = wordTable.cell(cell.RowIndex - 1, cell.ColumnIndex)
  38. &#39; &#39; Copy the contents of the cell above and add &quot; cont&#39;d&quot;
  39. &#39; previousCell.Range.Copy
  40. &#39; cell.Range.Paste
  41. &#39; cell.Range.InsertAfter &quot; cont&#39;d&quot;
  42. &#39; End If
  43. &#39; End If
  44. &#39; Next cell
  45. End Sub
  46. Property Get GetTopleftCellPage(ByVal cell As cell) As Word.cell
  47. Dim r As Long, rng As Range, tb As Word.Table &#39;, d As Document
  48. Dim firstVisibleLine As Long, currentPage As Long
  49. Dim firstCellOnPage As cell
  50. &#39;Set d = ActiveDocument
  51. Set tb = cell.Range.Tables(1)
  52. r = cell.RowIndex
  53. Set rng = cell.Range
  54. currentPage = rng.Information(wdActiveEndPageNumber)
  55. Do While currentPage = rng.Information(wdActiveEndPageNumber)
  56. r = r + 1
  57. Set rng = tb.cell(r, 1).Range
  58. If r &gt;= tb.rows.Count Then
  59. Exit Property
  60. End If
  61. Loop
  62. Set GetTopleftCellPage = rng.Cells(1)
  63. End Property

The result demo:
根据其他单元格的内容编辑Word表格中的单元格内容

follow the previous row

When I look more closely at your screenshot, it looks like you just want to give the value of the previous column to the next column. If that's the case, simply run these lines:

  1. dim rowCell as long
  2. rowCell= previousCell.row+1
  3. if rowCell &lt;=tb.rows.count then
  4. Set cell = tb.cells(rowCell,previousCell.ColumnIndex)
  5. cell.Range.Text = valueRef + &quot; cont&#39;d&quot;
  6. end if

OR

Or, you can get the cell at the top of each page by GetTopleftCellPage and then get the corresponding cell in the row before it. All these needs should be already in the code I gave.

答案2

得分: 0

这段代码会扫描表格的第一列,查找页面上第一个单元格为空的情况。

英文:

This will scan down the first column of the table looking for cases where the cell is the first on a page and is empty:

  1. Sub Tester()
  2. Dim tbl As Table, col As Column, c As Cell, i As Long, pgNumStart, pgNumEnd
  3. Dim rng As Range, currPage, lastText, txt, newPage As Boolean
  4. Set tbl = ActiveDocument.Tables(1)
  5. Set col = tbl.Columns(1) &#39;only looking in the first column of the table
  6. currPage = 0
  7. For Each c In col.Cells
  8. Set rng = c.Range.s
  9. rng.Collapse wdCollapseStart
  10. pgNumStart = rng.Information(3) &#39;cell begins on this page
  11. &#39;pgNumEnd = c.Range.Information(3) &#39;cell ends on this page
  12. newPage = (pgNumStart &gt; 1) And (pgNumStart &lt;&gt; currPage) &#39;on a new page?
  13. If newPage Then currPage = pgNumStart &#39;update current page
  14. txt = CellText(c)
  15. If Len(txt) = 0 Then &#39;cell is empty?
  16. If newPage And Len(lastText) &gt; 0 Then
  17. c.Range.Text = lastText &amp; vbLf &amp; &quot;(cont)&quot;
  18. End If
  19. Else
  20. lastText = txt
  21. End If
  22. Next c
  23. End Sub
  24. &#39;cell text, minus end-of-cell marker
  25. Function CellText(c As Cell)
  26. CellText = Replace(c.Range.Text, Chr(13) &amp; Chr(7), &quot;&quot;)
  27. End Function

huangapple
  • 本文由 发表于 2023年6月9日 00:58:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/76434145.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定