raja.rkannan


Evening Ppl,

I am working on a Excel VBA assignment which involves accessing a directory which has around 2500 files and displaying the filename, date created & date modified based on a search criteria.

To be more clear, i have well formed file names in that folder and the last 4 characters in the file name are DDMM (date and month)

TEST0806.csv

TEST0803.csv

TEST0802.csv

TEST0801.csv

ANSWER0806.csv

ANSWER0803.csv

ANSWER0802.csv

ANSWER0801.csv

I would have files for two weeks in this folder. My program should fetch the files for today and yesterday from this folder, for the specified "TYPE", meaning TEST, ANSWER etc.,

I have tried using the FSO method, it works but it is very slow. I tried using DIR() method, even this works but it does not give me the date created or date modified values.

Can any of the experts in this forum suggest me a faster solution to this problem

Thanks

Raja




Re: VBA - Accessing files in a folder

Andy Pope


Hi,

Can you post the FSO code you have used
And how long is slow






Re: VBA - Accessing files in a folder

Icekool

Hi,

I currently do a daily extract from the Command Prompt using the following batch file: -

n:
dir /ogn/q/s >N:\1._Document_Version_Control_Log\extract.txt
@echo " your extract is complete"
Pause 100

I then run a lengthy macro to put the txt file into Excel and format the data how I want it.

I would like to be able to complete the Dir extract from inside Excel rather than a command prompt.

I will be interested to know the resolution.

Regards

Mark






Re: VBA - Accessing files in a folder

raja.rkannan

Hi Andy

Thanks for your interest. I have now optimized the code and it is indeed very fast. Initially, i had directly used the FSO object, browsed to the specific directory, get all the files (2000+) and then loop through them to get the desired file names. Now i have used the Office.FileSearch method to select the list of files and once selected, i use the FSO.GetFile method to collect the file attributes.

Cheers

Raja