Copy Excel Table into existing Word Document stored on Sharepoint

[jessitarexcel]

I have spent days now searching for a solution to this problem and none of the solutions I have found work. I really need help with this macro. It should work, I have written so many iterations it is ridiculous and have watched a heap of Youtube videos as well. Any assistance is sincerely appreciated. I have made sure that Microsoft and Microsoft Word Objects 16.0 is selected in references.

The text in red isn't working. Is it the name of the tab that is the issue?

This is the latest instance of the VBA and I will post the other versions I have tried in comments below this post.

Dim wordObject As Object
Dim wordDocument As Object
Dim wordTable As Object

Application.ScreenUpdating = False
Application.EnableEvents = False

ThisWorkbook.Worksheet(COW and Stat Dec Table.Name).ListObjects("StatDecTable").Range

On Error Resume Next
Set WordApp = GetObject(class:="Word.Application")
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

Set pageEditor = xInspect.WordEditor
WordApp.Visible = True
WordApp.Activate
WordApp.Documents.Open "Link Removed for Privacy" (The link does work and the right word document opens but it won't paste the table)

Stat Dec and COW Table - New.Range("StatDecTable"[#All]").Copy
mydoc.Paragraphs(1).Range.PasteExcelTable_
LinkedtoExcel = False, _
WordFormatting = False, _
RTF: = False

Application.ScreenUpdating = True
Application.EnableEvents = True
Set pageEditor = Nothing
'Clear the clipboard
Application.CutCopyMode = False

End Sub

 

[jessitarexcel]

I have confirmed that the sheet name is correct and have taken a screenshot to show you below. I copied the name of the sheet to make extra sure and I know that part is correct. That isn't the issue. I am about to try the alternative code.

 

[jessitarexcel]

Are you sure your sheet name is correct? It used to be "Stat Dec and COW Table - New" and then you renamed it. Are you sure there are no unseen spaces in the new sheet name. I've tested the code and it copies the table with header to the document. Trial this...

Sub test()
Dim sht As Worksheet, sh As Worksheet, tbl As ListObject
For Each sht In ThisWorkbook.Sheets
MsgBox sht.Name & "   " & Len(sht.Name)
Set sh = ThisWorkbook.Sheets(sht.Name)
For Each tbl In sh.ListObjects
MsgBox tbl.Name & "   " & Len(tbl.Name)
Next tbl
Next sht
'Stat Dec and COW Table 22
'StatDecTable 12
End Sub

Dave

Ok now I have tried this macro, it bought up a bunch of message boxes with table names and sheet names with numbers attached. I assume based on the code that is what is meant to happen. You are checking for object names?

 

[jessitarexcel]

Okay wow, that shows how many objects there are. This is a development document and there have been sooooooo many requested changes, that is why there are so many objects. Do you think that is the issue? Maybe I should make a new clean template with less sheets and objects from the original version and then that might make things a bit easier do you think?

 

[jessitarexcel]

Still going....that is a lot of sheets and tables that aren't being used but are on hidden tabs.

 

[NdNoviceHlp]

It lists the sheet name and then table(s) that are on it. We are looking for this output (at bottom of previous test sub)....
'Stat Dec and COW Table 22
'StatDecTable 12
I see they are both listed but it should list the sheet "Stat Dec and COW Table 22" followed by the table(s) that are on it ie. "StatDecTable 12" should follow before the next sheet name. Does this happen? Dave

 

[jessitarexcel]

It lists the sheet name and then table(s) that are on it. We are looking for this output (at bottom of previous test sub)....
'Stat Dec and COW Table 22
'StatDecTable 12
I see they are both listed but it should list the sheet "Stat Dec and COW Table 22" followed by the table(s) that are on it ie. "StatDecTable 12" should follow before the next sheet name. Does this happen? Dave

The order that the images were posted in is the order they appeared on the screen, so it seems that the issue is that I copied this table from another tab?

 

[jessitarexcel]

I honestly think this template is a mess because of all of the changes. I am going to tidy up the template first before trying this again.

Are the objects meant to all have unique numbers? There are 3 elements with the number 23, two with the number 12.

Oh also, the Stat Dec and COW Table 22 is the old name of the tab. The new name of the tab is COW and Stat Dec Template. Anyway, I am going to tidy the template and try the VBA again. I will let you know how I go because I really want to resolve this issue.

 

[NdNoviceHlp]

Set tbl = ThisWorkbook.Worksheets("COW and Stat Dec Template") _
.ListObjects("StatDecTable").Range

The numbers are simply how many characters are in the name of the object just to determine if any spaces were in their names. Dave
edit: Are you sure that's the name of the sheet with the table? Your images indicate that the table is on the sheet named Stat Dec Template - Not Ready

Set tbl = ThisWorkbook.Worksheets("Stat Dec Table - Not Ready") _
.ListObjects("StatDecTable").Range

 

[jessitarexcel]

Set tbl = ThisWorkbook.Worksheets("COW and Stat Dec Template") _
.ListObjects("StatDecTable").Range

The numbers are simply how many characters are in the name of the object just to determine if any spaces were in their names. Dave
edit: Are you sure that's the name of the sheet with the table? Your images indicate that the table is on the sheet named Stat Dec Template - Not Ready

Set tbl = ThisWorkbook.Worksheets("Stat Dec Table - Not Ready") _
.ListObjects("StatDecTable").Range

There have been numerous instances of the code tried, I even tried adding in an auto caption and I referred to different sections of the sheet.

I am absolutely positive that the sheet is called COW and Stat Dec Tables (was table but another change was made) and the table name is StatDecTable.

 

[jessitarexcel]

I have just created a new, cleaner template and will post the code I am about to try and see if that works. The code doesn't seem to like the spaces and the word "and". I have now gone and changed all of the sheet names to remove any spaces in them.