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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.