Read Files, Arrange and Transpose Data to Single Worksheet

Hello to all of you at Excel Forum,

I have a folder containing text files, all of which are of the same format. Every text file contains numeric data arranged in 3 rows by 18 columns. For brevity and for use as an example, I will abbreviate this to 3 rows by 3 columns. I'm trying to come up with an Excel 2003 Macro that will do the following:

1. Prompt the user to select the folder where the text files to be read are stored.

2. Read and output the text in the first text file to the current Excel worksheet

File text will appear in the worksheet as follows (all number appear in their own cell with three rows by three columns):

10 14 17
34 56 12
43 32 18

3. Rearrange the above data into a single column:

10
34
43
14
56
32
17
12
18

4. Transpose this single column of data into the first row of the current worksheet

10 34 43 14 56 32 17 12 18

5. Read and output the next file in the folder into the current excel worksheet, do the above manipulation and place the data into the second row of the current worksheet.

This will continue until all files in the selected folder are read, manipulated and placed into their respective rows in the current worksheet.

My attempts, feeble as they are, with VBA editor to achieve this output are as follows for the first two files (I'm trying to automate the process because there could be 50 or more files I'll have to do this for):

Please Login or Register  to view this content.

Any help anyone would care to offer on this matter would be greatly appreciated.

Thank you,



Sir Phobos IV

Can you supply two sample text files, please?

Click GO ADVANCED and use the paperclip icon to post them up.