Add Msgbox to code if workbook not found

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
320
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,791
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
320
Office Version
  1. 2010
Platform
  1. Windows
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
74,791
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
320
Office Version
  1. 2010
Platform
  1. Windows

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
74,791
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
320
Office Version
  1. 2010
Platform
  1. Windows

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
74,791
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
 
Solution

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
320
Office Version
  1. 2010
Platform
  1. Windows
Works like a dream. Thank you so much for this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,791
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,438
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top