Check if wb exists

The Dir method will return a file name if the file exists and a blank if not, we can use this to create a small function to get a boolean response. Something like the below:

Sub test()
    Dim ans As Boolean, FileName As String
    
    FileName = "C:\Users\jbloggs\Desktop\test123.xlsx"
    
    ans = CheckFile(FileName)
    
    If ans = False Then
        MsgBox "File name: " & FileName & " does not exist."
        Exit Sub
    Else
        MsgBox "File name: " & FileName & " exists."
    End If
End Sub



Function CheckFile(FilePath As String) As Boolean
    ans = Dir(FilePath)
    If ans <> vbNullString Then
        CheckFile = True
    Else
        CheckFile = False
    End If
End Function

Hi CheeseSandwich!

I think I'm doing something wrong. If I modify my code with your solution the result is always the same message: "File name: xxxx does not exist."
Where should I insert your code to get the proper result?
Can you please explain?
Thank you.

Is there a space before your file name as the below code will create a file location like:
"H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\ FileName.xlsx"
notice the space before the FileName part - is this intentional?

Should it not be:
"H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\FileName.xlsx"

Workbooks.Open("H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\ " & Filename & ".xlsx")

This could be why we are drawing a blank as it can't find the file with the above file path.

Yes, it was a mistake, but in this case did not matter because I did the same typo when the sheet was saved. So the file name had that space too.

After trying your combined code it works.
I modified the first filename like you did (and refer that after the .open method too), maybe that was the problem.

Your solution works fine, thank you.

If you wanted to incorporate the check then it would look something like the below (untested)

Sub Recall()

    Dim ws As Worksheet
    Dim wb As Workbook
    Dim FileName As String
    Dim ans As Boolean, flName As String
    
    Set wb = ThisWorkbook
    Set ws = ActiveSheet
    
    FileName = ws.Range("C2") & " " & ws.Range("N2")
    
    Worksheets("Ures lap").Range("C2").ClearContents
    Worksheets("Ures lap").Range("N2").ClearContents
    
    flName = "H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\" & FileName & ".xlsx"
    ans = CheckFile(flName)
    If ans = False Then
        MsgBox "File name: " & flName & " does not exist."
        Exit Sub
    End If
    
    Set closedBook = Workbooks.Open(flName)
    closedBook.Sheets(FileName).Copy Before:=ThisWorkbook.Sheets("Meretek")
    closedBook.Close SaveChanges:=False
    
    Kill (flName)
    
    wb.Activate
    
    ActiveSheet.Buttons.Add(650, 18, 110, 20).Select
    Selection.Name = "Gomb 1"
    Selection.OnAction = "SaveSheet"
    ActiveSheet.Shapes("Gomb 1").Select
    Selection.Characters.Text = "Munkalap mentese"
    
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    
End Sub

Function CheckFile(FilePath As String) As Boolean
    ans = Dir(FilePath)
    If ans <> vbNullString Then
        CheckFile = True
    Else
        CheckFile = False
    End If
End Function

A VBA demonstration for starters :

PHP Code: 

Sub Demo1()
  Const E = ".xlsx", P = "H:\CD\01 Gyartaskozi ellenorzes\Lezart gyartasok\"
    Dim F$
        F = Workbooks("Moulding check.xlsm").ActiveSheet.[C2&" "&N2]
        If Dir(P & F & E) = "" Then MsgBox "File not found", 48: Exit Sub
    With Workbooks.Open(P & F & E, 0)
        .Sheets(F).Copy ThisWorkbook.Sheets("Meretek")
        .Close False
    End With
        Kill P & F & E
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄