Consulting

Results 1 to 5 of 5

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

  1. #1
    VBAX Newbie
    Joined
    Apr 2020
    Posts
    1
    Location

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

    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
    OnErrorResumeNext

    '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 IsNothingThenSet 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

    OnErrorGoTo 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

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Trial using a with statement something like...
    With  myDoc.Tables(1)
    .AutoFitBehavior (wdAutoFitWindow)
    End With

    HTH. Dave

  3. #3
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •