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.xlsm"

On Error GoTo Solution
Workbooks.Open str

'------
Exit Sub
Solution:

MsgBox "file is open"
Resume Next
Alok KumarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I would suggest using a function to test if a workbook is open
Sub TestOpen()
If Not wbOpen("C:\Users\Desktop\Test.xlsm") Then
    Workbooks.Open "C:\Users\Desktop\Test.xlsm"
End If

End Sub

Alternative:

Private Function WorkbookIsOpen(FileName) As Boolean
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(FileName)
If Err = 0 Then
WorkbookIsOpen = True
Else
WorkbookIsOpen = False
End If
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I agree with Roy -- rather than dismissing the dialog box if it appears, ensure it does not ~

realize that you need just the filename (Test.xlsm) to use the given code, not the path and filename
0
Roy CoxGroup Finance ManagerCommented:
Hi Crystal

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

Open in new window

0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Roy, I modified your code to open the file if it is not already open. It takes path\filename as a parameter, sets a variable for just the filename to test and opens path\filename if not already open.  It returns the workbook object so it can be used in the code for whatever called it. I would also make this public function so it can be called from anywhere :)
Public Function WorkbookOpenIfNot(pPathFileName) As Workbook
'160917 strive4peace
'based on code written by Roy Cox
'return a workbook object
   On Error GoTo Proc_Err

   Dim wb As Workbook
   Dim sFilename As String _
      , iPos As Integer
   iPos = InStr(pPathFileName, "\")
   If iPos > 0 Then
      sFilename = Mid(pPathFileName, iPos + 1)
   Else
      'maybe just the filename was passed
      sFilename = pPathFileName
   End If
   
   On Error Resume Next
   Set wb = Workbooks(sFilename)
   If Err.Number = 0 Then
      Set WorkbookOpenIfNot = wb
   Else
      'not already open -- try to open
      If iPos > 0 Then
         Err.Clear
         Set wb = Workbooks.Open(pPathFileName)
         If Not Err.Number > 0 Then
            Set WorkbookOpenIfNot = wb
         End If
      End If
   End If
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set wb = Nothing
   Exit Function 'or Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   WorkbookOpenIfNot "

   Resume Proc_Exit
   Resume
End Function

Open in new window

here is a Sub to test it:
Sub testWorkbookOpenIfNot()
'160917 s4p
   Dim wb As Workbook
   Dim sPathFile As String
   
   '-------------------------------- pick ONE and comment the rest
   sPathFile = "JustFilename.xlsx"
   sPathFile = "C:\path\filename.xlsx"
   sPathFile = "bogus.xlsx"
   '------------------------------------
   
   Set wb = WorkbookOpenIfNot(sPathFile)
   If wb Is Nothing Then
      MsgBox sPathFile & " could not be opened"
   Else
      MsgBox wb.Name, , "Workbook name"
   End If
   Set wb = Nothing
   
End Sub

Open in new window

for information on error handling:

basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
0
Roy CoxGroup Finance ManagerCommented:
Hi Crystal.

Nice code, normally I would go with something like below, simply because the OP has the path already in the code.

Sub TestOpen()
If Not wbOpen("Suppliers.xls") Then
    Workbooks.Open "C:\Users\Desktop\Test.xlsm"
End If

End Sub

Open in new window


I'll have a closer look at your code later.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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:
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

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
poster didn't come back -- probably new to VBA
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.