Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loading a text file (Ex97/SR1)

    Hi,

    I am not sure if I am repeating previously asked question. If so, please redirect me to the appropriate posting.

    I have a text file of fixed format consisting of
    - column heading
    - No. of records, S1, S2, S3, E1, E2, E3 (S and E stand for start and end of record)
    - Records
    Example is attached.

    What I wish to do is write a macro that will prompt the user to load this file and put each number in a single cell till the end of the file, such as in the Tools -> Text to Column... function. Could I please get an example of such a macro?

    Thanks, Hanan.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=66264&page=&v iew=&sb=&o=&vc=1#Post66264>This Thread</A> might give you some hints.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    Thanks Legare, this will help.

    One more question. Is there anyway I can modify this macro to get the user to chose the text file, such as when you get an OPEN file window? This way the user will be able to choose his/her file.

    Hanan.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    You can use the GetOpenFilename method to display the open dialog box and get the filename from the user.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    Sorry to bother you again. Do you have any example of how to use the GetOpenFilename method?

    Thanks.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    Sub test()
    Dim sPath As String
    sPath = Application.GetOpenFilename()
    MsgBox sPath
    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    See if this will do what you want:

    <pre>Public Sub LoadFiles()
    Dim strLine As String
    Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
    Dim vFileToLoad As Variant
    vFileToLoad = Application.GetOpenFilename(FileFilter:="Text Files,(*.txt),All Files,(*.*)", _
    FilterIndex:=1, Title:="File to laod")
    If vFileToLoad = False Then
    MsgBox "No file to laod."
    Exit Sub
    End If
    I = 0
    iFileNum = FreeFile
    With Worksheets("Sheet1").Range("A1")
    Open vFileToLoad For Input As #iFileNum
    Do While Not EOF(iFileNum)
    Input #iFileNum, strLine
    iLen = InStr(strLine, Chr(9))
    If iLen > 0 Then
    strLine = Left(strLine, iLen - 1)
    End If
    strLine = Trim(strLine) & " "
    J = 0
    Do While Len(strLine) > 1
    iLen = InStr(strLine, " ")
    .Offset(I, J).Value = Trim(Left(strLine, iLen - 1))
    strLine = Trim(Right(strLine, Len(strLine) - iLen)) & " "
    J = J + 1
    Loop
    I = I + 1
    Loop
    Close #iFileNum
    End With
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    If you need to include a filter you could use<pre> Application.GetOpenFilename("Text Files (*.Log; *.txt), *.log")</pre>

    in Adam's code. If the user selects cancel False is returned, otherwise the file path & name.

    Andrew C

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loading a text file (Ex97/SR1)

    Thanks to all of you <img src=/S/joy.gif border=0 alt=joy width=23 height=23>. Your help is much appreciated.

Posting Permissions

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