I am just a rookie when it comes to VBA. Can anyone tell me how I can write a macro for the following function:
1) Open text file from a specified list of file names stored in an excel sheet. i.e if filename reads 123 on the excel sheet, macro will open this file with .txt extension.
2) The following is what is stored in text file
11,008,20020819,02216,001,
02718, 00002
There is well over 5000 of these lines with different numbers. The 6th group of digits, i.e 02718 needs to be read and copied into a cell in another Excel worksheet.
3) The macro will switch back and forth between text file and excel file until 500 lines of the text file is read or EOF.
4) At this point the macro will automatically save the excel file name as a number determined by a counter. So after every 500 lines read and copied, the macro will save the excel file.
5) The macro will then continue with the next 500 lines of the text file or unitil EOF.Steps 3) and 4) are repeated until all the 6th group of digits are read from the text file and copied into the excel sheet.
6) At this point, macro will close the text file and open the next text file that is in the list on excel sheet. Steps 3) and 4) are repeated again.
7) Macro will keep performing all the above steps until the list in excel sheet is completed.
I appreciate all the help I can get with this problem. Thanks for everyone's time.
Start Free Trial
