How to find if a file exists within VBA

[montypython1]

Greetings,

I have an Excel macro that calls certain files in order to extract data. Since I will be distributing this to different departments, there is a good chance that from time to time, some of these files will not exist (since these data files are created through input by people within that department).

How can I test to see if the file exists, before trying to retrieve it?

Thanks,
Dave

 

[macropod]

If Dir 'Filepath & name' <> "" Then
'Exists
End if

Cheers
Paul Edstein
[MS MVP - Word]

 

[montypython1]

Hi Paul,

Thanks for the suggestion, but it doesn't seem to work as expected, but maybe I'm missing something. Here is my code:

If lcFolder & "\ED01_FI_Used03_m0.CSV" <> "" Then
Sheets("01Used_m0").Select
Range("A22:BC28").Select
Selection.ClearContents
Workbooks.Open Filename:= _
lcFolder & "\ED01_FI_Used03_m0.CSV"
'error occurs on line above
Range("A1:BC2").Select
Selection.Copy
'then do stuff with the data
End If

Even though the file does NOT exist, it still tries to run the code inside the IF/ENDIF statement. Note: the variable "lnFolder" refers to "C:\users\public\documents" and it DOES exist, but the file DOES NOT exist (I deleted it for testing purposes).

Any ideas why it would still run the code when the IF clause is NOT met?
Also, How do you post code in Tek-Tips so it shows the indents?

Thanks,
Dave

 

[macropod]

Hi Dave,

That's hardly surprising - you have no Dir test! Try:

If Dir(lcFolder & "\ED01_FI_Used03_m0.CSV") <> "" Then
  Sheets("01Used_m0").Range("A22:BC28").ClearContents
  Workbooks.Open Filename:=lcFolder & "\ED01_FI_Used03_m0.CSV"
  'error occurs on line above
  Range("A1:BC2").Copy
  'then do stuff with the data
End If

Cheers
Paul Edstein
[MS MVP - Word]

 

[montypython1]

Ahhhhhh ..... You are absolutely correct.
I overlooked the DIR keyword.

I just tried your suggestion and it works perfectly.

Thanks,
Dave

 

[Andrzejek]

And as far as "How do you post code in Tek-Tips so it shows the indents?", if you type:
[blue]
[ignore]

[/ignore][/blue]
Some code goes here
    other code
Last line[blue]
[ignore]

[/ignore][/blue]

You get:

Some code goes here
    other code
Last line

Have fun.

---- Andy

 

[montypython1]

Hi Andy,

Thanks ... That is exactly what I needed.
I figured it was something easy ... I just couldn't find it.

Dave