Getting "Subscript out of Range" error while trying to open an random workbook which is already open

[Koranga]

Hello All,

I am new VBA and Macros. I am trying to open a workbook and if it is already open just activating that workbook. But I am getting "Subscript out of Range" error again. Below is the snippet of my code.

Sub Activating()

Dim DestFile As String
Dim wb As Workbook
DestFile = Application.GetOpenFilename()
ret = Isworkbookopen(DestFile)
If ret = False Then
'open file
Set wb = Workbooks.Open(DestFile)
Else
'Just Activate the workbook
Workbooks(DestFile).Activate 'Here I am getting Subscript of of Range Error
End If
End Sub

Function Isworkbookopen(Filename As String)

Dim ff As Long, ErrNo As Long
Dim wkb As Workbook
Dim nam As String
wbname = Filename
On Error Resume Next


ff = FreeFile()
Open Filename For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: Isworkbookopen = False
Case 70: Isworkbookopen = True
Case Else: Error ErrNo
End Select


End Function

Here the file that I am trying to open is already open, name of the file can vary so it is also difficult to create a object of workbook class for that open workbook.

It would be great if anyone could help me out, as I had tried a lot and finally posting this question.
Thanks in advance.

 

[Norie]

You'll need to remove the file path from DestFile.

 

[Koranga]

I tried that also.

Filename = Split(DestFile, "")(UBound(Split(DestFile, "")))
Workbook(Filename).Close

But still getting the same error.

 

[Koranga]

I tried that also.

Filename = Split(DestFile, "")(UBound(Split(DestFile, "")))
Workbook(Filename).Activate

But still getting the same error.

Edit: Workbook(Filename).Activate

 

[Norie]

That won't get you the filename, try this.

    Filename = Split(DestFile, "\")(UBound(Split(DestFile, "\")))

 

[Koranga]

@Noire Now it is working as excepted. Thanks a lot.