Check If Files Exist and Return

Hi,
I have been searching here and on the www. for the this but no succes.

I would like to check/search if (FileName As String) is still exists in its directory.
--Why as string: because I have a hidden kollom where the links to the files are in txt format.--

I can't write VBA But maybe I can sketch it?

NOT A CODE

Please Login or Register  to view this content.

(MOD: tags even for psuedo-code please... yes, we're that pedantic!)

Could you perhaps use something like:

Please Login or Register  to view this content.

the above, stored in a Module (in a Macro Enabled file), could be called from a cell, eg:

Please Login or Register  to view this content.

where A1 holds File Name as string (assume full path)

If you want to pass the string directly

Please Login or Register  to view this content.

called from the cell using:

Please Login or Register  to view this content.

I'm not sure what your goal is - but if you want to return the filename where it exists else something else then you could adapt the above along the lines of:

Please Login or Register  to view this content.

so the above would return the file name if it exists else it will return an alternative value - the alternative value would be "No" if unspecified in the cell call else it would use the specific alternative specified, eg:

Please Login or Register  to view this content.

Of course if the drive specified is invalid (eg AA) it will return an error - you could account for that also if nec., I'm not operating on a network environment so I've not tested for full blown server paths.

Almost there.

Just one more problem When I add a file to a directory which is to be checked by your code the result isn't updated by F9 nor bye saving/closing/reopening.

Could you make it possible for these code to refresh with F9?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I had the same problem before and it got solved this way...

Originally Posted by vikas.bhandari

Oh!!!!! I didnt know it

if you add the following line to your code :

Please Login or Register  to view this content.

It will update the values as you want it to. Please use my previous workbook sample and include the above line in the LastSavedTime function. It should be done now.

Thanks,
Vikas

Originally Posted by Bluewhistler

Than you very much.

Please Login or Register  to view this content.

Does the trick!

I did as you told me and this works lika a charm! now when I press F9 the dates are refreshed!

Here's the Result & Solution:

Please Login or Register  to view this content.

Thx again Vikas

The cell is just 26pxl wide so The Yes and No if fine.

What I'm trying to get is 2 collums, one is for Autcad files and the other for E-plan.
The reason is that there are electrical drawings in both format and just one excel list of both the drawings. these are used by several ppl and the drawings are managad and edited by me only. So the ppl who use the Excel list can see if there is a drawing and if its drawn in acad or E-plan or both.

Originally Posted by Bluewhistlter

Just one more problem When I add a file to a directory which is to be checked by your code the result isn't updated by F9 nor bye saving/closing/reopening.

Could you make it possible for these code to refresh with F9?

If you add a file to a directory and subsequently recalculate XL via CTR+ALT+F9 the function should update but yes you can add a Volatile setting to the UDF such that it recalcs via F9

Please Login or Register  to view this content.

However...

I'd make the point that adding a file to a folder will not invoke any reaction in XL so the UDF is always susceptible to error in so far as it requires a Volatile action to take place within XL itself before it will update.

Originally Posted by Bluewhistler

do you sudgest using Ctrl-Alt-F9 instead of Volatile action?

No, I was just trying to make the point that even with Volatile applied the process of adding / removing a file from the directory would not trigger a recalculation of the UDF - a Volatile action would be required in XL to invoke the Calculation.

So in short, though using Volatile in this scenario makes sense it does not resolve all issues you face.

Originally Posted by Bluewhistle

seems to work both ways with this:

=FileExists("C:\test.txt")
and with this

=FileExists("A1")

Presumably you meant A1 rather than "A1" ?

Yes, given the variable strFN is a string either will work, ie the value within A1 is passed to the UDF as the string (or "A1" if that's what you used which would return "No").

Generally speaking if you were using the 2nd version exclusively (ie always using a range to determine the value) you would use a Range variable in the UDF given you can do more with that variable from within the UDF than you can with just it's value.

Either test the content prior to calling the UDF (advised), ie:

Please Login or Register  to view this content.

or invoke the UDF and test content

Please Login or Register  to view this content.

Just a thought I had regarding the file listing updating. Since you're using VBA anyway, why not create a macro and put it on a RELOAD button. The macro saves and reopens the current workbook, thus all values update in the entire workbook.

Something like:

Please Login or Register  to view this content.

Or does the Volatile make all this unnecessary?

Given you state all the links are to text files could you not simply take strFN and modify ?

Please Login or Register  to view this content.

or am I missing something.

Regards the other code, I'm not sure I see the value of a button related event over a key press of F9 - either way a manual intervention of some sort is required though granted the button may be deemed more intuitive to an end user.

I don't understand the reasoning behind the removal of the Volatile statement so can't comment.

I based the prior suggestion on your initial post:

Originally Posted by BlueWhister

I would like to check/search if (FileName As String) is still exists in its directory.
--Why as string: because I have a hidden kollom where the links to the files are in txt format.--

If as it seems the file type is not always text (eg pdf etc) and the intention is to return the filename less extension rather than "Yes" then if you assume the extension is always .??? (ie 4 chars) you can use:

Please Login or Register  to view this content.

If you can't make that assumption then you could use:

Please Login or Register  to view this content.

Other than that I confess I'm lost.

Using your example... assuming:

a) A1 relates to A2/B2 which are in turn related to A3/B3 respectively via the UDF (returning either Yes or Null)

b) A3 always contains the .txt file path and B3 the .pdf path

Then

Please Login or Register  to view this content.