Import and transpose tab seperated data from text file

[phillip harrison]

Hi there,

Im in a bit of a pickle with this one and my in house VBA guru cant work
it out either, we are sure there must be a simple way but my knowledge
doesnt extend this far so if any one can help that really would be
simply great.

My setup:

I have a text file which looks like this:

udm mat desc udmdsn msize u1 u2 u3 u4
u5 (all the way to) u1800
LABJ AL LightAlloy Steel 30 0.15 5.35 4.56 0.005
4.88 etc etc

to explain what the above means, the top half of the data (up to but not
including LABJ) remains constant and needs to be in a vertical list
starting at cell B2. The rest of the data changes each time the file is
opened and needs to be in a vertical list starting in cell A1, though
there will always be the same number of values as there are in the first
list. The values are all tab seperated and there is a 'return' between
u1800 and LABJ. Also, and im not sure why, the rows in the text file are
of random length and sometimes the 'u' can be at the end of one row and
the, say, '7' can be at the start of the next row. The same happens
sometimes for the decimal values, for example for, say 0.15, the '0.'
would be at the end of one row and the '5' would be at the start of the
next.

Just to clarify I need the results to look like:
LABJ udm
AL mat
LighALloy desc
Steel udmdsn
30 msize
0.15 u1
5.35 u2
4.56 u3
0.005 u4
4.88 u5
... ...
5.35 u1800

I really hope someone can help me with this and hope to hear from you
soon

Thanks

Phillip

 

[Tom Ogilvy]

On way would be to open the file as a new workbook.

Then select row 1 and do a copy, then pastespecial => Transpose in Cell B2
on another sheet.

Then select row 2 and do a copy, then pastespecial => Transpose in Cell A1
on that new sheet.

Now close original file without saving.

You can do this manually with the macro recorder on (going through the text
wizard), then modify the code to generalize it, including using
Application.GetOpenFileName so you can select the file to open.

Regards,
Tom Ogilvy