CODEWITHSUNDEEP
excelvbams-word
10101
10101

Reputation: 2412

"Nullify" Word style before pasting an Excel table in the document

I have a code (Full code can be found here) for inserting data from Excel to embedded Word document. It reads cell commands (cases) and inputting data to Word based on them (see screenshot above "main", "attachment", "table2"). The problem is that if there is no style defined for some Case it takes previous style.

I have came across the problem while copying Excel range and inserting it to my document. Code takes style "attachment" parameters and inserting table with predefined style "Attachment" to Word. How to null previous style parameters before inserting range to Ms Word?

Here is my code for inserting table: 

                  Set xlSht = Sheets("Service")
               Case "table2"
        xlSht.Range("B11:I20").Copy
     With wdRng
  .Paragraphs.Last.Range.PasteExcelTable False, False, False
  .Tables(.Tables.Count).AutoFitBehavior wdAutoFitWindow
End With

VBA code result: enter image description here

Manually copied: enter image description here

In Excel: enter image description here

Upvotes: 1

Views: 64

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25693

If you have a case where you want to "nullify" all style information do that before pasting the table. The "null" style in Word is the Normal style; this style is also "safest" when inserting tables*.

The following code snippet, based on the code in the question, first applies the style, then pastes the table.

Dim rngPara as Word.Range
Set rngPara = .Paragraphs.Last.Range
rngPara.Style = wdStyleNormal
rngPara.PasteExcelTable False, False, False

*Besides paragraph and character formatting Word also has table styles. These are a special kind of style that determines table formatting (as opposed to paragraph or character formatting)). These are only applied correctly when a table is created in an empty paragraph formatted with the Normal style.

Upvotes: 2

Related Questions