CODEWITHSUNDEEP
excelvba
RobRaj
RobRaj

Reputation: 43

Opening Excel file in VBA: Run-time error ‘462’: The remote server machine does not exist or is unavialiable

I need to create a macro that opens an Excel file, and saves some files inside the Workbook. The problem is that when I want to run macro more than once in a short time (which unfortunately I need to do), I receive error '462': The remote server machine does not exist or is unavialiable.

I've read about this and tried to fix it: I've created a special module at the beginning to kill Excel process: 

Call KillExcel

Function KillExcel()

Dim oServ As Object
Dim cProc As Variant
Dim oProc As Object

Set oServ = GetObject("winmgmts:")
Set cProc = oServ.ExecQuery("Select * from Win32_Process")

For Each oProc In cProc

    'Rename EXCEL.EXE in the line below with the process that you need to Terminate.
    'NOTE: It is 'case sensitive

    If oProc.Name = "EXCEL.EXE" Or oProc.Name = "EXCEL.EXE *32" Then
     ' MsgBox "KILL"   ' used to display a message for testing pur
      errReturnCode = oProc.Terminate()
    End If
Next

End Function

But unfortunately even if I close this processes I still receive this error. The part of code where I use Excel looks like this: 

Dim ark As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set ark = Excel.Workbooks.Open(FileName:=scexcel)
Set xlSheet = ark.Worksheets("Sheet1")
a = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row + 1


Cells(a, 2).Value = "ABC"
Cells(a, 3).Value = "DEF"
Cells(a, 4).Value = "GHI"
Cells(a, 5).Value = "JKL"

a = a + 1

Set xlSheet = Nothing
ark.Close SaveChanges:=True

Set ark = Nothing

If it helps, the macro fails every time I run it multiple times in a short time period at line: 

Set ark = Excel.Workbooks.Open(FileName:=scexcel)

Note that scexcel is the path of Excel file.

Can you please help me with this problem?

Upvotes: 2

Views: 2805

Answers (1)

Rory
Rory

Reputation: 34075

This should work for you (make sure you kill any hidden Excel.exe instances left over from your previous code first):

Dim ark                   As Excel.Workbook
Dim xlSheet               As Excel.Worksheet
Dim a                     As Long

Set ark = GetObject(scexcel)
ark.Application.Visible = True
Set xlSheet = ark.Worksheets("Sheet1")
With xlSheet
    a = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    .Cells(a, 2).Value = "ABC"
    .Cells(a, 3).Value = "DEF"
    .Cells(a, 4).Value = "GHI"
    .Cells(a, 5).Value = "JKL"
End With
Set xlSheet = Nothing
ark.Close SaveChanges:=True

Set ark = Nothing

Upvotes: 1

Related Questions