for x = file1 to file5000 ??? next file please.

Posted by Christopher on October 03, 2001 1:33 PM

I have a macro that imports a txt file, grabs two cells of data. A Date and Time. I have it then paste it into a new spreadsheet that I'm gathering all the dates and times of each file. Right now I have it use the application.openfile object and I have to select each file everytime I want to import. How do I have it do my macro to every file in the directory? I think I would use a "for" command, but how do I tell it to go to the next file?
Any help would be great.

Posted by Barrie Davidson on October 03, 2001 1:43 PM

Christopher, can you adapt my code at

http://ca.geocities.com/b_davidso/Web_Page_Files/Excel/extract_text_files.html

This was in response to the following question:
Q - I have an Excel Template file (report.xlt) and text files (1.txt, 2.txt, 3.txt etc - 45 of this text files) in the same directory. What I need to do is:

open my Report.xlt file
open 1.txt
copy and paste special (values only) some data from 1.txt to Report.xlt
save Report.xlt with the copied data as a 1.xls, 2.xls, 3.xls (etc) file
close 1.txt without saving it
Repeat step 2 - 5 as many times as many text files I will have (sometime only 5 and other times 105).
when I am done with this copying and saving I need to print ALL my 1.xls thru let say 45.xls files.
I would like to accomplish this by assigning a macro to a button on my tool bar. Currently I am doing this manually 1 file at the time. I would like to just activate my macro and walk away.

Hope this helps.
Regards,
BarrieBarrie Davidson

Posted by Christopher on October 03, 2001 1:51 PM

Thanks alot. From looking at it I think I can get it to work. I'll let you know. Thanks again.

Posted by Christopher on October 03, 2001 3:29 PM

Works like a champ!

Yup. Your code works great. I just had to make a couple of adjustments to have all the data end up in one excel file. Thanks a million!