Add Msgbox to code if workbook not found

[BradleyS]

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

 

[Fluff]

Does TargetWb contain the file path & filename of the workbook, or just the filename?

 

[BradleyS]

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]

How about this

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

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]

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]

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]

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]

Ok, in that case try

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

 

[BradleyS]

Works like a dream. Thank you so much for this.

 

[Fluff]

You're welcome & thanks for the feedback