Make excel check external file paths
 

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

Make excel check external file paths
 

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:

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

Make excel check external file paths
 

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

Stefi wrote:
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:

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

Make excel check external file paths
 

Roedd <<Stefi wedi ysgrifennu:

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

Make excel check external file paths
 

Where should this text be pasted into?

Thanks

Stuart


Robert Bruce wrote:
Roedd <<Stefi wedi ysgrifennu:

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

Make excel check external file paths
 

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

"Stuart" wrote in message
oups.com...
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

Make excel check external file paths
 

Roedd <<Stuart wedi ysgrifennu:

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

Make excel check external file paths
 

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




Robert Bruce wrote:
Roedd <<Stuart wedi ysgrifennu:

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

Make excel check external file paths
 

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


NickHK wrote:
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

"Stuart" wrote in message
oups.com...
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

Make excel check external file paths
 

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:

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




Robert Bruce wrote:
Roedd <<Stuart wedi ysgrifennu:

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

Make excel check external file paths
 

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" wrote in message
ps.com...
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


NickHK wrote:
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

"Stuart" wrote in message
oups.com...
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

Make excel check external file paths
 

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



Stuart wrote:
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


NickHK wrote:
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

"Stuart" wrote in message
oups.com...
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

Make excel check external file paths
 

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

--
Regards,
Tom Ogilvy


"Stuart" wrote:

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



Stuart wrote:
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


NickHK wrote:
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

"Stuart" wrote in message
oups.com...
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