largeselection
Active Member
- Joined
- Aug 4, 2008
- Messages
- 358
Hi,
I have some files that are fairly tedious to process. Basically all I have to do is open a report (it's in .txt delimitted format), filter a column for a value, copy the results of the filter and paste them into a sheet in a different workbook. Then I repeat for each report in the folder.
What I'd like to do is automate it a bit. I have the code done for the filter and copy/paste. What I need some advice with is how I can get it to loop through all of the files in the folder I specify.
At this point, I have code where the user clicks a button and a file search dialog pops up, they navigate to the folder where they have saved reports and that path is saved on the first page of the workbook. So I have the path established.
I also have the code written which will filter/copy the information from the report and paste it in the appropriate tab.
I figured the easiest way to do this would be to use some sort of loop. Something like:
-User clicks button
-User navigates to the folder containing the .txt delimitted reports to process
*-Code counts how many files exist in the folder specified
*-Opens the first file and names it "MyFile"
-Do Code to filter/copy information from MyFile to ThisWorkbook
*-Closes MyFile
*-Opens the next file and names it "MyFile" again
*-Repeat, repeat repeat until all files have been processed
1) Does this seem like a reasonable approach?
2) How can I do something like this?
The points with "*" in front of them are the ones I'm having difficulty with. Though the Closes MyFile part I can probably figure out and the "Opens the next file and names it "MyFile" again" would be accomplished with a loop as it does the next file in the folder.
So I think the only part that's holding me up is if I have the path where the folder is, how do I translate that into "count the number of files in there, then for each file in there..., then next file until there are none left"
Thanks!
I have some files that are fairly tedious to process. Basically all I have to do is open a report (it's in .txt delimitted format), filter a column for a value, copy the results of the filter and paste them into a sheet in a different workbook. Then I repeat for each report in the folder.
What I'd like to do is automate it a bit. I have the code done for the filter and copy/paste. What I need some advice with is how I can get it to loop through all of the files in the folder I specify.
At this point, I have code where the user clicks a button and a file search dialog pops up, they navigate to the folder where they have saved reports and that path is saved on the first page of the workbook. So I have the path established.
I also have the code written which will filter/copy the information from the report and paste it in the appropriate tab.
I figured the easiest way to do this would be to use some sort of loop. Something like:
-User clicks button
-User navigates to the folder containing the .txt delimitted reports to process
*-Code counts how many files exist in the folder specified
*-Opens the first file and names it "MyFile"
-Do Code to filter/copy information from MyFile to ThisWorkbook
*-Closes MyFile
*-Opens the next file and names it "MyFile" again
*-Repeat, repeat repeat until all files have been processed
1) Does this seem like a reasonable approach?
2) How can I do something like this?
The points with "*" in front of them are the ones I'm having difficulty with. Though the Closes MyFile part I can probably figure out and the "Opens the next file and names it "MyFile" again" would be accomplished with a loop as it does the next file in the folder.
So I think the only part that's holding me up is if I have the path where the folder is, how do I translate that into "count the number of files in there, then for each file in there..., then next file until there are none left"
Thanks!