Add Msgbox to code if workbook not found

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
300
I have this code which works great, in that it tests to see if a specific workbook is open and if not it opens the workbook.
However, I would like to create my own msgbox if the file is not found, but when I add an "else" to the if statement it doesn't find the file!

This is the working code I would like to add a message to:

TargetWb = "My Excel Workbook.xlsx"

For Each Workbook In Workbooks
If Workbook.FullName = TargetWb Then Workbook.Close (False)
Next Workbook
Workbooks.Open(TargetWb).Activate

...the code I have amended below doesn't work, could someone tell me why?

TargetWb = "My Excel Workbook.xlsx"

For Each Workbook In Workbooks
If Workbook.FullName = TargetWb Then
Workbook.Close (False)
else
Msgbox "file not found"
end
Next Workbook
Workbooks.Open(TargetWb).Activate
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Does TargetWb contain the file path & filename of the workbook, or just the filename?
 

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
300
When I run it - it only shows the filename in the variable. If the file resides in the same folder it works fine using just the file name.
I prefer not to use a path because someone else will be running it on a different computer and they might have a different folder name.

Unless I get them to select the file first, which could be an alternative.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about this
Code:
Sub BradleyS()
   Dim Wbk As Workbook
   Dim TargetWb As String
   Dim Flg As Boolean
   
   TargetWb = "My Excel Workbook.xlsx"
   
   For Each Wbk In Workbooks
      If Wbk.Name = TargetWb Then
         Wbk.Close (False)
      Else
         Flg = True
      End If
   Next Wbk
   If Flg Then MsgBox "File not found"
   Workbooks.Open TargetWb
End Sub
Although I don't see the point in closing the workbook, only to re-open it, so maybe
Code:
Sub BradleyS()
   Dim Wbk As Workbook
   Dim TargetWb As String
   Dim Flg As Boolean
   
   TargetWb = "My Excel Workbook.xlsx"
   
   For Each Wbk In Workbooks
      If Wbk.Name = TargetWb Then
         Flg = True
         Exit For
      End If
   Next Wbk
   If Not Flg Then Workbooks.Open TargetWb
End Sub
 

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
300

ADVERTISEMENT

I'd be happy with the 2nd code but it doesn't provide a msgbox I can customise if not found, and the first code shows the msgbox and then goes to the next line and shows the default Microsoft msg e.g. Workbooks.Open TargetWb line which I'm trying to avoid being shown.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Please explain what you are trying to do.
Do you need to close the workbook if it's open, do you want to open it if it's not already open, and what do you mean by "the default Microdoft msg"
 

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
300

ADVERTISEMENT

Sorry to be a pain and thank you for your help so far.

I have a workbook open which this code is in, and I want to get some data from another workbook with a specific name that should reside in the same folder that this workbook is in.
So I'm checking to see if it is already open and if so I will do nothing and continue with the data capture, if not I want to open it (if in same folder) and if not found I want to show a message to say I can't find the specific named file in the folder this workbook is in
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Ok, in that case try
Code:
Sub BradleyS()
   Dim Wbk As Workbook
   Dim TargetWb As String
   Dim Flg As Boolean
   
   TargetWb = "+book1.xlsm"
   
   For Each Wbk In Workbooks
      If Wbk.Name = TargetWb Then
         Flg = True
         Exit For
      End If
   Next Wbk
   On Error Resume Next
   If Not Flg Then Set Wbk = Workbooks.Open(TargetWb)
   On Error GoTo 0
   If Wbk Is Nothing Then MsgBox "File not found"
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,416
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top