"The remote server machine does not exist" and insert caption

[azbs]

I'm beginner in VBA macros. I try to write a macro to copy table from excel to word.

I use the code below:

When I defined row height after this line "WordTable.AutoFitBehavior (wdAutoFitWindow)" I have a message "The remote server machine does not exist".

The second problem is - how can I add table caption above table in Word?

Sub ExcelRangeToWord()

'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
(VBE > Tools > References > Microsoft Word 12.0 Object Library)
'SOURCE: www.TheSpreadsheetGuru.com

Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table

'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False

'Copy Range from Excel
Set tbl = ThisWorkbook.Worksheets(Sheet1.Name).ListObjects("Table1").Range

'Create an Instance of MS Word
On Error Resume Next

'Is MS Word already opened?
Set WordApp = GetObject(class:="Word.Application")

'Clear the error between errors
Err.Clear

'If MS Word is not already open then open MS Word
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

'Handle if the Word Application is not found
If Err.Number = 429 Then
MsgBox "Microsoft Word could not be found, aborting."
GoTo EndRoutine
End If

On Error GoTo 0

'Make MS Word Visible and Active
WordApp.Visible = True
WordApp.Activate

'Create a New Document
Set myDoc = WordApp.Documents.Add

'Copy Excel Table Range
tbl.Copy

'Paste Table into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False

'Autofit Table so it fits inside Word Document
Set WordTable = myDoc.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)

EndRoutine:
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True

'Clear The Clipboard
Application.CutCopyMode = False

End Sub

[Dave]

Trial using a with statement something like...

With myDoc.Tables(1)
.AutoFitBehavior (wdAutoFitWindow)
End With
HTH. Dave

[Artik]

Before you make line WordTable.AutoFitBehavior (wdAutoFitWindow), make sure the table has been pasted (If Not WordTable is Nothing). As far as I'm not mistaken, pasting the Excel range into a Word document can sometimes have problems (either the Excel range has not been copied or the range has not been pasted into the Word document).

Artik

[gmayor]

The following should work (and set the row height)

Sub ExcelRangeToWord()

'Modified by Graham Mayor - https://www.gmayor.com - Last updated - 23 Apr 2020
'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'Uses late binding so no need to set a reference to Word

Dim tbl As Excel.Range
Dim WordApp As Object
Dim myDoc As Object
Dim WordTable As Object

'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False

'Declare Named Range from Excel
Set tbl = ThisWorkbook.Worksheets(1).Range("Table1")

'Copy Excel Table Range
tbl.Copy

'Create an Instance of MS Word
On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err Then
Err.Clear
Set WordApp = CreateObject("Word.Application")
End If

'Handle if the Word Application is not found
If Err.Number = 429 Then
MsgBox "Microsoft Word could not be found, aborting."
GoTo EndRoutine
End If

On Error GoTo 0

'Make MS Word Visible and Active
WordApp.Visible = True
WordApp.Activate

'Create a New Document
Set myDoc = WordApp.Documents.Add


'Paste Table into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False

'Autofit Table so it fits inside Word Document
Set WordTable = myDoc.Tables(1)

With WordTable
.AutoFitBehavior 2 'wdAutoFitWindow
.Range.Rows.HeightRule = 2 'wdRowHeightExactly
.Range.Rows.Height = WordApp.CentimetersToPoints(1) 'set row height to 1 cm
End With

EndRoutine:
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True

'Clear The Clipboard
Application.CutCopyMode = False
Set WordApp = Nothing
Set WordTable = Nothing
Set myDoc = Nothing
Set tbl = Nothing
End Sub

[Artik]

Graham, your code looks correct. :)
However, sometimes there are problems with copying and pasting. I described the problem on the Polish forum http://www.excelforum.pl/viewtopic.php?p=370570 (Google Translator may be your friend).

Artik