Aligning Text Output in Variables

Formatting Report - Formatting Text Files

Sub Aligning_Text_In_Variables()

' ---------------------------------------------------------------
' Written By Shanmuga Sundara Raman for http://vbadud.blogspot.com
' ---------------------------------------------------------------

' Aligment of Text in Reports is a pain.. that too if the report is a flat file

' Here are a couple of ways to align

Dim sCharBuff As String
Dim sName As String
Dim sAge As String
Dim sAdd As String
Dim sPrint As String

' Left Alignment
' Example
' Name :
' Age :
' Address :


sCharBuff = "123456789123456789" ' Just for length
sName = "Name"
sAge = "Age"
sAdd = "Address"

sPrint = sCharBuff
LSet sPrint = sName
Debug.Print sPrint & ":"

sPrint = sCharBuff
LSet sPrint = sAge
Debug.Print sPrint & ":"

sPrint = sCharBuff
LSet sPrint = sAdd
Debug.Print sPrint & ":"

' Right Alignment
' Example
' Name:
' Age:
' Address:


sCharBuff = "123456789123456789" ' Just for length
sName = "Name"
sAge = "Age"
sAdd = "Address"

sPrint = sCharBuff
RSet sPrint = sName
Debug.Print sPrint & ":"

sPrint = sCharBuff
RSet sPrint = sAge
Debug.Print sPrint & ":"

sPrint = sCharBuff
RSet sPrint = sAdd
Debug.Print sPrint & ":"


' ---------------------------------------------------------------
' Aligning Text Output in Variables
' ---------------------------------------------------------------


End Sub

Spacing in Text Files

Formatting Report , Formatting Text Files, Aligning Text File

For a Neatly Spaced Text File:

Sub EquiSpaced_TextFile()

' ---------------------------------------------------------------
' Written By Shanmuga Sundara Raman for http://vbadud.blogspot.com
' ---------------------------------------------------------------

Dim iMaxLength As Integer

iMaxLength = 20

Open "d:\VBADudExamples\TabbedFile.txt" For Output As #1 ' Open file for output.

Print #1, "Name "; Tab(iMaxLength); "Santhosh Prabhakaran"
Print #1, "Place "; Tab(iMaxLength); "Madras"
Print #1, "Designation "; Tab(iMaxLength); "Admin Manager"

Close #1 ' Close file.

' Excel VBA, Write to Text File, Neatly Spaced Text File


End Sub