Alok Kumar
asked on
Control Pop up window
I am trying to open a file and if the file is already open, then a pop up window appears ... mentioning the file is already open then we press No button. I want a vba to control this of pressing "No" button through excel vba code.
Dim str As String
str = "C:\Users\Desktop\Test.xls m"
On Error GoTo Solution
Workbooks.Open str
'------
Exit Sub
Solution:
MsgBox "file is open"
Resume Next
Dim str As String
str = "C:\Users\Desktop\Test.xls
On Error GoTo Solution
Workbooks.Open str
'------
Exit Sub
Solution:
MsgBox "file is open"
Resume Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Crystal
I just copied and pasted the file name and forgot to edit
This is my usual example with this Function
I just copied and pasted the file name and forgot to edit
This is my usual example with this Function
Sub TestOpen()
If Not wbOpen("Suppliers.xls") Then
Workbooks.Open "Suppliers.xls"
End If
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Roy, thanks. Your code is simpler. I like the idea and it is a nice function to add to my library.
btw, if you don't want to handle a return value (ie: x) then you can test the result without assigning it, for instance:
btw, if you don't want to handle a return value (ie: x) then you can test the result without assigning it, for instance:
Sub testNoObjectVariable()
On Error Resume Next
Dim sName As String
sName = ActiveWorkbook.Name
Err.Clear
If Workbooks(sName) Is Nothing Then
MsgBox sName & " is not open" 'this will not execute
End If
sName = "bogus " & sName
Err.Clear
If Workbooks(sName) Is Nothing Then
MsgBox sName & " is not open" 'this will (probably!) execute
End If
End Sub
poster didn't come back -- probably new to VBA
realize that you need just the filename (Test.xlsm) to use the given code, not the path and filename