search folder for file

Hi

Does DIR() work in 2007?

What sort of files are you testing? Excel?

rylo

Yes and they are excel files. Once i get this down, each file has different set of instructions.

Basically all files are opened and edited with vba code, saved, then imported to access database. After that happens, I open another file that is linked to access database to generate reports, then i copy and past pivot tables to email and send reports that way.

Right now its 5 a day and another 10 weekly.

Hi

Ok so you should be able to use DIR to cycle through the files, open any excel file, cycle through the sheets in the file and do your search(s).

If all the data is being copied into an access database, then why don't you just extract it from there into excel using SQL. Don't have to work your way through the files. Or do you have to different processing on the files to make them in a shape to put into Access?

If you can describe in more detail what you are doing, there may be alternative approaches that would simplify things...


rylo

Total of 5 files a day, 3 files have to edited before importing them into access.

Here is the complete process I hope to implement.

-Everyday from 7 to 12 in the morning i receive 5 files at least 15 mins apart.
When the file hits my inbox I have thunderbird(email client) to save it and open with default program.

So a change of plans i guess, i need this code to run on any file that is opened.(sorry just test this to see how it actually works.)

-Use a case statement to check the file name and execute code based on what the file name may contain. Its gonna be specific for example Report32145 would have to be in the file name for case 1.( this name will change everyday, but will always contain Report32145.(so i guess search for *Report32145*)

-After the case statement is used the specified macro is execute on the file, editing it to the requirement format.

-Save file to specified spot and with specified name.(all this is specified in the vba code to run based on case statement.

-Close file

-Then i would use a command after the close statement to open access database and run a macro that imports the file that was just saved.

-The database would close itself and open another file that refreshes its report.

-That file use vba to copy and paste date into email and send email.



I know this will be long process and take some time to setup, but it will be worth it.

Hi

Start with the access part. Create the file import procedure, then create an autoexec macro that will call this sub so that when the database opens, it will automatically import the data.

See how that goes.

rylo

That part I got, but I need to start at the beginning with running the case when the file opens to execute the desired code.


And btw, thanks for the help and time.

Hi

Rather than opening the file that has just been saved, can thunderbird open a specific excel workbook?

rylo

As of right now, no. I'm searching alternative email clients to see if I can do this.

Hi

Good luck. If you can find a way to open a particular excel workbook, then things should be reasonably easy from there.


rylo

I'm working on doing this using the plugin for thunderbird, but in the mean time, i'm going to move forward.

First this will be how to search folder for files containing keywords.

Since I'm using 2007 i can't use filesearch, so how would I do this.

Thanks

Hi

Have you tried dir() in 2007? If it doesn't work, then maybe you will have to post in the 2007 forum to see if someone there knows how to do the file search.

rylo

Not sure what FileCountA handles. I would suggest that you add code to handle the case where Max=0.

The dictionary object can be handy for such things. e.g.

Please Login or Register  to view this content.