Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: trying to get data from txt to Excel need help

    What am I trying to do:
    I have 130 txt files that look like so: Note spacing is correct

    Co% 99.9999
    ThickNessug 88.8888
    Depthug 0
    Epsilon% 7.77777
    DoseNew 6.66666e+016
    DoseOld 5.5555e+016
    Co 4.44444e+021

    I only need to copy the "DoseNew" value (6.66666e+016) and then put them into an excel sheet.
    Also I would like it if I could "User define" a portion of txt file name to open these files. Meaning that I run a program that generates a unique file name but is incremented to the 130 files, however I will be running other unique files in this same directory here is a sample of the .txt file name structure:

    XX_ABC_Monitor_die8_blablaRes_1_XX_Box.txt

    The “XX” portion is the same. The “Res_1” is the portion that increments example “Res_2”, “Res_3” and so on to “Res_130”

    What I have so far:

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    Dim fd_gefile As Variant
    Dim copercent, thickness, depth, epsilon, dosenew, doseold, co As Variant
    Dim temphold As Variant

    Path$ = Cells(11, 2) & "\" & Cells(7, 2) & Cells(110, 11)

    If icheck = 1 Then
    fd_gefile = datafile$
    GoTo 595
    End If

    With fd
    .InitialFileName = Path$
    If .Show = -1 Then
    For Each vrtSelectedItem In .SelectedItems
    temphold = vrtSelectedItem
    Next vrtSelectedItem
    fd_gefile = temphold
    Else
    'The user pressed Cancel.
    GoTo 203
    End If
    End With

    Set fd = Nothing

    ActiveSheet.Unprotect

    595 Open fd_gefile For Input As #1

    Line Input #1, copercent
    copercent = Trim(copercent)
    Length = Len(copercent) - 4
    Cells(14, 11) = Val(Right(copercent, Length))

    Line Input #1, thickness
    thickness = Trim(thickness)
    Length = Len(thickness) - 12
    Cells(15, 11) = Val(Right(thickness, Length))

    Line Input #1, depth
    depth = Trim(depth)
    Length = Len(depth) - 8
    Cells(16, 11) = Val(Right(depth, Length))

    Line Input #1, epsilon
    epsilon = Trim(epsilon)
    Length = Len(epsilon) - 9
    Cells(17, 11) = Val(Right(epsilon, Length))

    Line Input #1, dosenew
    dosenew = Trim(dosenew)
    Length = Len(dosenew) - 8
    Cells(18, 11) = Val(Right(dosenew, Length))

    Line Input #1, doseold
    doseold = Trim(doseold)
    Length = Len(doseold) - 8
    Cells(19, 11) = Val(Right(doseold, Length))

    Line Input #1, co
    co = Trim(co)
    Length = Len(co) - 3
    Cells(20, 11) = Val(Right(co, Length))

    Close #1
    icheck = 0

    203 ActiveSheet.Protect

    End Sub


    Just a little explanation:

    Path$ = Cells(11, 2) & "\" & Cells(7, 2) & Cells(110, 11)
    This part tells the macro where to look based upon user defined cell instructions

    This macro pulls all of the data from the text file, I can delete the other portions to just give me the “DoseNew” value. But my real question is how I can go about modifying this program (Or start over) with a program that can look at 130 files…any help would be great, thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    is this any help you could find all instances of these textfiles and loop them one at a time,

    Code:
    With Application.FileSearch
        .LookIn = "H:\EXCEL"
        .SearchSubFolders = True
        .Filename = "*mytext*.txt"
        If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderDescending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & " file(s) found."
            For I = 1 To .FoundFiles.Count
                Cells(I + 1, 1) = (.FoundFiles(I))
            Next I
        Else
            MsgBox "There were no files found."
        End If
    
    End With
    HTH
    Dave

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    Interesting suggestion, I'll see if I can implement that and get it to work, thanks for the suggestion, I am still open to other ideas.

    Thanks


Posting Permissions

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