Paste table from Excel to Word as “Use Destination Styles” paste using VBA

1
votes

I have a macro that currently copies a table from a range of cells in an Excel spreadsheet, then pastes it to a Word document (which was created earlier in the macro as WordApp). However, I'm hoping to have it paste into the Word doc with the paste option "Use Destination Styles" that is one of the paste style options in Word. Does anyone know if there is a way to embed that into the Excel's VBA code?

Here is my current code (assume that "WordApp" is dim word application and a new doc has already been accounted for in the code).

         'Select table data
            Sheets("Sheet3").Select
            Range("I11:P18").Select
            Selection.Copy
         'Paste table into word doc
            WordApp.Selection.TypeParagraph
            WordApp.Selection.Paste

Any help would be greatly appreciated so I can finally finish this gigantic VBA project!

EDIT:

Unfortunately, that doesn't work--it pastes it in the format I don't want. I tried using this code, but i'm getting a run time error 5342, which says "The specified data type is unavailable" and highlights .PasteAndFormat line of code. Does anyone know why there is an error? I got that code from the object browser, so I'm not sure why it doesn't recognize it:

        Sheets("Sheet3").Select
        Range("H21:P34").Select
        Selection.Copy
    'Paste Q1-7 table into word doc
        WordApp.Selection.PasteAndFormat (WdPasteOptions.wdUseDestinationStyles)

Please, anyone who can solve this, I would really appreciate it. This is a final detail on a gigantic macros project that I've been working on, and I just want to wrap this up.

2
excelvbams-wordstylescopy-paste

2 Answers

1
votes

I figured it out on my own. Here's the code that pastes it as a table, the way that I wanted it to be pasted, without having to use word's paste as destination styles option.

        Sheets("Sheet3").Select
        Range("H21:P34").Select
        Selection.Copy
    'Paste Q1-7 table into word doc
        WordApp.Selection.PasteExcelTable False, True, False
0
votes
WordApp.Selection.Range.PasteSpecial DataType:=wdPasteText

Does this work? I have this for a macro that pastes just one cell text into Word, but I don't have resources available to test this in Word. (I would've just written a comment if I had 50 rep, so sorry for making a whole answer for this).