VBA code to write data to txt with user specified spacing

[envime]

Hello,
I am new to vba coding. I have used excel a lot but not so much with macros.

Problem:
I have to write data into a txt file with specified spacing.
for example lets say the dotted boundaries represent the text file.
-------------------------------------------------------
101 1 10108 108 91010107

------------------------------------------------------
The numbers above are 1 10 10 8 10 8 9 10 10 10 7. Each of this number is within a cell in excel. The first number should write after 12 spaces from the left and each number has 2 spaces and so if the number within the excel cell a single digit then there should a space.
I hope I am not confusing.
Also for each line I have to insert an identifier within first 12 spaces.
I tried modifying this code but to no use .
Any help would be just awesome!!

Sub Totext()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer

myFile = Application.DefaultFilePath & "\test.prn"

Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then

Print #1, Round(cellValue, 0)
Else
Print #1, Round(cellValue, 0);
End If
Next j
Next i
Close #1

End Sub

 

[Reggie74]

'vba subroutine written for Envime - 06/10/2014
Sub datamanip()

    Dim a, fa As Variant            'objects to create the textfile
    Dim header As Integer           'to uniquely identify each row
    Dim tail As String              'the rest of the data in a particular row
    Dim i As Long                   'counter to increment to the next row
    Dim lastrow  As Long            'the no. of rows populated with a data entry
    Dim spacing As String           'determines the no of spaces
    
    
    Set fa = CreateObject("Scripting.FileSystemObject")
    Set a = fa.CreateTextFile(ActiveWorkbook.Path & "\Output.txt", True)

    'starting header
    header = 101
    
    'append unique identifier prior to concatenation of data
    tail = Space(9) & header & " "
    
    'initialise starting row no
    i = 1
    
    'depending on where you starting point is - i chose Column A1 BUT you can change this
    Cells(i, 1).Select
    
    'get the last row
    lastrow = Range("A65536").End(xlUp).Row
    
    'loop from row 1 to last row
    For iloop = 1 To lastrow
        'loop until an empty cell is found then continue to the next row
        Do While ActiveCell.Value <> ""
            Select Case Len(ActiveCell.Value)
                    Case 1:
                        tail = tail & ActiveCell.Value & " "
                        ActiveCell.Offset(0, 1).Select
                    
                    Case 2:
                        tail = tail & ActiveCell.Value
                         ActiveCell.Offset(0, 1).Select
            End Select
        Loop
    
        'output to text file
        a.writeline tail
        
        'increment unique header for next row
        header = header + 1
        
        'determine no of spacing
        spacing = 12 - Len(header) - 1
        
        'append unique identifier prior to concatenation of data
                
        tail = Space(spacing) & header & " "
                        
        'increment the counter to the next row
        i = i + 1
        Cells(i, 1).Select
        
    Next iloop
    
    'close the text file
    a.Close
    
End Sub

Hello,
I am new to vba coding. I have used excel a lot but not so much with macros.

Problem:
I have to write data into a txt file with specified spacing.
for example lets say the dotted boundaries represent the text file.
-------------------------------------------------------
101 1 10108 108 91010107

------------------------------------------------------
The numbers above are 1 10 10 8 10 8 9 10 10 10 7. Each of this number is within a cell in excel. The first number should write after 12 spaces from the left and each number has 2 spaces and so if the number within the excel cell a single digit then there should a space.
I hope I am not confusing.
Also for each line I have to insert an identifier within first 12 spaces.
I tried modifying this code but to no use .
Any help would be just awesome!!

Sub Totext()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer

myFile = Application.DefaultFilePath & "\test.prn"

Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then

Print #1, Round(cellValue, 0)
Else
Print #1, Round(cellValue, 0);
End If
Next j
Next i
Close #1

End Sub