Excel VBA file recognition

[starl]

Working on a macro in excel. Have a list of files on a sheet, program goes through and opens those files (it does things with those files, but that is irrelevant).
My problem is that not all files may exist. So, before the entire program runs, I want a form to pop up listing the files that couldn't be found. I'm thinking that it runs through the list and for those it finds, it marks the next cell over - when it's done, it looks and whatever files aren't marked, they get listed in the form.
My solution is to go several tedious loops, but I'm hoping someone has a better idea.

thanks

 

[TimCottee]

Starl, you could always add a module and use the following function code to achieve this:

Public Function ValidFile(FileName As String) As Boolean
ValidFile = Len(UCase(Dir(FileName))) > 0
End Function

Then in your column use =ValidFile(B1) where B1 is the previous column to get a true or false response.

 

[starl]

hey tim! nice to see you here.

no, the function doesn't do what I want... tho it is pretty cool. (or maybe I'm not using it correctly...
D1-D10 contain filenames, F1-F10 is my empty column - I want something in the F column to tell me if the filename listed in the D column actually does exist in a specific directory.

 

[TimCottee]

DO d1-D10 have the full path name in them or is this in another column/cell?

The function I gave you should work if you enter =ValidFile(D1) in F1 and so on to =ValidFile(D10) in F10 anyway.

If you want a macro to achieve the same thing that is not too difficult, you just use the ValidFile = Len(Dir(Filename)) > 0 line but substitute the value for D1-D10 in a loop as you go through it.

I can knock this up if you want it done that way.

 

[starl]

no, only the filename is in the cell. I tried using my globals w/FileName, but it gives me FALSE - but I know the files are there...
ha - I added the fullpath to the cell and it does work... but how can I make it work w/o having to give the fullpath?

 

[TimCottee]

Where are the globals defined? In the worksheet or in the module? You may have to reference them as ThisWorkbook.GlobalPathNameVariable or something like that otherwise the scope will probably be wrong.

 

[starl]

you got it! I had the globals in my other module - so I cut/paste the function over and TADA! it worked. woohoo!!!! thanks - you saved me hours of work... and more loops.

I never thought of creating functions to do some of the work... and that was a interesting way of using LEN.. makes sense tho. ugh - I've got so many loops in my code...

thanks again!

 

[TimCottee]

Glad to be of help starl you know how to get hold of me if you need any more.