Keep / Assign variable leading zeros

[Rusty]

Hi,

Am importing a load of data from a text file, splitting into columns, amending in excel then outputting a text file.

One (or more) of the columns has leading zeros which I want to retain.

eg:

000000000915000
000000000915000
000000004340000
000000003623130
000000003623130

Which comes in as:

915000
915000
4340000
3623130
3623130

Which is fine. But I want to make sure that when I output the text file, that they are all (in this case) 15 characters in length.

Therefore, to assign a variable number of leading zeros dependent on the cell value.

I'm sure I could write a macro to calculate cell by cell, but is there an easy way to do it all in one hit?

Thanks

 

[Rusty]

Heres the code...

Sub Journal_Import_Format_Import_File()

    Dim Orig_File As String
    Orig_File = Range("Cover!E3")
    
    Dim Location As String
    Location = Range("Cover!E5")
    
    Dim Out_File As String
    Out_File = Orig_File & "X"
    Range("Cover!E7") = Out_File
    
    Dim File_Ext As String
    File_Ext = Range("Cover!F3")
    
    Sheets("Work").Select
    Cells.Delete
    Columns("H:H").Select
    Selection.NumberFormat = "@"
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Location & Orig_File & "." & File_Ext, _
        Destination:=Range("A1"))
        .Name = Orig_File
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(15, 7, 8, 2, 1, 7, 7, 18, 1, 1, 5, 5, 15, 25, 8, 7, 8, 6, 8, 9, 8, 7, 1, 10, 5, 5, 18, 18, 1, 5, 4, 1, 1, 1, 1, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 8, 1, 1, 5, 3, 15, 88)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Rusty]

Have written code to deal with all possible scenarios... anyone want?