code not executing in 1 particular remote desktop

[excelguru123]

Hi all, I am running this code that has to work through multiple remote desktops. The issue is that it works from my own desktop and all of the other remote desktops except for one where it gives a run-time error 9: subscript out of range.

Prior to that, my code is calling a drive, opening a spreadsheet through the pathway:

File = Cells(7, 4)
Path = Cells(7, 5)

ChDrive Left(Path, 1)
ChDir Path

Set wbMyWorkbook = Workbooks.Open(FileName:=File, UpdateLinks:=False, ReadOnly:=True)
wbMyWorkbook.Activate
[B]Set wsMyWorksheet = wbMyWorkbook.Worksheets("Sheet1")[/B]

I know Path works fine because it opens "wbMyWorkbook" but then it gives an error on the last line bolded. I added "wbMyWorkbook.Activate" incase it wasn't the active workbook but it still doesn't work. Only 1 of my remote desktops is running into this error. Any ideas would be much appreciated. Thanks.

 

[DanteAmor]

Set wsMyWorksheet = wbMyWorkbook.Worksheets("Sheet1")

Have you already checked that the sheet with the name "Sheet1" exists in that book?

Try:

Sub open_book()
  Dim wbMyWorkbook As Workbook
  Dim wsMyWorksheet As Worksheet
  Dim sFile As String, sPath As String, sName As String
    
  sFile = Cells(7, 4)
  sPath = Cells(7, 5)
  sName = "Sheet1"
  
  ChDrive Left(sPath, 1)
  ChDir sPath
  
  Set wbMyWorkbook = Workbooks.Open(Filename:=sFile, UpdateLinks:=False, ReadOnly:=True)
  If Evaluate("ISREF('" & sName & "'!A1)") Then
    Set wsMyWorksheet = wbMyWorkbook.Worksheets(sName)
  Else
    MsgBox "Sheet1 does not exists"
  End If
End Sub

 

[excelguru123]

Have you already checked that the sheet with the name "Sheet1" exists in that book?

Try:

Sub open_book()
  Dim wbMyWorkbook As Workbook
  Dim wsMyWorksheet As Worksheet
  Dim sFile As String, sPath As String, sName As String
   
  sFile = Cells(7, 4)
  sPath = Cells(7, 5)
  sName = "Sheet1"
 
  ChDrive Left(sPath, 1)
  ChDir sPath
 
  Set wbMyWorkbook = Workbooks.Open(Filename:=sFile, UpdateLinks:=False, ReadOnly:=True)
  If Evaluate("ISREF('" & sName & "'!A1)") Then
    Set wsMyWorksheet = wbMyWorkbook.Worksheets(sName)
  Else
    MsgBox "Sheet1 does not exists"
  End If
End Sub

Okay I tried this code and gives the message "Sheet1 does not exist" as indicated in your code. My question then is, why is it not detecting Sheet1? It does exist and it works in all my other remote desktops and how can I fix it?

A temporary and inefficient thing that seems to work is repeating this part of the code twice like this:

Set wbMyWorkbook = Workbooks.Open(FileName:=File, UpdateLinks:=False, ReadOnly:=True)
wbMyWorkbook.Activate

Set wbMyWorkbook = Workbooks.Open(FileName:=File, UpdateLinks:=False, ReadOnly:=True)
wbMyWorkbook.Activate
Set wsMyWorksheet = wbMyWorkbook.Worksheets("Sheet1")

 

[JEC]

Try enabling the AccessibilityCplAdmin library on that desktop