VBA Copy and Paste Excel Table into Word Document

Question

The code I'm writing is taking the used range (excluding the first two rows) of a worksheet and copying that table (or tables) onto its own page in a word document. The code was working fine until I had to make some changes to it... Right now the problem is that it is looping through all the worksheets in the workbook, but re-pasting the contents of the last worksheet only. Also, I can't seem to get VBA to recognize the pasted tables as tables -- so it isn't letting me center them in the word document. Any ideas for how I can solve these problems? Thanks in advance.

Sub toWord()
Dim ws As Worksheet
Dim fromWB As Variant
Dim wdApp As Object
Dim wdDoc As Object
Dim docName As Variant
Dim rng As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False


Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Add
wdDoc.Activate
'Creates InputBox that allows user to enter name to save document as
docName = Application.InputBox(Prompt:="Enter Document Name", Title:="Save Word Document", Type:=2)
wdDoc.SaveAs2 fileName:=docName, FileFormat:=wdFormatDocument 'Saves document under user-provided name

fromWB = Application.GetOpenFilename(FileFilter:="Excel Workbook(*.xlsx),*.xlsx", Title:="Open Merged Data")
If fromWB <> False Then
Set fromWB = Workbooks.Open(fromWB)
Set fromWB = ActiveWorkbook
ElseIf fromWB = False Then
    MsgBox "No File Selected"
    GoTo ResetSettings
End If


For Each ws In fromWB.Worksheets
    Range("A2").CurrentRegion.Offset(2).Resize(Range("A2").CurrentRegion.Rows.Count - 2).Select
    Selection.Copy
    Set wdApp = GetObject(, "Word.Application")
    wdApp.Visible = True
    wdDoc.Activate
    wdDoc.Range(wdDoc.Characters.Count - 1).Paste
    wdDoc.Range(wdDoc.Characters.Count - 1).InsertBreak Type:=7
    ActiveDocument.Tables(1).Select
    Selection.Tables(1).Rows.Alignment = wdAlignRowCenter
Next ws
   wdDoc.Styles("Normal").NoSpaceBetweenParagraphsOfSameStyle = True
wdDoc.Save
Set wdDoc = Nothing
Set wdApp = Nothing
MsgBox "Imported into Word Document"


ResetSettings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Can you place a break point at Set wdApp = GetObject(, "Word.Application") and advance line by line. As you already set this variable to the Word.Application setting it in your loop might be restarting the object, eliminating your previously pasted text. You can also try commenting that line and see if just making wdApp.Visible = True will work. Regards. — nbayly

joehanna joehanna · Accepted Answer · 2015-06-15T18:50:40

The issues I see with your code are:-

Set fromWB = ActiveWorkbook is superfluous and can be removed
You are referencing Range without a worksheet prefix, so it will always default to the current active worksheet (which is the one that would be visible when you open the workbook). Normally I would say to do this: ws.Range("A2") but in this case, I would suggest ws.Activate as the first statement inside your For Each loop since you are using physical actions such as copy/paste.
To be a good resource citizen, you should be quitting Excel and setting fromWB = Nothing to release the memory. In fact, in VBA, you should be setting all assigned object variables to Nothing before you leave the method.

VBA Copy and Paste Excel Table into Word Document

1 Answers