Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel file paths | Excel Discussion (Misc queries) | |||
How to check if external .txt file has text in it... | Excel Programming | |||
file paths | Excel Discussion (Misc queries) | |||
Printing Excel Formulas without file paths updating | Excel Discussion (Misc queries) | |||
Relative paths to external data? | Excel Discussion (Misc queries) |