?
Solved

Control Pop up window

Posted on 2016-09-17
7
Medium Priority
?
49 Views
Last Modified: 2016-10-07
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
0
Comment
Question by:Alok Kumar
  • 4
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
Roy Cox earned 1000 total points (awarded by participants)
ID: 41802693
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
 
LVL 24
ID: 41802696
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
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41802719
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 24

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points (awarded by participants)
ID: 41803133
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
 
LVL 22

Assisted Solution

by:Roy Cox
Roy Cox earned 1000 total points (awarded by participants)
ID: 41803786
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
 
LVL 24
ID: 41803817
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
 
LVL 24
ID: 41833336
poster didn't come back -- probably new to VBA
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question