Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Prompt for and then fix a TXT file

    I get a text file similar to the one attached except considerably more rows.
    I need a macro within Excel that will prompt for the text file (browse and click) and then look through the first field.
    Any row entry in field 1 that is a number is left alone; any entry that has a letter anywhere in field 1 has the 3rd field changed to have a "0" (a zero) placed to the left of the third digit (becoming the fourth digit).

    So, the next to last entry in the attachment is changed so that the 3rd field becomes 30260 and the last entry is change to become 130260.

    Probably the resulting text file should be named the same as the input file with some designation after the source file name like "Reformatted"

    Is that a piece of cake?

    TIA

    Kevin
    Attached Files Attached Files
    Last edited by kweaver; 2016-08-04 at 11:36.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Kweaver

    The solution I propose does not use a Macro but its fast and should meet your needs.

    Attached in the Workbook "Input2.xlsx" It has been set up to be the master Workbook.

    What you would do is first import the text file into an Excel workbook and change the Worksheet name to what you want it to be.

    Next Open the Input2.xlsx workbook and copy the renamed worksheet into the master Workbook
    Then on the "Revised Data" Worksheet in Cell H3 type in the exact name of the imported worksheet.
    You will now have the data revised as you requested. If you need it be "Flat" just copy and paste special "Values"

    Hope this helps.

    TD
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks, duthiet. That's too many steps for the user. They want to click a button and have a macro navigate to the specific folder and automatically make the changes as I noted. If I were doing this for them over and over again, I could easily do what you've suggested, but the user needs it to be automatic. Actually, even if I were doing it for them, I'd want an automatic macro to do it for me as well.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Try this:

    Place in ThisWorkbook Module:
    Code:
    Private Sub Workbook_Open()
        Application.WindowState = xlMinimized
        OpenFile
    End Sub
    Place in a standard module:
    Code:
    Public Sub OpenFile()
    Application.ScreenUpdating = False
    '------------------------------------------
    'DECLARE VARIABLES
    Dim Filter As String
    Dim Caption As String
    Dim Fname As String
    Dim CurPath As String
    '------------------------------------------
    'SET VARIABLES
    Filter = "Text files (*.txt),*.txt"
    Caption = "Please Select the source file "
    CurPath = CurDir     'GET CURRENT DIRECTORY
    ChDir ("C:\Users\Maudibe\Desktop")
    '------------------------------------------
    'OPEN TEXT FILE
    Fname = Application.GetOpenFilename(Filter, , Caption)
    Workbooks.OpenText Filename:=Fname, Origin:=437, StartRow:=1, DataType:=xlDelimited, Tab:=True, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    '------------------------------------------
    'CHECK FOR TEXT VALUES AND INSERT ZERO
    For I = 1 To LastRow
        If Not IsNumeric(Cells(I, 1)) Then
            Num1 = Left(Cells(I, 3), 2)
            Num2 = Right(Cells(I, 3), Len(Cells(I, 3)) - 2)
            Cells(I, 3) = Num1 & "0" & Num2
        End If
    Next I
    '------------------------------------------
    'SAVE AND CLOSE FILES
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.Quit
    End Sub
    I purposely did not code error handling so that there would be a way to open the workbook. Just click cancel when browsing for the text file.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maudibe, that's heading in the right direction.

    But, I'm trying to avoid any user requirements to change the code (e.g., I don't want them to have to modify the chDir line).
    Is there a way to prompt and browse for the text file without having it launch onOpen? Then, fix the text file but save it under a new name similar to the old? If the original is Input1, maybe the new, changed could be Input1-Edited.

    BTW: the 3rd field could be a multi-digit number, not always 4 or 5 digits. But the change is always to the left of the 3rd digit from the right. So, 12345 becomes 120345 and 999555 becomes 9990555. Is this valid: Num1 = Left(Cells(I, 3), Len(Cells(I, 3)) - 3)


    /Kevin
    Last edited by kweaver; 2016-08-04 at 19:50.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    any entry that has a letter anywhere in field 1 has the 3rd field changed to have a "0" (a zero) placed to the left of the third digit (becoming the fourth digit).
    But the change is always to the left of the 3rd digit from the right

    Confusing. Here is the revised file that will not open minimized. Click on the button to run the code. The Open Dialogue box will open to the C:\Users folder (>= Win 7) which does not need user intervention. If XP then change the default folder to what you want. Navigate to the folder with the text file then open it. The conversion will take place followed by a message stating the conversion is complete with the new file name and path. If a zero is added, it will be the fourth digit from the right.
    Attached Files Attached Files
    Last edited by Maudibe; 2016-08-04 at 22:39.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud...

    I seem to have gotten this to work if I paste the text file data into a sheet and run this macro.
    Would be nice to be able to browse for it.

    Code:
    Sub Insert0()
    '
    ' Insert0 Macro
    '
    ' Dim filename As String, lineText As String
    ' Dim myrng As Range, i, j
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To LastRow
        If Not IsNumeric(Cells(i, 1)) Then
            Num1 = Left(Cells(i, 3), Len(Cells(i, 3)) - 3)
            Num2 = Right(Cells(i, 3), 3)
            Cells(i, 3) = Num1 & "0" & Num2
        End If
    Next i
    
        
        filename = ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"
        ' LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Open filename For Output As #1
        Data = Range("A1:G" & LastRow)
        Set myrng = Range("data")
        
        For i = 1 To myrng.Rows.Count
            For j = 1 To myrng.Columns.Count
                lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
            Next j
            Print #1, lineText
        Next i
        
        Close #1
    End Sub

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    HELP, Maud or someone!

    This now prompts now, but I need to adjust the Num1 and Num2 lines because they're no longer in the sheet.

    Code:
    Sub ReformatFile0()
    
        Dim FileIn    As String
        Dim FileOut   As String
        Dim sLine     As String
        Dim sRecord() As String
        Dim LastRow   As Integer
        Dim Num1      As Integer
        Dim Num2      As Integer
        Dim Data      As Range
        Dim myrng     As Range
        Dim i         As Integer
        Dim j         As Integer
        Dim lineText  As TextRange2
        
    
        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
        
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To LastRow
        If Not IsNumeric(Cells(i, 1)) Then
            Num1 = Left(Cells(i, 3), Len(Cells(i, 3)) - 3)
            Num2 = Right(Cells(i, 3), 3)
            Cells(i, 3) = Num1 & "0" & Num2
        End If
    Next i
    
        
        ' filename = ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"
        ' LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        FileOut = Left(FileIn, Len(FileIn) - 4) & " - Reformatted.txt"
        Open FileOut For Output As #1
        Data = Range("A1:G" & LastRow)
        Set myrng = Range("data")
        
        For i = 1 To myrng.Rows.Count
            For j = 1 To myrng.Columns.Count
                lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
            Next j
            Print #1, lineText
        Next i
        
        Close #1
        MsgBox "Finished"
            
        End If  'FileIn <>...
        
    End Sub 'ReformatFile0()

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Here's a slightly different approach.

    Code:
    Option Explicit
    
    Function GetFile() As String
    
       Dim intChoice As Integer
       Dim strPath   As String
       Dim vParts    As Variant
       Dim iCntr     As Integer
       
    'only allow the user to select one file
       Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
       
    'make the file dialog visible to the user
       intChoice = Application.FileDialog(msoFileDialogOpen).Show
       
    'Retrieve & Return Fully Qualified File Spec to the Caller
       If intChoice <> 0 Then
         'get the file path selected by the user
         strPath = Application.FileDialog( _
                   msoFileDialogOpen).SelectedItems(1)
         GetFile = strPath
       Else
         GetFile = ""
       End If
       
    End Function  'GetFile
    
    Sub ProcessFile()
    
       Dim zFileSpec As String
       Dim wkbMain   As Workbook
       Dim wkbRaw    As Workbook
       Dim lCntr     As Long
       Dim zTemp     As String
       
       Set wkbMain = ActiveWorkbook
       
       zFileSpec = GetFile()
       
       If (zFileSpec <> "") Then
         Workbooks.OpenText Filename:=zFileSpec, _
             Origin:=437, StartRow:=1, DataType:=xlDelimited, _
             TextQualifier:=xlNone, ConsecutiveDelimiter:=True, _
             Tab:=True, Semicolon:=False, Comma:=False, Space:=True, _
             Other:=False, FieldInfo:=Array(Array(1, 1), _
             Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
             TrailingMinusNumbers:=True
        Set wkbRaw = ActiveWorkbook   'In case you need the reference later!
       Else
         Exit Sub  'User didn't supply a file to load
       End If
       
       [A1].Select
       lCntr = 1
       
       Do
       
          If (WorksheetFunction.IsNumber(Cells(lCntr, "A")) = False) Then
            zTemp = Format(Cells(lCntr, "C").Value)
            Cells(lCntr, "C").Formula = "=" + _
                  Left(zTemp, Len(zTemp) - 3) + "0" + Right(zTemp, 3)
            
          End If
          
          lCntr = lCntr + 1
          
       Loop Until (Cells(lCntr, "A").Value = "")
       
    '*** End Processing ***
    
       Application.DisplayAlerts = True
    
       With wkbRaw
           wkbRaw.SaveAs "Fixed-" & wkbRaw.Name, xlTextWindows
           .Close
       End With 'wkbRaw
    
    End Sub  'ProcessFile
    This code will prompt the user for the file with a standard "File Open" dialog.

    It will then open the file and do the processing then save the file back out in the same format to the same directory with the Prefix "Fixed-" added to the file name.

    You'll note a difference in the file import specs and the method of processing the changed numbers.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud: Thanks. I posted my nonsense before realizing you did a slick solution. Thanks!!

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    TWO slick version. Thanks RG!!

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I got a new text file from the user. The layout (format) was slightly different from the original, but still a text file and still need the third field changed per the original definition (inserting a zero). I've attached it.

    It looks like this came from a CSV file with the commas. How an I modify either macro to handle this option? Should there be a variable assigned that defines the delimiter somehow?

    I was using Maud's. Not sure if RGs handles this situation.
    Attached Files Attached Files
    Last edited by kweaver; 2016-08-05 at 21:19.

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    This is the code segment that needs changing:
    Code:
    Workbooks.OpenText Filename:=zFileSpec, _
             Origin:=437, StartRow:=1, DataType:=xlDelimited, _
             TextQualifier:=xlNone, ConsecutiveDelimiter:=True, _
             Tab:=True, Semicolon:=False, Comma:=False, Space:=True, _
             Other:=False, FieldInfo:=Array(Array(1, 1), _
             Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
             TrailingMinusNumbers:=True
    You'll notice that Comma:=False, you need to change that to true and set the other delimiters (Tab & Space) to False. You might also want to set the ConsecutiveDelimiter argument to False as with a comma delimited file two consecutive delimiters indicate a null value field.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks. The file I attached was NOT the way they're going to send the text file after all.
    SIGH.

    When they resent the correctly formatted file, of course it worked.

    I'll keep your comment and code change handy just in case.

    If it weren't for customers, this would all be fun, wouldn't it? Always an interesting challenge. Keeps the old brain working!!

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG, when I made your changes, while the final result was correct, three undesirable things happened:

    1. It opened an Excel sheet with the text data in it (don't want user to see that) and
    2. It asked about saving the file...it should just do it and
    3. The resulting file had double sets of quotes around the previously existing quotes in field 1 [how do I avoid this?] They have (just) confirmed that they will always have the first field with quotes around it and there will be commas separating the fields.

    So, now I have to confirm if the result should have quotes (not doubled up) around the first field and commas separating the fields. If that's the case, is that easily handled?

    I suspect that's in the IF statement where the "0" is inserted...but, there wouldn't be a commas after the last field.

    Kevin
    Last edited by kweaver; 2016-08-05 at 23:17.

Page 1 of 4 123 ... LastLast

Tags for this Thread

Posting Permissions

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