Excel 2010 timing issues using Dir

**brifoz** · 03-05-2012, 08:06 AM

Hi, I have been experiencing timing issues using vba to process 6 CSV files in a folder. Basically, Excel misses files which are definitely in the folder.

I have been able to get more files picked up by inserting Application.Wait statements, but even then the results are not always consistent. I have also had the same problem with using the Kill command - some files get deleted and some don't. Innserting a Wait seems to sove the problem, but I don't think this is other than a clumsy 'patch'.

I have tried inserting DoEvents, but that makes no difference at all.

Code snippet:

Please Login or Register  to view this content.

Sometimes it gets all 6 files, sometimes it just gets two or three. Really infuriating! Is this an Excel 2010 bug?

**Firefly2012** · 03-05-2012, 08:12 AM

I can't say I've noticed any problems around using Dir. Are you sure the csv files all exist at the point where you start running the script? |They aren't being generated on the fly as it were?

**Andrew-R** · 03-05-2012, 08:12 AM

Does it make a difference if you don't call your variable 'FileName', which is a reserved word?

**brifoz** · 03-05-2012, 11:46 AM

Thank you, Dave for the information about tags. I only joined today, so please take that into account!

As regards FileName being a reserved word, a good suggestion, but changing it to MyFileName makes no difference. When I ran the program just now, it picked up just two files out of 6.

Before this code, the program runs ftp.exe via a shell command and I found I had to insert a Wait of 2 seconds after that for this code to pick up the folder. So that might have something to do with it.

Interestingly, inserting MsgBox commands to view each filename as it is found resolves the problem. I think this allows whatever process is hanging around to finish. But this program has to run with no intervention!

I haven't got an older version of Excel any more, but this program seemed to work OK before I upgraded to 2010.

Maybe something using DoEvents might help, but inserting it in the loop shows no difference.

**brifoz** · 03-05-2012, 11:49 AM

FireFly2012 - I think you have something there. Because some of these files are being downloaded via ftp, so maybe sometimes they haven't finished downloading. I need a way of checking when the downloads have finished.