Import text files to Excel file

[crakter]

Hello,

I was wondering if anyone could help me with a code that can be used on an button to open txt files and parse it into the bottom of the list in an table/sheet.
If you look at the attached file.
This is the format of the TXT file. There is a seperator | between the columns. So this should be split and added to different columns.

Please Login or Register  to view this content.

So "LAGER" should be in column A, "20130314" should be in column B and so on.

Hope someone can help me with this! I don't know where to start exactly.

[mrice]

Suggest that you record a macro for this by using the Text to Columns button on the Data Ribbon and specifying the pipe character (|) as the delimiter. Then inspect the code to find the line which does the splitting.

[Sean Thomas]

Hi, place this in sheet1 code
place a commandbutton on sheet 1

Please Login or Register  to view this content.

[crakter]

Thank you Sean,

That works great. I was just wondering. There seems to be a problem when there is a string like 3-6, 6-12 or 12-18 in some of the columns. It gets turned into 41428
/41614/43435 . I have tried to change the format of the cell to number or text but it doesn't seem to work. Have you got an idea?

Thanks again!

[Sean Thomas]

it sounds like it is reading it as a date.
Have you tried selecting the whole column and formatting as text or number before imprting the text?
failing that, if you can add another txt file with additional dat i will take a look

[Sean Thomas]

try changing all the 1's in this line to 2's
this changes the data to text rather than converting to date

Please Login or Register  to view this content.

[crakter]

Thanks agin Sean - that works perfectly!