Make excel check external file paths

[Stuart]

I have a spreadsheet which contains thousands of file paths going doing
column A. I wanted excel to be able to look at cell A1, A2 and so on
and check if the file does exist on the harddrive and then return in
cell B1 return Yes or No.

I wanted this to loop until it reaches the end of the file paths and
has returned yes or no for all of them.

Thanks in advance.

Stuart

 

[Guest]

Use this UDF! Insert it in a normal module!
Public Function FileExists(fname) As Boolean
FileExists = IIf(Dir(fname) <> "", True, False)
End Function

Insert in B1: =FileExists(A1) and drag it down as required!

Regards,
Stefi


„Stuart†ezt írta:

 

[Stuart]

Hi Stefi,

How do I insert the code into a normal module?

I am not getting anywhere with this code......been trying a similiar
code for days,

Hope you can help,

Many thanks

Stuart

 

[Robert Bruce]

Use this UDF! Insert it in a normal module!
Public Function FileExists(fname) As Boolean
FileExists = IIf(Dir(fname) <> "", True, False)
End Function

Insert in B1: =FileExists(A1) and drag it down as required!

Or , more simply:

Public Function FileExists1(fname) As Boolean
FileExists1 = Len(Dir(fname)) > 0
End Function

Rob

 

[Stuart]

Where should this text be pasted into?

Thanks

Stuart

 

[NickHK]

Stuart,
Don't know if it applicable to you, but you can also call windows API
directly from the worksheet. This has the advantage of dealing only in
Unicode. For me this is good because my English XL/Window is on a network
where the other computers run Chinese Windows 2K and NT4. Dir() and
GetAttr() cannot handle this.

<Module code>
Declare Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesW"
(ByVal lpFileName As String) As Long
</Module code>

Then the cell formula is just:
==IF(GetFileAttributes(A15)>0,TRUE,FALSE)

NickHK

 

[Robert Bruce]

Where should this text be pasted into?

Thanks

Stuart

Put it in a regualr module in the workbook your filenames are in. Then just
enter it as a worksheetfunction in, say, B1 and copy down as far as your
filenames go in column A.

Rob

 

[Stuart]

Hi Rob,

hmmmm - seems none of the file paths are coming back as true when I
know they exist. I'm getting a message #NAME?.

Can you help?

Thanks

Stuart

 

[Stuart]

Is there a better way to have excel check external file paths exist?

The methods given are returning #NAME? each time I enter a correct file
path in cell A1,

Please respond as soon as you can,

Kind Regards,

Stuart

 

[Guest]

Hi Stuart,

Could you find out how to create a normal module and insert a function into
it?
If yes, and the function still doesn't work, make a try with this other one:

Function IsFile(filepath)
Set fs = CreateObject("Scripting.FileSystemObject")
IsFile = fs.FileExists(filepath)
End Function

Regards,
Stefi


„Stuart†ezt írta:

 

[NickHK]

Stuart,
That error means that Excel cannot find the function.
Either way, it must be in a standard module, NOT on a worksheet or
ThisWorkbook module or a class module.
And the function must be marked Public.
e.g. Public Function FileExists(....

NickHK

 

[Stuart]

Hi there,

Since my previous tests failed - the file paths werent entered into the

spreadsheet correctly!! I wondered if it were possible to have excel
extract a list of all the files in a drive and put each file found's
full path location in a row on the spreadsheet.


for example - I have my C Drive. I would expect excel to find all
Windows files and then list their full path down column A,


Hope this explains what I'm looking for,


Thanks in advance,


Kind Regards,


Stuart

 

[Guest]

http://support.microsoft.com/kb/185476/EN-US/
How To Search Directories to Find or List Files

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

http://support.microsoft.com/kb/186118/EN-US/
How To Use FileSystemObject with Visual Basic