Out of Range Error

[cbrf23]

I dont understand why I'm getting an out of range error on this.

I have a macro in my personal.xls workbook which runs when it opens (so essentially when excel starts this macro runs). It checks to see if a file is open, and if not, it opens it.

For some reason, I'm getting an error "out of range". The file still opens correctly, so I dont understand what this is all about...

Function IsWorkbookOpen(WorkbookName As String) As Boolean
Dim wb As Workbook
For Each wb In Excel.Workbooks
If UCase$(wb.Name) = UCase$(WorkbookName) Then
IsWorkbookOpen = True
Exit Function
End If
Next
End Function
 
 
Sub OPEN_MYFILE()
On Error GoTo ERRHANDLER 
If IsWorkbookOpen("MYFILE.xls") = True Or IsWorkbookOpen("MYFILE.xls [Shared]") Then
MsgBox "MY FILE ALREADY OPEN"
Else
Workbooks.Open ("X:\xxxxxxxxx\MYFILE.xls") '<----out of range error
Run "HIDE_MYFILE"
End If
Exit Sub
ERRHANDLER:
If Err.Number = 1004 Then
Exit Sub
Else
MsgBox "UNKNOWN ERROR"
End If
Exit Sub 
End Sub

 

[Hermanito]

Change

Workbooks.Open ("X:\xxxxxxxxx\MYFILE.xls")

into either

Call Workbooks.Open("X:\xxxxxxxxx\MYFILE.xls")

or

Workbooks.Open "X:\xxxxxxxxx\MYFILE.xls"

 

[cbrf23]

I tried both ways you suggested, still receiving the same error. Hit debug, still highlights that line.

 

[Hermanito]

Well, I have to ask the obvious question then: are you sure the path you provide is correct? If yes, are you really sure? If yes, are you really really really sure?

I tested the lines with me on an existing workbook, correct path, and it works just fine. Contrary to what I thought, even your original version opened the file without problems...

 

[cbrf23]

Sorry about the delay in response, holiday weekend Yes, I'm positive the path is correct. Even though I get the error, the file still opens. lol.

 

[Hermanito]

Found on another forum, credits to RichardSchollar:

...on the computer where this is failing, go Start>Control Panel>Folder Options>View tab and make sure "Hide extensions for known file types" is unchecked. Then it should work.

 

[Norie]

Try removing the On Error..., that could be hiding errors.

 

[cbrf23]

Hi all,

I fixed the error. I'm not sure what was causing it to be quite honest. I did double check that my hide extensions for known file types option was unchecked and it was unchecked, so that was not the issue. I ended up monkeying around with things a bit and changed the order of operations of the on_open macros and everything is working now with no errors.

Thanks for all the help! Sorry I dont have more insight into the exact solution to share with you.