Instantiating Excel - "The remote Server Machine does not Exist or is unavailable"

Question

I have a Sub in a form that executes a bunch of code (including outputting an Excel file to a network folder) and then calls two subs, which I've pasted below.

Call FormatExcelFile(Path) 'call function that formats the file 

Call WorkBook_RO(Path, False) 'call function  that sets password on Excel file

A few times the user has received the following error message: "The remote Server Machine does not Exist or is unavailable"

I'm pretty sure that the error occurs when Access attempts to execute the WorkBook_RO sub.  Further digging on the internet leads me to believe that it has something to do with how I'm instantiating Excel.

Note: I added the word "New" to the  Set XlApp = New Excel.Application statement in the WorkBook_RO sub today, had the user rerun, and the code executed successfully.

Could you let me know if there's anything in the code below that's incorrect or not optimal/ best practice?

Here's the code:

Public Sub FormatExcelFile(Path As String)

Dim applicationExcel As Excel.Application

On Error Resume Next

Set applicationExcel = CreateObject("Excel.Application")

applicationExcel.Workbooks.Open FileName:=Path

applicationExcel.Cells.Select

applicationExcel.Cells.EntireColumn.AutoFit

 applicationExcel.Sheets("VOM").Select

applicationExcel.Cells.Select

applicationExcel.Cells.EntireColumn.AutoFit

applicationExcel.Range("A1").Select

'IncompleteReqts

applicationExcel.Sheets("IncompleteReqts").Select

applicationExcel.Cells.Select

applicationExcel.Cells.EntireColumn.AutoFit

applicationExcel.Range("A1").Select

'

applicationExcel.Sheets("Demands").Select

applicationExcel.Range("A1").Select

applicationExcel.ActiveWorkbook.Save

applicationExcel.Quit

Set applicationExcel = Nothing

End Sub

_____________________________________________

Sub WorkBook_RO(FileToRead, bskip As Boolean)

Dim strFileName As String

Dim XlApp As Excel.Application

Dim xlBook1 As Excel.Workbook

If bskip = True Then Exit Sub

strFileName = FileToRead

      ****Set XlApp = New Excel.Application 'Just added the word "NEW" here today

Set xlBook1 = XlApp.Workbooks.Open(strFileName)

'xlBook1.C

xlBook1.WritePassword = "password"

XlApp.ActiveWorkbook.Save

XlApp.Quit

Set XlApp = Nothing

End Sub

Answer 1

I have the below code after execution it has to update the test data sheet as Executed in the Status column, but while updating I am getting error message as 

" The remote server machine does not exist or is unavailable: 'cells'

Line (55): "objExcelSheet.cells(intcounter,3).Value = "Executed"".

below is my code.

Option Explicit

''**********************************************************************

'Script Name: DriverScript

'Author:

'Date of Design: 12-Apr-2013

'Date of last Revision: 12-Apr-2013

'Functions Called:

'Description:  'This Script performs:-

'1.Logging into the application

'2.Closing Excel spreadsheets which are open

'3.Importing the TestCase spreadsheet

'4.Executing the Test Case by calling the corresponding actions

'******************************************************************

SystemUtil.CloseProcessByName("IExplore.EXE")

SystemUtil.CloseProcessByName("EXCEL.exe")

'***** Opening and retriving data from excel file

Dim objExcel

Dim intCntrl

Dim strRun

Dim strStatus

Dim objworkbook

Dim objExcelSheet

Dim introwCount

Dim intcolCount

Dim intcounter

Set objExcel=CreateObject("Excel.Application")

Set objworkbook=objExcel.workbooks.open("D:\AutomationTesting\TestData\TestData.xls")

Set objExcelSheet=objworkbook.worksheets("Run Sheet")

introwCount=objExcelSheet.usedrange.rows.count

intcolCount=objExcelSheet.usedrange.columns.count

For intcounter=2 to introwCount-1

intCntrl=objExcelSheet.cells(intcounter,1)

strRun=objExcelSheet.cells(intcounter,2)

strStatus=objExcelSheet.cells(intcounter,3)

If strRun ="Yes" then

RunAction "Action1 ["&intCntrl&"]", oneIteration

Wait(20)

objExcel.objworkbook("TestData").Activate

objExcelSheet.cells(intcounter,3).Value = "Executed"

End If

objExcel.DisplayAlerts = False

Next

'Set objExcelSheet = Nothing

objExcel.DisplayAlerts = False

objExcel.Save

objworkbook.Save

objExcel.DisplayAlerts = True

objExcel.Quit

Set objExcel = Nothing

Please look into this and do the neeful.

Regards,

Sreekanth Peetla

Answer 2

There is a lot wrong with this code imho but I'm not sure it explains the error. Here are some opportunities for improvement:

1. You say "I'm pretty sure that the error occurs...". How about "I'm 100% sure, and it happens exactly on this line"? That is certainly possible and a best practice. You accomplish it by (a) installing the free "MZ Tools" and using it to add line numbers, and (b) by adding a good error handler in every procedure. "On Error Resume Next" is the WORST error handler. An improvement would be:

public sub mySub

on error goto Err_Handler

'code goes here

Exit_Handler:

'possible cleanup goes here

Exit sub

Err_Handler:

msgbox "ErrNo=" & Err.Number & " on line " & Erl & ": " & Err.Description

resume Exit_Handler

end sub

2: Use early binding or late binding, not both. If you know all users will have Excel installed, use early binding:

dim objXL as Excel.Application   'Requires a reference to Microsoft Excel [version] Object Library in Tools > References

set objXL = new Excel.Application

'code goes here

set objXL = Nothing

Replace all code that uses late binding (CreateObject).

3: Procedure arguments need to be typed and ByVal, unless a special case applies.

So your line:

Sub WorkBook_RO(FileToRead, bskip As Boolean)

becomes:

Public Sub WorkBook_RO(Byval FileToRead as String, ByVal bskip As Boolean)

4: Remove redundant code

    strFileName = FileToRead

    Set xlBook1 = XlApp.Workbooks.Open(strFileName)

This should be written as:

    Set xlBook1 = XlApp.Workbooks.Open(FileToRead)

Another example:

Public Sub WorkBook_RO(Byval FileToRead as String, ByVal bskip As Boolean)

    If bskip = True Then Exit Sub

It is curious to pass in an argument to decide not to run a procedure. There may be rare cases where that is correct, but mostly one would write in the calling routine:

if not bskip then WorkBook_RO myFileName

By the way, extra points if you realized that this line:

  If bskip = True Then Exit Sub

needs to be fixed up like this:

  If bskip = True Then Goto Exit_Handler

Finally, make sure you have Option Explicit in every module. It wasn't clear from your code fragment if you had set it.