VBA Tab Indentation

[vahnx]

How do you specify the fixed indentation alignment for text output in a file.

ie my text file looks like:

HELLO | 534
BYE | 4
HOME | 635
GIRAFFEERAA | 11

I need it to look like:

HELLO--------|-534
BYE----------|-4
HOME--------|-635
GIRAFFEERAA-|-11

or

HELLO--------|-534
BYE----------|---4
HOME--------|-635
GIRAFFEERAA-|--11

where '-' represents a space for fixed alignment. Kind of like vbTab but a fixed length of my choosing. I did this before along time ago but forget how.

 

[Ruddles]

Being able to see your code would be helpful but in the absence of that, the general technique for ensuring a text string is padded to a certain length is as follows:-

str=left(str & space(32),32)

 

[vahnx]

Haha yes, padding, that's the word I was looking for.

Sub Test()
    Debug.Print Now & ": The following may take as long as 30 seconds to run and may seem to freeze. Please be patient."
    Debug.Print Now & ": Sizing arrays"
    ReDim fg(MaxRows), gr(MaxRows)
    Dim j, temp
    
    Debug.Print Now & ": Populating arrays"
    For i = 2 To MaxRows
        fg(i) = Range("AW" & i).Value
        gr(i) = Range("A" & i)
    Next i

    Debug.Print Now & ": Sorting arrays"
    For i = 2 To MaxRows
        j = i
        Do While j > 0 And fg(j - 1) > fg(j)
            temp = fg(j)
            fg(j) = fg(j - 1)
            fg(j - 1) = temp
            temp = gr(j)
            gr(j) = gr(j - 1)
            gr(j - 1) = temp
            j = j - 1
        Loop
    Next i
    
    Dim startingTime, endingTime

   myfile = "C:\output.txt"
   Debug.Print Now & ": Writing to " & myfile
   fnum = FreeFile()
  
   Dim str As String
   startingTime = Timer
   Open myfile For Output As fnum
   For i = MaxRows To 2 Step -1
            If Not fg(i) = "" Then
                str = gr(i) & " | " & fg(i)
                str = Left(str & Space(32), 32)
                Print #fnum, str
            End If
    Next i
    Close #fnum
    endingTime = Timer
    Debug.Print Now & ": Total time took ", startingTime - endingTime
End Sub

The output file remains looking the same.

 

[Ruddles]

I've just run this code and checked the output file and each line is padded to 32 characters on the right, exactly as you've programmed.

I think you want to pad each field separately though, the first on on the right and the second one on the left - no?

Try:-

                gr(i) = Left(gr(i) & Space(32), 32)
                fg(i) = Right(Space(32) & fg(i), 32)
                str = gr(i) & " | " & fg(i)

 

[vahnx]

My output file.. opened in both notepad and wordpad and still no padded spaces appear, not even in the debugging window when I print it there.

 

[Ruddles]

When I run your code, the lines are definitely padded on the right, at the end of each line, as programmed. You can't see them because they are spaces, but you can place your cursor on them. If you change the pad character to something visible, you will see them.

It doesn't matter in any case, because the source of the problem has been identified. Have you tried the revised code?

 

[vahnx]

Ah sorry I didn't see the revised code :D. Yeah it works now, thanks!