[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Control Pop up window

Posted on 2016-09-17
7
Medium Priority
?
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 22
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

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 22
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 22
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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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