Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import and transpose tab seperated data from text file
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import and transpose tab seperated data from text file
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 "phillip harrison" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Text File with Comma Seperated Data - Need Help Pls | Excel Discussion (Misc queries) | |||
comma seperated file import | Excel Discussion (Misc queries) | |||
Import text doc to XL. Need items seperated by " " in new column | Excel Discussion (Misc queries) | |||
import data from a text file | Excel Discussion (Misc queries) | |||
Get External Data, Import Text File, File name problem | Excel Programming |