Run timer Error 462 The remote server machine does not exist ...

[bisel]

Hello All,

Hoping someone can help me.

I have created, with the help of experts on this site, a macro which takes some ranges in the Excel and then creates a Word document where the ranges are pasted as tables.

I am running into a problem and in searching I find that the problem is not uncommon, but the cause is not immediately apparent to me.

Here is the macro that I am running ...

Sub Excel_to_Word5()


Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table
Dim tablenum As String


Dim i As Integer


i = 1


Set wdApp = New Word.Application ' Forces new Word application every time.  Prevent error 462.
Set wdDoc = wdApp.Documents.Add


Application.CutCopyMode = False 'clear clipboard


With wdApp
  .Visible = True
    
' Set up the Word document layout
  With wdDoc
    .PageSetup.Orientation = 1 'wdOrientLandscape
[COLOR=#ff0000]    .PageSetup.LeftMargin = InchesToPoints(0.5)[/COLOR]
    .PageSetup.RightMargin = InchesToPoints(0.5)
    .PageSetup.TopMargin = InchesToPoints(0.5)
    .PageSetup.BottomMargin = InchesToPoints(0.5)
    
For i = 1 To 12


tablenum = "sheet13table" & i


' Copy the first range from Excel to Word
    Range(tablenum).Copy
    .Range.Characters.Last.Paste
    Application.CutCopyMode = False 'clear clipboard
    If i = 12 Then
        .Range.InsertAfter vbCr ' Insert new line
        Else
        .Range.InsertAfter Chr(12) ' Insert page break
    End If
    
' Format table
    Set myTable = wdDoc.Tables(i) ' Sets variable myTable to Table(1) in document
        With myTable
'            .Columns.Add ' Adds a column to the right of the table preparing for last paste
            .Range.Font.Size = 9
            .Range.Font.Bold = False
            .AutoFitBehavior wdAutoFitWindow 'Autofit this table to window
            .Rows(1).HeadingFormat = True 'Set first row as table header
        End With 'myTable
Next i


  End With 'wdDoc
  
End With ' wdApp


Application.CutCopyMode = False 'clear clipboard


End Sub

The macro runs OK, but after the successful execution, if I attempt to run the macro a second time, I frequently get an Error '462' The remote server machine does not exist or is unavailable. The when I click "debug", it highlights the line ".PageSetup.LeftMargin = InchesToPoints(0.5)" that I colored in red text in the above.

Appreciate any insight into why this occurs.

Best regards,

Steve

 

[bobsan42]

It is a good practice (for several reasons) to destroy the objects that were created (if you don't need them anymore).
And because you say that first run is without problems I believe that it may help in your case.
Before End Sub, just after you clear the clipboard put this:

Set wdApp = Nothing
Set wdDoc = Nothing

See if it helps.

 

[bisel]

It is a good practice (for several reasons) to destroy the objects that were created (if you don't need them anymore).
And because you say that first run is without problems I believe that it may help in your case.
Before End Sub, just after you clear the clipboard put this:

Set wdApp = Nothing
Set wdDoc = Nothing

See if it helps.

Thanks for the tip, Bobsan, I included those two lines in the VBA code.

Unfortunately, it did not make any difference.

Regards,

Steve

 

[bisel]

I made a change to the code that seems to have resolved this problem.

In the original code, the first several lines were this ...

Sub Excel_to_Word5()
 
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table
Dim tablenum As String
 
Dim i As Integer
 
i = 1
 
Set wdApp = New Word.Application ' Forces new Word application every time.  Prevent error 462.
Set wdDoc = wdApp.Documents.Add
 
Application.CutCopyMode = False 'clear clipboard
 
With wdApp
  .Visible = True
   
' Set up the Word document layout
  With wdDoc
    .PageSetup.Orientation = 1 'wdOrientLandscape
    .PageSetup.LeftMargin = InchesToPoints(0.5)
    .PageSetup.RightMargin = InchesToPoints(0.5)
    .PageSetup.TopMargin = InchesToPoints(0.5)
    .PageSetup.BottomMargin = InchesToPoints(0.5)
   
…
…
 
End Sub

I changed it to this ...

Sub Excel_to_Word5()


Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table
Dim tablenum As String


Dim i As Integer


i = 1


Set wdApp = New Word.Application ' Forces new Word application every time.  Prevent error 462.
Set wdDoc = wdApp.Documents.Add


Application.CutCopyMode = False 'clear clipboard


wdApp.Visible = True
   
    
' Set up the Word document layout
[COLOR=#0000ff]  With wdDoc[/COLOR]
[COLOR=#0000ff]    .PageSetup.Orientation = 1 'wdOrientLandscape[/COLOR]
[COLOR=#0000ff]    .PageSetup.LeftMargin = wdApp.InchesToPoints(0.5)[/COLOR]
[COLOR=#0000ff]    .PageSetup.RightMargin = wdApp.InchesToPoints(0.5)[/COLOR]
[COLOR=#0000ff]    .PageSetup.TopMargin = wdApp.InchesToPoints(0.5)[/COLOR]
[COLOR=#0000ff]    .PageSetup.BottomMargin = wdApp.InchesToPoints(0.5)[/COLOR]

....
....

To summarize, I removed the With ... End With wdApp statement explicitly used wdApp as part of the With ... End With wdDoc statement ... as I highlighted in blue text above.