Error manipulating opened file in vba

Question

My code has to open a file (.xls) but is possible that file was opened before. If the file is not opened works perfectly but if is opened appear Runtime error(9) Subscript out of range, marked as * bellow:

Dim src As Workbook
Dim file as String

file = "c:/tmp/file.xls"

If IsFileOpen(file) = True Then
   Workbooks(file).Activate  '* <====
   Set src = ActiveWorkbook
Else
    Set src = Workbooks.Open(file, True, True)  
End If


Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next   ' Turn error checking off.
filenum = FreeFile()   ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum          ' Close the file.
errnum = Err           ' Save the error number that occurred.
On Error GoTo 0        ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

    ' No error occurred.
    ' File is NOT already open by another user.
    Case 0
     IsFileOpen = False

    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    Case 70
        IsFileOpen = True

    ' Another error occurred.
    Case Else
        Error errnum
End Select

End Function

There's several duplicates of this [here](http://stackoverflow.com/questions/19818319/updated-the-vba-code-and-still-it-gives-me-a-subscript-out-of-range-error) and [here](http://stackoverflow.com/questions/19817399/subscript-out-of-range-error-in-this-excel-vba-script) — InbetweenWeekends, Nov 05 '15 at 13:35
My error is that I cannot access an opened file, only a closed file (into else statement) — user1801745, Nov 05 '15 at 13:53

score 2 · Accepted Answer · answered Nov 05 '15 at 15:53

The problem is how you're activating the workbook. Excel is expecting a workbook name (or index) not a path and name. Try this (change file name to suit your situation):

Sub test()
Dim src As Workbook
Dim file As String

file = "c:/junk/foo.xlsx"

If IsFileOpen(file) = True Then
   Workbooks("foo.xlsx").Activate 
   Set src = ActiveWorkbook
Else
    Set src = Workbooks.Open(file, True, True)
End If

End Sub

Error manipulating opened file in vba

1 Answers1