Results 1 to 18 of 18

Text file to Excel conversion with Ascending order

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Question Text file to Excel conversion with Ascending order

    Dear all Mates,

    Kindly arrange the small modification in existing code.this code is developed many weeks back by well wisher from this site.

    My Code as below:
    Sub test()
        Dim fn As String, txt As String, n As Long, m As Object, mtch As Object, sm As Object, a()
        fn = Application.GetOpenFilename("TextFiles,*.anl")
        If fn = "False" Then Exit Sub
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
        With CreateObject("VBScript.RegExp")
            .Global = True: .MultiLine = True
            .Pattern = "^ *=+(\r\n)([^=]+\r\n)+"
            Set mtch = .Execute(txt)
            ReDim a(1 To mtch.Count)
            .Pattern = "^ +(.*?) +N O\. +(\d+)(.*\r\n){4}.*?: +(\d+\.\d+).*?: +(\d+\.\d+).*?X +(\d+\.\d+).*"
            For Each m In mtch
                If .test(m) Then
                    n = n + 1: Set sm = .Execute(m)(0).submatches
                    a(n) = Array(n, sm(0), sm(1), sm(3), sm(4), sm(4))
                End If
            Next
        End With
        If n > 0 Then
            ReDim Preserve a(1 To n)
            [a2].Resize(n, 6).Value = Application.Index(a, 0, 0)
        End If
    End Sub
    with help of above code developing excel file, and required modification is mentioned in sample excel file.and text file also attached.please find the attachments

    Kindly modify the Code

    Thanking you,

    Best regards.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by FDibbins; 08-18-2016 at 01:48 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Text file to Excel Conversion Macro
    By msb15 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-10-2016, 06:39 PM
  2. conversion of text file(delimited) into Excel file using macro
    By thara.p24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2014, 08:35 AM
  3. [SOLVED] How to put non-repeating text in ascending order
    By augustus88 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-06-2014, 07:10 PM
  4. [SOLVED] Sorting multiple values in ascending order - Excel 2010
    By M4RKM in forum Excel General
    Replies: 14
    Last Post: 12-30-2012, 06:37 AM
  5. Replies: 0
    Last Post: 07-11-2012, 08:28 PM
  6. one macro to sort text in ascending and descending order
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-13-2008, 10:24 PM
  7. Formatting cells in Excel with Ascending/Descending order
    By Andrew Buckley in forum Excel General
    Replies: 0
    Last Post: 11-30-2005, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1