Importing/Manipulating TAB seperated text file

[captainx]

Hope this is an easy one.

I have to import a pretty ugly text file on a regular basis, to make import much smoother I would like to read the file and attached lines 1, 2 and 3 together.

Then rinse and repeat.

This will stop us destroying the output file with columns and save a lot of hassle reformatting.

Can I use VB to Open, manipulate then Save As to keep the text file native before it gets into excel?

 

[Rasm]

Try this

    Dim Linevalues() As Variant
    Dim Counter As Long
    Dim Astr as string
    Counter = 0
    Dim FileName As String
    FileName = "C:\YourPath\YourFileName"
    Open FileName For Input As #1
    Do Until EOF(1) 'This simply counts number of lines in ASCII file
        Line Input #1, Astr
        Counter = Counter + 1
    Loop
    Close #1
    ReDim Linevalues(Counter)
    Counter = 0
    Open FileName For Input As #1
       Do Until EOF(1) 'Reads ASCII file line by line
          Line Input #1, Astr
          Linevalues(Counter) = Astr
          Counter = Counter + 1
       Loop
    Close #1
    Open FileName For Output As #1
        For i = 0 To Counter - 1
            Print #1, Linevalues(i)
        Next i
        
        'Your code here - what ever you want to add
        'Construct YourString
       
        Print #1, yourString
    Close #1

 

[Rasm]

This leaves the file as is - then you ad your values at the end - or did you want to combine the first 3 lines in ASCII file into a single string first - maybe I misunderstood

If so you can do this

Open file - do the loop EOF(1) - End Of File

Line Input #1,Astr
Line Input #1,AAstr
Line Input #1,AAAstr

NewString= Astr & VbTab & AAstr & VbTab & AAAstr

Now place this into your worksheet

This is old old DOS code - but it still works - just not efficient - but vey durable.

 

[captainx]

If so you can do this

Open file - do the loop EOF(1) - End Of File

Line Input #1,Astr
Line Input #1,AAstr
Line Input #1,AAAstr

NewString= Astr & VbTab & AAstr & VbTab & AAAstr

That's gonna nail it, cheers.

I had just finished reading through the script and you have answered my question about joinging the lines.

Awesome, thanks.