VBA to check file exists

[tonywatsonhelp]

Hi Everyone,

I have a macro that opens a file and pulls in data,
I would like to add a bit of code that checks that file exists, if not exit sub

please help if you can

Thanks
Tony
Here my code:

Sub Open_Dell()


 With Workbooks.Open(ThisWorkbook.Path & "\Dell Notebooks.xlsx", UpdateLinks:=0)
 
            With .Sheets("Layout")
               .Range("A2:D" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            
           Lr1 = ThisWorkbook.Sheets("Price Lists").Cells(Rows.Count, "A").End(xlUp).Row + 2
           ThisWorkbook.Sheets("Price Lists").Range("A" & Lr1).PasteSpecial xlPasteValues
           
           Application.CutCopyMode = False
            
           End With
           .Close False
End With
End Sub

 

[Fluff]

How about

Sub Open_Dell()
Dim Fname As String
   

Fname = Dir(ThisWorkbook.path & "\Dell Notebooks.xlsx")
If Fname = "" Then Exit Sub
With Workbooks.Open(Fname, UpdateLinks:=0)
 
            With .Sheets("Layout")
               .Range("A2:D" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            
           Lr1 = ThisWorkbook.Sheets("Price Lists").Cells(Rows.Count, "A").End(xlUp).Row + 2
           ThisWorkbook.Sheets("Price Lists").Range("A" & Lr1).PasteSpecial xlPasteValues
           
           Application.CutCopyMode = False
            
           End With
           .Close False
End With
End Sub

 

[tonywatsonhelp]

This is perfect, thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[uk747]

Can this be made quicker or better by checking if open and then getting data. Data > Get Data from File Workbook

Not sure of quickest way to get data from closed book

 

[Fluff]

As this is a totally different from the op, you need to start a thread of your own. Thanks

 

[uk747]

ok will do

PS I got an error with the line below. The file is there because it passed If Fname = "" Then Exit Sub

With Workbooks.Open(Fname, UpdateLinks:=0)

Says Run time error 1004 Sorry we couldnt find test.xlsx. Is it possible it was moved renamed or deleted