Modifying Pearson's Code to "Treat consecutive delimiters as one"+remove columns

I would like to create a macro that imports 8 separate text files into two excel sheets in the same workbook (4 in each, see below for details). Pearson's code is almost exactly what I need, however I need to treat consecutive delimiters (space) as one.

Up until the multiple file import issue explained briefly above, a previous post is exactly what I needed: http://www.excelforum.com/excel-prog...rs-as-one.html

Pearson himself replied with a solution, however it didn't seem to work for me. I fully expect that I'm missing something very obvious here. I put his solution directly under the line he specified (as well as trying to put it directly after on the same line).

However, when I do this I get "Run-time error '1004': Unable to get the Trim property of the WorksheetFunction class".

So solving this would be my first hurdle.

My second would be the part about the multiple file import.

As I stated above, I need to take two sets of four different text files (so four on each sheet), import only columns 2-5 from each (though each one has 11) and put them side-by-side (with one column space between each) in the same worksheet, starting at row 6. So the appropriate data from file 1 would be stored in columns B-E, file 2 in G-J, etc. The second worksheet will function exactly as the first in the exact same format.

Please excuse me if I could have more clearly re-worded the following. I find it confusing even to myself...

I have a specific file path for the aforementioned files, and as this task must be repeated in the future I would like to add to Pearson's script another user-input that asks to specify two sets of two-letter folder names (e.g., TR and GB) for which to find the appropriate four files, which will only differ by these same two letters at the beginning of their file names (four with TR and four GB).

Example: "TRfilename.txt" and "GBfilename.txt" would be in files "TR" and "GB", respectively.

As if I wasn't asking enough, after this macro is finished importing each individual file, I would like to sort by the second column of the remaining data (column 3, as it were) by increasing values. After the sort is complete, I also need to search the data for the first row that contains a specific word in said second column and delete this row and every one below it.

Sounds like a real challenge to me, but I wouldn't be surprised if I saw a solution from one of you experts, so thanks in advance for the help.

The longest string in the file is just over 100 characters long.

Please Login or Register  to view this content.

I can't really say. I'm not that experienced in VB.

Perhaps you can dumb down your question a bit for me?

I still get the "Run-time error '1004': Unable to get the Trim property of the WorksheetFunction class" error.

I'm not able to post the data as it's proprietary information.

This is the longest line in the file (which is repeated at standard intervals):

" XXXXXX xxxxxxx xxxxxxxxxx xxxxxxxx xxx Tue Apr 13 16:06:33 2010 "

This line has no bearing or indicator of column widths, markers, etc. It's really just a program & time stamp, but as you can see there are a lot of spaces.

There also seems to be a null-code character at the beginning (which didn't carry over in cut-paste to here).

Hmm. This is a stubborn one. I still get the same error.

OK, thanks anyway.

Anyone else able to help please?

I would be willing to use entirely new code if it were easier to get help that way.