Writing results to text file. Spacing between data.

[Ergo30]

Hi

I pretty new to Visual basic 6, having only dealt with VBA I am after some advice about writing the results from my project to a text file. I have successfully written the code (which is pretty simple stuff I know) to write the data to the text file but I need to keep the spaces between each entry the same size so that I can transfer/convert it into excel at a later date. Is there a way to keep the spacing between the data the same for every row that is entered.

This is difficult to explain but I have a list of checkbox's that people will either tick one or all of them. The results from these are either 1 or 0. Then I have their username from Windows, the date, the time and then 3 text box for free text. The problem I have is the text box results, I dont mind setting the max length in the text box parameters but this would cause a problem if they only entered a small amount of text.

I will try and show an example of what I mean.

Data would be something like this

simon 01\02\2007 0 0 1 1 1 1 0 0 Text1 Text2 Text3
simon 02\02\2007 0 1 0 1 1 0 0 1 Moretext1 text 2 text3

What I would like to see is:

simon 01\02\2007 0 0 1 1 1 1 0 0 Text1 Text2 Text3
simon 02\02\2007 0 1 0 1 1 0 0 1 Moretext1 text 2 text 3
Bascially if you try to convert the first example in Excel I could not line up the columns on 'Text1, Text2' with More text1 and text2.

I hope this explains it for you all and hope that you can help.

Many Thanks

Simon

 

[gmmastros]

While building the string to save to the text file, you could use the Space and Left functions to accomplish this.

Ex:

    Dim Text1 As String
    Dim Text2 As String
    
    Text1 = "Hello"
    Text2 = "World"
    
    MsgBox Left(Text1 & Space(10), 10) & Left(Text2 & Space(10), 10)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ergo30]

George

I will give this a go and feed back here, looks good though. And easy.

Thanks

 

[strongm]

As an alternative you might like to look at the Excel Selection object's TextToColumns method

 

[Ergo30]

Strongm

I think what your saying is to store directly to Excel. If I am right could you let me have some code that does this for me. at the moment I am using PRINT to a text file and ideally it would be better to store direct into Excel. As this will a multiple user application I cannot have excel open for longer than it takes to store the data.

Thanks for replying, all comments are helpful

Many Thanks

 

[AndyGroom]

I know it's not exactly what you're asking for, but if the purpose of the Text file is simply a way to move the data from your application into Excel then surely an easier solution would be to create a CSV text file rather than use spaces?

Eg:

"simon","01\02\2007","0","0","1","1","1","1","0","0","Text1","Text2","Text3"

Excel would be far happier importing a CSV file than one formatted with spacing.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[Swi]

If Excel is the final format use the above mentioned suggestions. If not and you still want fixed fielded data fields try using a UDT (User Defined Type). Here is an example:

Option Explicit
Private Type Test
    Test1 As String * 10
    Test2 As String * 10
    Test3 As String * 50
    EOFMarker As String * 2
End Type
Dim udtTest As Test
Dim ff As Integer

Private Sub Command1_Click()
    ff = FreeFile
    Open "C:\Test.txt" For Binary Access Write As ff
    udtTest.Test1 = "AAAAA"
    udtTest.Test2 = "BBBBB"
    udtTest.Test3 = "CCCCCCCCCC"
    udtTest.EOFMarker = vbCrLf
    Put ff, , udtTest
    udtTest.Test1 = "XXXXX"
    udtTest.Test2 = "YYYYY"
    udtTest.Test3 = "ZZZZZZZZZZ"
    udtTest.EOFMarker = vbCrLf
    Put ff, , udtTest
    Close ff
    MsgBox "Done!", vbInformation
End Sub

Swi

 

[BobRodes]

<could you let me have some code that does this for me.

googling to "excel texttocolumns" yields many examples of code, and it appears that you haven't yet taken that step and investigated the results. Suppose you do that, and then come back with specific questions if you get stuck.