FileName = Dir() - Invalid procedure call or argument

[dflak]

This error happens in the Read CSV Module.

There is something funky going on with this code.

In the code, I pass an argument to a function, GetDate. The argument is a file name with like "ABC_yyyymmdd.csv" and the function is designed to return the string MMM DD. As an intermediate step in the function, it changes the parameter passed to it to YYYYMMDD. It also changes the name of the file in the calling program. FileName is NOT a global. I "worked around" the issue with a two-step and created a variable called DateName and it gets destroyed by the parsing process.

I don't think I am doing anything to change where dir is pointing.

Attached is the file, the misbehaving macro is AddPages. The zip file contains test CSV files. Cells B1 and B2 on the control panel can be used to configure where these CSV files are read and where they get deposited to when they are processed. So you should be able to replicate the issue.

[Fluff13]

There is no AddPages macro

[dflak]

ModReadCSV

Please Login or Register  to view this content.

[Fluff13]

At the end of the loop make this change

Please Login or Register  to view this content.

You're changing the dir()when you move the files after processing

[Zer0Cool]

You cant use DIR for lopping files and modifying them. Use FSO for modifying them inside your DIR loop.

[Fluff13]

@dflak
Thanks for the Rep.

@Zer0Cool
The code is not modifying the csv files, only moving them.

[dflak]

I am "getting away" with this in other code and I decided to try to figure out why. In those cases, I have a program that reads a mail folder, gets the messaged and downloads the attachments to the folder I subsequently want to read for processing. These files are read into the directory in a specific order, and they are probably read out in the same order. In other words, by the time I hit the Filename = Dir() command, DIR is already "done with" the file and all the files it "sees" in front of it are still there.

[Zer0Cool]

Ok let me re-phrase my statement, you cant use DIR inside DIR to evaluate other things.

Please Login or Register  to view this content.

Cannot be inside the DIR loop. Use FSO to work with file objects inside the DIR loop

[Fluff13]

If you try the code you'll find that it works.

[dflak]

I think I see what is going on here.

This

Please Login or Register  to view this content.

Blows the "stack" of file names.

When followed by

Please Login or Register  to view this content.

a new "stack" is created.

Using fso.FileExists() - preserves the original stack.

[Zer0Cool]

@dflak You got it. Using DIR within the loop is essentially breaking the loop, hence using FSO to resolve the problem.