Solved

Control Pop up window

Posted on 2016-09-17
7
40 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 18

Accepted Solution

by:
Roy_Cox earned 250 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 19
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 18

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 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 18

Assisted Solution

by:Roy_Cox
Roy_Cox earned 250 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 19
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 19
ID: 41833336
poster didn't come back -- probably new to VBA
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 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