data formatting problem

The following code produces the output shown below. My problem is that everything is not aligned properly. I want all the numbers to be presented in standard columns like excel. However I just can't do that because of the difference in number widths like 1,75,751. Can somebody look at the code below and suggest something. I can't use flexigrid etc because the number of rows in the output might go to 1000.
___________________________________________________________________________________
Dim fso As New FileSystemObject
    Dim fsoStream As TextStream
    ' Create a text file, and return a reference to a TextStream
    Set fsoStream = fso.CreateTextFile("c:\ppcout.txt", True)
    ' Write to the file
   
   
    For i = 1 To period
    fsoStream.WriteLine _
    Str(demand(i)) + "     " + Str(a1(i)) _
  + "     " + Str(a2(i)) + "     " + Str(a3(i)) _
  + "     " + Str(a4(i)) + "     " + Str(a5(i))
 
   
    fsoStream.WriteBlankLines 2
    Next
   
   
    fsoStream.WriteLine ("-----------------------------")
    fsoStream.WriteLine ("Cost Matrix")
    fsoStream.WriteLine ("-----------------------------")
    fsoStream.WriteBlankLines 2
   
    For i1 = 1 To 5
    fsoStream.WriteLine Str(Module1.totalcost(i1))
    fsoStream.WriteBlankLines 1
    Next
   
     fsoStream.Close
    Set fsoStream = Nothing
    Set fso = Nothing

_________________________________________

 75      75      75      108      75      75


 0      0      0      0      0      0


 33      71      61      0      70      71


 28      0      0      38      0      0


 0      0      0      0      0      0


 10      0      10      0      70      0


-----------------------------
Cost Matrix
-----------------------------


 258

 328

 286

 424

 258
anshumaEngineeringAsked:
Who is Participating?
 
Shiju SasidharanAssoc Project ManagerCommented:
try this

'===============
Private Sub Command1_Click()
Dim fso As New FileSystemObject
Dim fsoStream As TextStream
Dim sAlign As String
Dim sTab As String

    Set fsoStream = fso.CreateTextFile("c:\ppcout.txt", True)
    sTab = LAlign(" ", 5)
    For i = 1 To period
       sAlign = LAlign(demand(i), 6) & sTab & LAlign(a1(i), 6) & sTab & LAlign(a2(i), 6) & sTab & LAlign(a3(i), 6) & sTab & LAlign(a4(i), 6) & sTab & LAlign(a5(i), 6)
       fsoStream.WriteLine sAlign
       fsoStream.WriteBlankLines 2
    Next
   
    fsoStream.WriteLine ("-----------------------------")
    fsoStream.WriteLine ("Cost Matrix")
    fsoStream.WriteLine ("-----------------------------")
    fsoStream.WriteBlankLines 2
   
    For i1 = 1 To 5
        fsoStream.WriteLine Str(Module1.totalcost(i1))
        fsoStream.WriteBlankLines 1
    Next
    fsoStream.Close
    Set fsoStream = Nothing
    Set fso = Nothing
End Sub
Public Function LAlign(ByVal StringToAlign As String, ByVal MaxSpace As Integer) As String
Dim iCount As String
    StringToAlign = Left(StringToAlign, MaxSpace)
    If MaxSpace - Len(StringToAlign) > 0 Then _
        StringToAlign = StringToAlign & Space(MaxSpace - Len(StringToAlign))
    LAlign = StringToAlign
End Function
'============================

hope this will help u

;-)
Shiju
0
 
cyberdevil67Commented:
Hi anshuma,

try

String.Format ("This is a test {0} and another {1}",Arg1,arg2)

Cheers!
0
 
DabasCommented:
From VB6 help on User-Defined String Formats (Format Function)

------------
You can use any of the following characters to create a format expression for strings:

Character Description
@ Character placeholder. Display a character or a space. If the string has a character in the position where the at symbol (@) appears in the format string, display it; otherwise, display a space in that position. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string.
....
------
In your case, I would suggest using something like:
fsoStream.WriteLine(Format(Str(demand(i)),"@@@@@@@@@@@@") + Format(Str(a1(i),"@@@@@@@@@@@") + .... etc

Dabas
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aryaomniCommented:
instead of using any fixed characters use vbtab

    fsoStream.WriteLine _
    Str(demand(i)) + vbTab + Str(a1(i)) _
  +  vbTab + Str(a2(i)) +  vbTab + Str(a3(i)) _
  +  vbTab + Str(a4(i)) +  vbTab + Str(a5(i))

0
 
cyberdevil67Commented:
Tabs are no good..

because you'll get the following

   100
   20

But if you use Format str you'l get
   100
    20

which looks better
0
 
aryaomniCommented:
But
the TextMetric gives diffterent character width for different characters . i.e width of "i" is less than width of "W" . charcter stuffinf will not give you proper results. also You can not predict which type of font the viewer of your text file . so its better to use tab .

in this case you can use this way
  fsoStream.WriteLine _
    Str(demand(i)) + vbTab + space( constFixedLength - len(Str(a1(i)) ) + Str(a1(i))

space(constFixedLength ) or space$(constFixedLength ) returns
constFixedLength sized string containing spaces


0
 
cyberdevil67Commented:
Well to be honest its a personal preference more than a must do it this way!!

Tabs or no Tabs the characters will still end up screwing the tab side of things anyway.

Unless you can force fixed width fonts...

     WWW     EEE
     III    JJJ

Will display differently and unaligned, with tabs or without tabs...
0
 
anshumaEngineeringAuthor Commented:
Thank you so much. It was very nice of you guys
0
 
Shiju SasidharanAssoc Project ManagerCommented:
thank u anshuma
for the points
;-)
Shiju
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.