Solved: Check if workbook is open or is active workbook

[Rejje]

Hi! Vba noob in need...

Problem: Below sub only checks if Range("V_50100").Value is active workbook.

I'd like for it to check IF EITHER Range("V_50100").Value is active workbook OR Range("V_50100").Value is open anyhow THEN MsgBox "Is allready open!".

I can't get it to work...

[VBA]Sub OpenTemplate()
If UCase(ActiveWorkbook.FullName) = UCase(Range("V_50100").Value) Then
'function: OrIsOpen
MsgBox "Is allready open!"

Else
Workbooks.Open Range("V_50100").Value
End If
End Sub[/VBA]

[Bob Phillips]

[vba]

Sub OpenTemplate()
Dim wb As Workbook

On Error Resume Next
Set wb = Workbooks(Range("V_50100").Value)
On Error GoTo 0
If Not wb Is Nothing Then

'function: OrIsOpen
MsgBox "Is allready open!"
Else

Workbooks.Open Range("V_50100").Value
End If
End Sub
[/vba]

[Rejje]

Hmm - didn't work perfectly...

When I'm from a another workbook (template saved as something else) it workes fine to open the template.

However when I'm working in the template or if have the template open when working in another workbook and call the macro by pushing the button I get the regular "Microsoft Office Excel" / "Template.xlsm is allready open. If you open etc... Do you want to open it?" YES/NO. I never get MsgBox.

What's could be the problem?

[Bob Phillips]

What value do you have in Range("V_50100")?

[Rejje]

C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOST MALL\KUNDPOST MALL.xlsm

Mall in Swedish = Template

[Bob Phillips]

Try this variation

[vba]

Sub OpenTemplate()
Dim wb As Workbook
Dim filename As String
Dim pos As Long

With Range("V_50100")

filename = .Value
pos = InStrRev(.Value, "\")
If pos > 0 Then filename = Right$(.Value, Len(.Value) - pos)

On Error Resume Next
Set wb = Workbooks(filenamee)
On Error GoTo 0
If Not wb Is Nothing Then

'function: OrIsOpen
MsgBox "Is allready open!"
Else

Workbooks.Open .Value
End If
End With
End Sub
[/vba]

[Rejje]

Still the same thing - MsgBox never occurs...

[Bob Phillips]

Can you post that template file?

[mohanvijay]

try this

Dim file_path, file_name As String
Dim pos, wb_count, i As Long
Dim opend As Boolean
opend = False

file_path = Range("v_50100").Value
pos = InStrRev(file_path, "\")
file_name = Right(file_path, Len(file_path) - pos)
wb_count = Workbooks.Count

For i = 1 To wb_count
    If Workbooks(i).Name = file_name Then
        MsgBox "Is allready open"
        opend = True
        Exit For
    End If
Next i

If opend = False Then Workbooks.Open file_path

[Rejje]

Works perfectly!

[Bob Phillips]

Maybe mine didn't work because I mis-spelled filename in the error-trapped setting wb variable and you don't have Option Explicit.