Excel VBA file recognition

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

starl

Thread Starter
Joined
Dec 2, 2000
Messages
64
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
 
Joined
Oct 10, 2001
Messages
186
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

Thread Starter
Joined
Dec 2, 2000
Messages
64
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.
 
Joined
Oct 10, 2001
Messages
186
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

Thread Starter
Joined
Dec 2, 2000
Messages
64
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?
 
Joined
Oct 10, 2001
Messages
186
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

Thread Starter
Joined
Dec 2, 2000
Messages
64
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!
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top