将Excel范围复制到嵌入式Word文档，跳过“". (空单元格) [英] Copy Excel range to embedded Word document skipping "" (empty cells)

问题描述

(此问题是对

(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.)

问题是我在Excel中有动态内容.在E列的单元格中，我实际上具有公式，例如E55 =IF(B55="";"";"normal") E55也是=IF('Technical!B55'="";"";'Technical!B55').我当前的代码不了解例如单元格B57为空，并认为应将其打印到MS Word.它不会在MS Word中插入任何内容，但是会插入例如为MS Word中的样式预定义的项目符号.如何停止呢?我可以举一个例子，例如=IF(B55="";"empty";"normal")，因此如果有帮助的话，不必要的行将用单词空"标记.

The problem is that I have dynamic content in Excel. In cells of Column E I have actually formula, for example E55 =IF(B55="";"";"normal") E55 is also =IF('Technical!B55'="";"";'Technical!B55'). My current code does not understand that for example cell B57 is empty and thinks it should be printed to MS Word. It does not insert any content to MS Word but inserts for example bullets that are predefined for style in MS Word. How to stop that? I can make for example =IF(B55="";"empty";"normal") so unnecessary rows will be marked with word "empty" if it helps anyhow.

   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
55    Text again a lot of text again comes here                  normal
56    Text again a lot of text again comes here                  normal
57    =IF('Technical!B57'="";"";'Technical!B57')                 =IF(B57="";"";"normal")            
58    =IF('Technical!B58'="";"";'Technical!B58')                 =IF(B58="";"";"normal")

我当前的代码:

With objWord
  Set wdRng = .Range.Characters.Last
  Set wdUndo = .Application.UndoRecord
  wdUndo.StartCustomRecord ("Doc Data")
  Set xlSht = Sheets("Other Data")
  'Here comes Header
  .Bookmarks("Date").Range.Text = xlSht.Range("AT2").Value
  .Bookmarks("DocumentName").Range.Text = xlSht.Range("AX13").Value
Set xlSht = Nothing

Set xlSht = Sheets("Pricelist")
  For Each cell In xlRng
    wdRng.InsertAfter vbCr & cell.Offset(0, -4).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 "technical"
          wdRng.Paragraphs.Last.Style = .Styles("Technical")
          Case "topic"
          wdRng.Paragraphs.Last.Style = .Styles("Topic")
          Case "signature"

           Sheets("Signatures").Range("M7:N7").Copy

  With wdRng

    .Paragraphs.Last.Range.PasteSpecial (wdPasteBitmap)

  End With

          Case "pagebreak"
     With wdRng
     .Paragraphs.Last.Range.InsertBreak Type:=wdPageBreak
     End With
          Case "table"

          xlSht.Range(cell.Offset(0, -4), cell.Offset(0, -1)).Copy

  With wdRng

    .Paragraphs.Last.Range.PasteAndFormat (wdFormatPlainText)

  End With

    End Select
  Next cell
  .SaveAs2 ActiveWorkbook.Path & "\" & _
Sheets("Other Data").Range("AN2").Value & ", " & _
Sheets("Other Data").Range("AN7").Value & "_" & _
Sheets("Other Data").Range("AN8").Value & "_" & _
Sheets("Other Data").Range("AX3").Value & ".docx"

  wdUndo.EndCustomRecord
  Set wdUndo = Nothing
  .Undo
  .Application.Quit False
End With

推荐答案

有时候喝杯茶，然后考虑其他事情是件好事.我想出了使用=IF(B55="";"empty";"normal")的想法，因此我会在不必要的单元格中得到空"字.然后，我创建了名称为空"的新Case，并使用了以下代码:

Sometimes it is good to have a cup of tea and think about something else. I have came up with an idea of using =IF(B55="";"empty";"normal") so I will get word "empty" in unnecessary cells. Then I created new Case with name "empty" and used following code:

         Case "empty"

  With wdRng

    .Paragraphs.Last.Range.Delete

  End With

我不知道这是否是最好的解决方案.至少可以.

I don't know if this is the best solution. At least it works.

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