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

    Need leading zeros exported to text file

    Through various calculations, I create a sheet in a workbook.
    I've formatted the B and C columns using a special format (B is "00000000" and C is "0000") as in this code:

    Code:
    Range("B1:B20000").NumberFormat = "00000000"
    Range("C1:C20000").NumberFormat = "00000"
    However, when the sheet is saved as a text file, the leading zeros vanish from the saved file.

    Is there another format I should be using for the columns? Any clues?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    The value is saved as a number so that is what you get in the export.
    If you want the exact format in the export you need to use text in the cell, but then you can't format it as required.

    cheers, Paul

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi kweaver

    When you save as a text file (or csv file), the leading zeros are in fact saved to the file.
    In fact, 'what you see in the excel display is what is saved to the file'.
    You can verify this by opening the saved file in Notepad.
    However, if you then open that csv file in Excel, it will treat the data numerically and display these without the leading zeros.

    zeddy

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm attaching a snapshot of the input and the output. Notice that the leading zero in fields 2 and 3 are missing in the output that's displayed in NotePad.

    I'm wondering if there's an adjustment that an be made to this part of the macro that will deal with fields 2 and 3.

    Code:
    For RowNdx = StartRow To EndRow
        WholeLine = ""
        For ColNdx = StartCol To EndCol
            If Cells(RowNdx, ColNdx).Value = "" Then
                CellValue = Chr(34) & Chr(34)
            Else
               CellValue = Cells(RowNdx, ColNdx).Value
            End If
            WholeLine = WholeLine & CellValue & Sep
        Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
        Print #FNum, WholeLine
    Next RowNdx
    Attached Images Attached Images
    Last edited by kweaver; 2016-09-21 at 11:53.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You didn't mention you were using code to save the text file for some reason. For that you need to use the cell's Text property rather than Value.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm afraid I don't know what to do. I'm piecing together VBA from others to do this and am stuck on this step.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..I think Rory is suggesting you try this:
    Code:
    For RowNdx = StartRow To EndRow
        WholeLine = ""
        For ColNdx = StartCol To EndCol
            If Cells(RowNdx, ColNdx).Text = "" Then
                CellValue = Chr(34) & Chr(34)
            Else
               CellValue = Cells(RowNdx, ColNdx).Text
            End If
            WholeLine = WholeLine & CellValue & Sep
        Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
        Print #FNum, WholeLine
    Next RowNdx
    zeddy
    Last edited by zeddy; 2016-09-22 at 09:05.

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

    kweaver (2016-09-22)

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    So obvious (to you, but not to me). Slow learner! OY. 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
  •