Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    VBA code drops total line of TXT file. URGENT help needed if possible.

    [my VBA is not very good]

    I just got an Excel file with the code shown below. The initial TXT file on which is acts contains lines with fields separated by commas.
    So, two sample lines might look like:

    6/10/2016,236,3300,REG,8,16-1017,
    6/7/2016,339,2100,REG,8,15-1057,ballards,hotcheck

    The result desired should be:
    6/10/2016,236,1,3300,HRLY,8,16-1017,
    6/7/2016,339,1,2100,HRLY,8,15-1057,

    But, something in the macro manages to totally "kill" the line with multiple text fields separated with commas.
    What needs to be changed?
    Code:
    Sub ReformatFile()
        Dim FileIn As String
        Dim FileOut As String
        Dim sLine As String
        Dim sRecord() As String
        Dim FFIN As Integer
        Dim FFOUT As Integer
        Dim I As Integer
        
        With Application.FileDialog(msoFileDialogOpen)
            .AllowMultiSelect = False
            .Filters.Add "Text Files", "*.txt"
            .Show
            If .SelectedItems.Count = 0 Then
                MsgBox "File open aborted"
                Exit Sub
            End If
            FileIn = .SelectedItems(1)
        End With
        If FileIn > "" Then
            FileOut = Left(FileIn, Len(FileIn) - 4) & " - Reformatted.txt"
            FFIN = FreeFile
            Open FileIn For Input As FFIN
            FFOUT = FreeFile
            Open FileOut For Output As FFOUT
            Do Until EOF(FFIN)
                Line Input #FFIN, sLine
                sRecord = Split(sLine, ",")
                If UBound(sRecord) = 6 Then
                    Select Case Mid(sRecord(5), 4, 2)
                        Case "10"
                            If sRecord(3) = "REG" Then
                                sRecord(3) = "HRLY"
                            End If
                        Case Else 'should be only "20"
                            Select Case sRecord(3)
                                Case "REG"
                                    sRecord(3) = "SVCHR"
                                Case "OT"
                                    sRecord(3) = "SVCOT"
                                Case "DT"
                                    sRecord(3) = "SVCDT"
                            End Select
                    End Select
                    For I = 6 To 3 Step -1
                        sRecord(I) = sRecord(I - 1)
                    Next I
                    sRecord(2) = "1"
                    sLine = Join(sRecord(), ",") & "," 'you can remove the trailing comma if not needed
                    Print #FFOUT, sLine
                End If
            Loop
            Close FFIN
            Close FFOUT
            MsgBox "Finished"
        End If
    End Sub
    Last edited by RetiredGeek; 2016-06-30 at 16:08. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    Code:
                If UBound(sRecord) = 6 Then  '*** ON Rec#2 UBound(sRecord) = 7 so all this code is skipped!
                    Select Case Mid(sRecord(5), 4, 2)
                        Case "10"
                            If sRecord(3) = "REG" Then
                                sRecord(3) = "HRLY"
                            End If
                        Case Else 'should be only "20"
                            Select Case sRecord(3)
                                Case "REG"
                                    sRecord(3) = "SVCHR"
                                Case "OT"
                                    sRecord(3) = "SVCOT"
                                Case "DT"
                                    sRecord(3) = "SVCDT"
                            End Select
                    End Select
                    For I = 6 To 3 Step -1
                        sRecord(I) = sRecord(I - 1)
                    Next I
                    sRecord(2) = "1"
                    sLine = Join(sRecord(), ",") & "," 'you can remove the trailing comma if not needed
                    Print #FFOUT, sLine
                End If
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    RG...thanks for that...so, what should I do? Check for >=6 ? I tried that and it didn't solve the problem.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    What should the code do if there are more than 6 items?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    It should ignore all of the "comment fields" and just return the other data fields.

    Users sometimes separate "comment fields" with one or more commas and they should all be ignored and only the other data addressed.

    So, this line: 6/7/2016,339,2100,REG,8,15-1057,ballards,hotcheck

    could even possibly be 6/7/2016,339,2100,REG,8,15-1057,ballards,hotcheck, garage

    and all of that text in those three fields should be ignored.
    I would need to get the data up to and including the 6th comma.

    So, I think this line: sRecord = Split(sLine, ",") needs to be adjusted somehow because this will split the line with too many fields.
    Last edited by kweaver; 2016-06-30 at 16:53.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I ended up with all the comment fields still showing in the finished file.

    The line needs to be edited and all of the comment fields removed.

    This works: sLine = Left(sLine, 35) but I cannot guarantee that 35 will always work.

    I picked 35 because in my sample data the 6th comma delim is safely at 35th position.
    Ideally, that 35 should be the location of the 6th comma.

    Is that programmable here?
    Last edited by kweaver; 2016-06-30 at 17:55.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    Sorry for the delay had to do something with the Wife!

    This will do what I think you are asking?

    Code:
    Option Explicit
    
    Sub ReformatFile()
    
        Dim FileIn    As String
        Dim FileOut   As String
        Dim sLine     As String
        Dim sRecord() As String
        Dim FFIN      As Integer
        Dim FFOUT     As Integer
        Dim I         As Integer
        
    '*** For testing only next 2 lines ***
        FFIN = 1
        FFOUT = 2
    '*** Remove when you have the FreeFile routine available
    
        With Application.FileDialog(msoFileDialogOpen)
            .AllowMultiSelect = False
            .Filters.Add "Text Files", "*.txt"
            .Show
            If .SelectedItems.Count = 0 Then
                MsgBox "File open aborted"
                Exit Sub
            End If
            FileIn = .SelectedItems(1)
        End With
        
        If FileIn <> "" Then
        
            FileOut = Left(FileIn, Len(FileIn) - 4) & " - Reformatted.txt"
            FFIN = FreeFile
            Open FileIn For Input As FFIN
            FFOUT = FreeFile
            Open FileOut For Output As FFOUT
            Do Until EOF(FFIN)
                Line Input #FFIN, sLine
                sRecord = Split(sLine, ",")
                    
                Select Case Mid(sRecord(5), 4, 2)
                      Case "10"
                          If sRecord(3) = "REG" Then
                            sRecord(3) = "HRLY"
                          End If
                      Case "20"
                          Select Case sRecord(3)
                              Case "REG"
                                   sRecord(3) = "SVCHR"
                                Case "OT"
                                    sRecord(3) = "SVCOT"
                                Case "DT"
                                    sRecord(3) = "SVCDT"
                          End Select
                      Case Else '*** Error You should Print Message ***
                End Select
            
                sLine = ""
                
                For I = 0 To 4
                   If (I = 2) Then
                    sLine = sLine + "1,"
                   End If
                    sLine = sLine + sRecord(I) + ","
                Next I
                    
                sLine = sLine + sRecord(5)
                Print #FFOUT, sLine
                  
            Loop
            
            Close FFIN
            Close FFOUT
            MsgBox "Finished"
            
        End If  'FileIn <>...
        
    End Sub 'ReformatFile()
    Sample Input:

    6/10/2016,236,3300,REG,8,16-1017,
    6/7/2016,339,2100,REG,8,15-1057,ballards,hotcheck

    Sample Output:

    6/10/2016,236,1,3300,HRLY,8,16-1017
    6/7/2016,339,1,2100,HRLY,8,15-1057

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    kweaver (2016-06-30)

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    That's perfect and I understand what you did is even better! Thanks, RG...regards to the boss.

Posting Permissions

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