Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Concatenate, Remove Period and Save as Text file Through Macro

    Hello,

    I'm seeking help to create a Fixed length text file, through macro to concatenate cell, remove period and save as text file. The excel file

    Col A------Col B-----'C'---------Col D--------Col E
    000012 000249001 011 00000001200.21 000000
    012345 110230112 010 00000001200.00 000000

    Column A width 6, B-9, C-3, D-14(1 of period that will be deleted) E-6

    The required TEXT FILE SAMPLE

    0000120002490010110000000120021000000
    0123451102301120100000000120000000000

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If you put this in F1 and fill down, you can then select the F column and paste into NotePad or whatever else you use as the text file.

    =A1&B1&C1&SUBSTITUTE(D1,".","")&E1

    I'm sure someone (not me) can provide you the code to select F and move that data to a txt file.

    I found this code that seems to do the trick:

    Sub SaveToTxt()
    Dim MyRange As Range
    Dim objFileSystem As Object, objTextStream As Object
    Dim cell As Range
    Set MyRange = ActiveSheet.Range _
    ("F1:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    Set objTextStream = objFileSystem.CreateTextFile("C:\TESTTEST.txt", True)
    For Each cell In MyRange.Cells
    objTextStream.writeline (cell.Value)
    Next
    objTextStream.Close
    End Sub
    Last edited by kweaver; 2013-10-08 at 18:46.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    You were posting while I was coding but I will put my solution here as well.

    foncesa,

    A different approach for your review. It will remove the periods, concatenate the cells (image2), and then save the file to the desktop as a text file with the same name as the excel file (image 3).

    HTH,
    Maud

    concat1.png concat2.png concat3.png

    Code:
    Public Sub ConcatenateCells()
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    
    For I = 1 To LastRow
        Cells(I, 4) = Replace(Cells(I, 4), ".", "")
        For J = 1 To LastCol
            Cells(I, 1) = Cells(I, 1) & Cells(I, J)
            Cells(I, J).ClearContents
        Next J
    Next I
    wbName = ActiveWorkbook.Name
    Application.DisplayAlerts = False
        ChDir "C:\Users\Maudibe\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\Maudibe\Desktop\" & wbName & ".txt", FileFormat _
            :=xlTextMSDOS, CreateBackup:=False
    Application.DisplayAlerts = True
    
    End Sub
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Maudibe for response, it saves the text file with removing the custom format of '0' which is to be preserved while saving to text file.
    Code:
    Sub Macro1()
        Columns("A:A").Select
        Selection.NumberFormat = "000000"
        Columns("B:B").Select
        Selection.NumberFormat = "000000000"
        Columns("C:C").Select
        Selection.NumberFormat = "000"
        Columns("D:D").Select
        Selection.NumberFormat = " 00000000000.00"
        Columns("E:E").Select
        Selection.NumberFormat = "000000"
    End Sub

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi foncesa

    I have amended the code in Maud's excellent file.
    Click the button in the attached file to see it work.

    This attached version saves the processed text file into the same location as where the attached file is saved, rather than the desktop. I like my desktop tidy.
    Also, it opens up the processed text file in Notepad, so you can check the contents.
    Finally, it returns you to the starting point in Excel, with the original file.

    Let us know if this works for you.

    zeddy
    Attached Files Attached Files

Posting Permissions

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