Excel VBA复制范围(左侧的所有单元格)到嵌入式Word文档 [英] Excel VBA copy range (all cells from the left) to embedded Word document

问题描述

(此问题是对

(This question is a follow-up on how to work with a document embedded in an Excel workbook in the Word application interface (instead of in-place). The reason this is necessary is to be able to save the result as an independent document.)

我想到的问题是某些单元格不符合条件cell.Offset(0, -3).Text.因此，文本不仅使用Offset(0, -3)放在一个单元格中，而且在Word中看起来应该像这样:

The problem I came up with is that some cells does not fit to criteria cell.Offset(0, -3).Text. So text is not only in one cell with Offset(0, -3) and in Word they should look like:

Legal             John Smith
                  Telephone         [phone]
                  Email             [e-mail]

在Excel中，它们位于单独的行中.

In Excel they are in separate rows.

• 法律"在B50中.
• "John Smith"在C50中.
• 电话"在C51中.
• "+ 4854132155"在D51中.
• 电子邮件"位于C52中.
• "[e-mail]"在C52中.

这个想法是要有一个名为"table"的"Case"，因此代码将理解它应该逐行复制Case"table"左侧的所有内容(单词"table"位于E列).粘贴到Word作为表格并执行wdAutoFitWindow，以便该表格完全适合Word文档窗口.

The idea is to have a "Case" called "table" so code will understand that it should copy everything from left of Case "table" row by row (word "table" is located in Column E). Paste to Word as a table and do wdAutoFitWindow so that table would fit perfectly to Word Document Window.

数据在Excel中的外观:

How data looks in Excel:

   A    B                   C                   D                  E
49    Paragraph with number 1                                    main
48    Ok text is text and it is good to have here.. a lot of     normal
50    Legal             John Smith                               table
51                      Telephone         [phone]            table 
52                      Email             [e-mail]    table
53    Paragraph with number 2                                    main
54    Text again a lot of text again comes here                  normal

当前代码有什么问题:当前代码运行平稳.但是，一旦到达E列中带有单词"table"的Excel行，它将删除所有已插入的内容，并仅插入带有参数"table"的最后一行.因此从根本上说，它一直在做到Case"table"为止的所有事情.然后作为输出，您将得到:

What is wrong with current code: Current code runs smoothly. However once it reaches Excel row with word "table" in Column E, it deletes everything already inserted and inserts only last row with parameter "table". So basically it is doing everything right until Case "table". Then as an output you will get:

              Email             [e-mail]

2   Paragraph with number                                   
    Text again a lot of text again comes here     

代替:

1   Paragraph with number                                   
    Ok text is text and it is good to have here.. a lot of 
    Legal          John Smith                             
                   Telephone         [phone]              
                   Email             [e-mail]
2   Paragraph with number                                   
    Text again a lot of text again comes here        

这是我尝试实现的部分:

Here is the part I have been tried to implement:

Set xlSht = Sheets("Offer Letter")
  For Each cell In xlRng
    wdRng.InsertAfter vbCr & cell.Offset(0, -3).Text
     Select Case LCase(cell.Value)
        Case "title"
          wdRng.Paragraphs.Last.Style = .Styles("Heading 1")
        Case "main"
          wdRng.Paragraphs.Last.Style = .Styles("Heading 2")
        Case "sub"
          wdRng.Paragraphs.Last.Style = .Styles("Heading 3")
        Case "sub-sub"
          wdRng.Paragraphs.Last.Style = .Styles("Heading 4")
        Case "normal"
          wdRng.Paragraphs.Last.Style = .Styles("Normal")
        Case "contact"
          wdRng.Paragraphs.Last.Style = .Styles("Contact")
          Case "attachment"
          wdRng.Paragraphs.Last.Style = .Styles("Attachment")
          Case "table"

                  xlSht.Range(cell.Offset(0, -3), cell.Offset(0, -1)).Copy
     wdRng.PasteExcelTable False, False, False

      wdRng.Tables(1).AutoFitBehavior wdAutoFitWindow

    End Select
  Next cell

推荐答案

您似乎应该使用:

  With wdRng
    .Paragraphs.Last.Range.PasteExcelTable False, False, False
    .Tables(.Tables.Count).AutoFitBehavior wdAutoFitWindow
    .Tables(.Tables.Count).Range.Style = "Normal"
  End With

这篇关于Excel VBA复制范围(左侧的所有单元格)到嵌入式Word文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！