Copy Table From Excel to Word

Post date: Dec 28, 2015 2:45:29 PM

I paste XL Tables as images to MS Word.

Why an image? Why not an editable table?

One guiding data management principle is to "preserve one version of the truth". In practice this means we should always get our data from its source. If we are pasting from XL, our source data is in XL or our table contains formulas over data downloaded from its source. If we need to change our table, we should re-download the data to XL and/or change the formulas in XL. If we could also change our table in MS Word, we could end up with our XL table showing one thing and our Word table showing something else. We would have two versions of the truth.

Images are more difficult to alter; thus, more likely to be an accurate snapshot of the our data. Images also preserve our XL table's formatting; thus, this can save us from having to fix formatting in MS Word. Lastly, pasting images is easy. Below is a code snippet to show how easy.

This code snippet assumes our table's name is tblData and that we want to paste our table into a new MS Word document.

VBA Code:

Dim oMSW As Object

Set oMSW = CreateObject("Word.Application")

oMSW.Visible = True

oMSW.Documents.Add

[tblData[#All]].Copy

oMSW.Selection.PasteSpecial Placement:=0, DataType:=3

NOTE!

This code uses Late Binding (click link for more info) because I find it is the most reliable way to code for others. When we use late binding, we lose object enumerations which help make our code more readable. Below is the same last code line as it would appear had we added the "Microsoft Word 15.0 Object Library" reference to our XL project.

oMSW.Selection.PasteSpecial Placement:=wdInLine, DataType:=wdPasteMetafilePicture

Below is a list of all "DataTypes" used by PasteSpecial for MS Word along with their numeric values. Without the "Microsoft Word 15.0 Object Library" reference we cannot use MS Word's enumerations. Instead, we must use the values from this table.