PDA

View Full Version : Solved: Check if workbook is open or is active workbook



Rejje
11-25-2010, 10:31 AM
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...

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

Bob Phillips
11-25-2010, 10:41 AM
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

Rejje
11-25-2010, 01:05 PM
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
11-25-2010, 01:09 PM
What value do you have in Range("V_50100")?

Rejje
11-25-2010, 01:19 PM
C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOST MALL\KUNDPOST MALL.xlsm

Mall in Swedish = Template

Bob Phillips
11-25-2010, 02:07 PM
Try this variation



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

Rejje
11-25-2010, 02:52 PM
Still the same thing - MsgBox never occurs...

Bob Phillips
11-25-2010, 03:48 PM
Can you post that template file?

mohanvijay
11-25-2010, 11:25 PM
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
12-01-2010, 11:35 AM
Works perfectly!

Bob Phillips
12-02-2010, 03:56 PM
Maybe mine didn't work because I mis-spelled filename in the error-trapped setting wb variable and you don't have Option Explicit.