Copy table to word vba but will not open word .

danpan

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi all so I've managed to make this code work for what i needed it to , it opens word , it opens and pastes the table in the desired position on my device.
when I copy the code into my work computer i keep getting a runtime error and it cant open the word document.
any suggestions or any improvements that you can see that i could make to the code would be greatly appreciated. To give you an idea of my level at excel back in April I had no idea how to create a table.
Any help is greatly appreciated.

The code is courtesy of sigma code. with minimal adjustments.

VBA Code:
Sub TblWrd()

'declare word Variables
Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim WrdTbl As Word.Table
Dim wrksht As Worksheet
Dim wrdrange As Word.Range
Dim wrdshp As Word.InlineShape

'declare excel variables
Dim Exceltbl As ListObject

'create  a new istance of word
Set WrdApp = New Word.Application
    WrdApp.Visible = True
    WrdApp.Activate '<-  I know the issue is at that line  and I know googling will give me the actual answer but id rather undertand what the problem is and why it fails to open world ,[/COLOR][/B]
 
    'open document in word application
    Set WrdDoc = WrdApp.Documents.Open("C:\......112.dotx")

    'loop through the list objects in the applicaton
    For Each Exceltbl In ActiveSheet

   'Set Exceltbl = ActiveSheet.ListObjects("TABLE1", "table2", "table3")
    Exceltbl.Range.Copy
  
   'pause the excel application for one second
   Application.Wait Now() + #12:00:02 AM#
  
  'paste to bookmark
  Set wrdrange = WrdDoc.Bookmarks(1).Range   
    
     'paste to word
            With wrdrange
        .PasteExcelTable linkedtoexcel:=True, WordFormatting:=True, RTF:=True
    End With
 
  'create a referemce to the table we just pasted
  Set WrdTbl = WrdDoc.Tables(WrdDoc.Tables.Count)
      WrdTbl.AllowAutoFit = True
      WrdTbl.AutoFitBehavior (wdAutoFitWindow)
     
      'create a new page
       WrdApp.ActiveDocument.Sections.Add
       WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
      
       'clear my clipboard
       Application.CutCopyMode = False
      
       Next
    Next

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,294
I suggest you tell us the exact error (you can take a screenshot of the runtime error message box and post here) and also the highlighted line in the code when you click on the Debug button on error. It might help to diagnose the problem.

The possible reasons at first look without seeing that extra information:
  1. You copied and pasted the code. Did you also select the Microsoft Word library in Tools->References in VBE? The code is using early binding for the Word objects, so you need to include the necessary libraries.
  2. Are you referring to an existing file with the Open method? Does C:\......112.dotx exist in both computer, and you provide the exact path accordingly?
 

danpan

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
vba.png
Hi i though it had solved it self but one has learnt nothing solves its self :) so when i restarted themy pc andhad not opened word it was working fine
when i had a different word document open it came up with this error.
 

danpan

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I suggest you tell us the exact error (you can take a screenshot of the runtime error message box and post here) and also the highlighted line in the code when you click on the Debug button on error. It might help to diagnose the problem.

The possible reasons at first look without seeing that extra information:
  1. You copied and pasted the code. Did you also select the Microsoft Word library in Tools->References in VBE? The code is using early binding for the Word objects, so you need to include the necessary libraries.
  2. Are you referring to an existing file with the Open method? Does C:\......112.dotx exist in both computer, and you provide the exact path accordingly?
Hi thank you for taking a look at this , here is the screenshot of the error.
vba.png

So on testing this a few times and restarting i found out that the code works fine as long as there is no other word applications open or other word documents opened. However when i select debug and then run the code again it works , but if i should close the document and run it it goes through the same issue.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,294

ADVERTISEMENT

I don't have access to a Windows machine at the moment, but your code contains errors.

Remove the last Next, it doesn't have an opening For.
For Each Exceltbl In ActiveSheet : To loop through the tables in the worksheet, you need ActiveSheet.ListObjects instead of ActiveSheet only.
And you don't need to activate the Word application window while the code is copy & paste from Excel to Word.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,294
I made some changes to make the current code work ( I didn't remove the unnecessary object declarations, etc.).
Please try the following revised code by changing "FULL_WORD_FILE_PATH_HERE" string with the absolute path of the Word document.

VBA Code:
Sub TblWrd()
'declare word Variables
Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim WrdTbl As Word.Table
Dim wrksht As Worksheet
Dim wrdrange As Word.Range
Dim wrdshp As Word.InlineShape

    'declare excel variables
    Dim Exceltbl As ListObject

    'create  a new istance of word
    Set WrdApp = New Word.Application
    WrdApp.Visible = True
 
    'open document in word application
    Set WrdDoc = WrdApp.Documents.Open("FULL_WORD_FILE_PATH_HERE")

    'loop through the list objects in the applicaton
    For Each Exceltbl In ActiveSheet.ListObjects
        Exceltbl.Range.Copy

        ' YOU SHOULDN'T NEED THE FOLLOWING
        'pause the excel application for one second
        'Application.Wait Now() + #12:00:02 AM#
  
        'paste to bookmark
        Set wrdrange = WrdDoc.Bookmarks(1).Range
    
        'paste to word
        wrdrange.PasteExcelTable linkedtoexcel:=True, WordFormatting:=True, RTF:=True
 
        'create a referemce to the table we just pasted
        Set WrdTbl = WrdDoc.Tables(WrdDoc.Tables.Count)
        WrdTbl.AllowAutoFit = True
        WrdTbl.AutoFitBehavior (wdAutoFitWindow)
     
        'create a new page
         WrdApp.ActiveDocument.Sections.Add
         WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
      
        'clear my clipboard
        Application.CutCopyMode = False
    Next
    WrdApp.Activate
End Sub
 
Solution

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,514

ADVERTISEMENT

Do your home & work PC's use the same Word version? If you've developed the code at home on a PC using Office 2016, for example, but the work PC uses Office 2013, you will have to re-add the reference to Word on your work PC.

Since you're using a Word template (.dotx extension) you should be using:
VBA Code:
Set WrdDoc = WrdApp.Documents.[B]Add[/B]("C:\......112.dotx")
with the full path.

What is the point of:
1.
VBA Code:
Set wrdrange = WrdDoc.Bookmarks(1).Range
wrdrange.PasteExcelTable linkedtoexcel:=True, WordFormatting:=True, RTF:=True
All this does is paste each Excel table to the same Word bookmark.

2.
VBA Code:
WrdApp.ActiveDocument.Sections.Add
WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
All this does is add a new Section to the Word document without doing anything with it.

Subject to the above, the code could be reduced to:
VBA Code:
Sub TblWrd()
'declare word Variables
Dim WrdApp As New Word.Application, WrdDoc As Word.Document, WrdTbl As Word.Table
'declare excel variables
Dim Exceltbl As ListObject
With WrdApp
  .Visible = True
  'open document in word application
  Set WrdDoc = .Documents.Add("C:\......112.dotx")

  'loop through the list objects in the applicaton
  For Each Exceltbl In ActiveSheet.ListObjects
    Exceltbl.Range.Copy
    With WrdDoc
      'paste to bookmark
      Set WrdTbl = .Bookmarks(1).Range.PasteExcelTable(LinkedToExcel:=True, WordFormatting:=True, RTF:=True)
      WrdTbl.AllowAutoFit = True
      WrdTbl.AutoFitBehavior (wdAutoFitWindow)
     
      'create a new Section
      .Sections.Add
       WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
    End With
    'clear my clipboard
    Application.CutCopyMode = False
    Next
  Next
End With
End Sub
 

danpan

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I made some changes to make the current code work ( I didn't remove the unnecessary object declarations, etc.).
Please try the following revised code by changing "FULL_WORD_FILE_PATH_HERE" string with the absolute path of the Word document.

VBA Code:
Sub TblWrd()
'declare word Variables
Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim WrdTbl As Word.Table
Dim wrksht As Worksheet
Dim wrdrange As Word.Range
Dim wrdshp As Word.InlineShape

    'declare excel variables
    Dim Exceltbl As ListObject

    'create  a new istance of word
    Set WrdApp = New Word.Application
    WrdApp.Visible = True

    'open document in word application
    Set WrdDoc = WrdApp.Documents.Open("FULL_WORD_FILE_PATH_HERE")

    'loop through the list objects in the applicaton
    For Each Exceltbl In ActiveSheet.ListObjects
        Exceltbl.Range.Copy

        ' YOU SHOULDN'T NEED THE FOLLOWING
        'pause the excel application for one second
        'Application.Wait Now() + #12:00:02 AM#
 
        'paste to bookmark
        Set wrdrange = WrdDoc.Bookmarks(1).Range
   
        'paste to word
        wrdrange.PasteExcelTable linkedtoexcel:=True, WordFormatting:=True, RTF:=True

        'create a referemce to the table we just pasted
        Set WrdTbl = WrdDoc.Tables(WrdDoc.Tables.Count)
        WrdTbl.AllowAutoFit = True
        WrdTbl.AutoFitBehavior (wdAutoFitWindow)
    
        'create a new page
         WrdApp.ActiveDocument.Sections.Add
         WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
     
        'clear my clipboard
        Application.CutCopyMode = False
    Next
    WrdApp.Activate
End Sub
Hi Thank you that has worked I really appreciate you taking time to look at this . You have been really helpful.
 

danpan

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Do your home & work PC's use the same Word version? If you've developed the code at home on a PC using Office 2016, for example, but the work PC uses Office 2013, you will have to re-add the reference to Word on your work PC.

Since you're using a Word template (.dotx extension) you should be using:
VBA Code:
Set WrdDoc = WrdApp.Documents.[B]Add[/B]("C:\......112.dotx")
with the full path.

What is the point of:
1.
VBA Code:
Set wrdrange = WrdDoc.Bookmarks(1).Range
wrdrange.PasteExcelTable linkedtoexcel:=True, WordFormatting:=True, RTF:=True
All this does is paste each Excel table to the same Word bookmark.

2.
VBA Code:
WrdApp.ActiveDocument.Sections.Add
WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
All this does is add a new Section to the Word document without doing anything with it.

Subject to the above, the code could be reduced to:
VBA Code:
Sub TblWrd()
'declare word Variables
Dim WrdApp As New Word.Application, WrdDoc As Word.Document, WrdTbl As Word.Table
'declare excel variables
Dim Exceltbl As ListObject
With WrdApp
  .Visible = True
  'open document in word application
  Set WrdDoc = .Documents.Add("C:\......112.dotx")

  'loop through the list objects in the applicaton
  For Each Exceltbl In ActiveSheet.ListObjects
    Exceltbl.Range.Copy
    With WrdDoc
      'paste to bookmark
      Set WrdTbl = .Bookmarks(1).Range.PasteExcelTable(LinkedToExcel:=True, WordFormatting:=True, RTF:=True)
      WrdTbl.AllowAutoFit = True
      WrdTbl.AutoFitBehavior (wdAutoFitWindow)
    
      'create a new Section
      .Sections.Add
       WrdApp.Selection.GoTo what:=wdGoToPage, which:=wdGoToNext
    End With
    'clear my clipboard
    Application.CutCopyMode = False
    Next
  Next
End With
End Sub
Hi and thank you as well for taking the time to look into this , at home I use 365 and at work we use the 2019 suite , I couldn't pick your brains so to speak , if I wanted to specify the order in which the tables get pasted ,I would assume the best way of doing so is naming the tables , and assigning a bookmark to each of them . ?The sections part looking back at it , I was probably trying to see if I could paste each table to a new section .But I think the bookmark maybe the easies way.
Just to give some context Im trying to create a document where most cells are mail merged into word from excel and then in certain areas of the form to add the respective table if that makes sense
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,705
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top