Simplify macro

I need help simplifying this macro:

I run a report for every hour of the day.
A text file is saved into a directory as the following (0100 = 1:00 AM, 0200 = 2:00 AM, etc):

sv_toptrans 0100 FI.txt, sv_toptrans 0200 FI.txt, sv_toptrans 0300 FI.txt... sv_toptrans 2200 FI.txt, sv_toptrans 2300 FI.txt, sv_toptrans 2400 FI.txt (1 file for each hour - 24 files total)

I need the macro to look in the directory. Then copy the data to 'sheet1'. Then format the data. Finally move the data to to differernt worksheets.

This macro works as long as a have a text file for each hour.

If there is not a text file for a particular hour, I need the macro to skip it and move to the next hour. For example, if there is a sv_toptrans 0100 FI.txt file, it should copy the text file to the workbook, format it and then move the data to the different worksheets and then go to the sv_toptrans 0200 FI.txt file. If there is not a sv_toptrans 0200 FI.txt file, then it should move onto the sv_toptrans 0300 FI.txt file. If there is a sv_toptrans 0300 FI.txt file, it should format it and then move the data to the different worksheets and then go to the sv_toptrans 0400 FI.txt file.

================================================================

Please Login or Register  to view this content.

*** I repeat this for all 24 hours ***

Try this:

Please Login or Register  to view this content.

hi everyone,

I haven't tried JB's code but I suggest one small change to it, because I think you may end up with a lot of redundant ("duplicate") connections in your workbook if the code is repeatedly run. Of course, I could be wrong...

Please Login or Register  to view this content.

iamasimpleman,
If you're happy with JB's solution, can you please mark the thread as Solved?
(see Rules for details)

Rob