export to text file from excel at fixed positions

[Arafat]

Hello,

I have got a code from one of the threads on Mr.Excel, but that code fails when it encounters a blank cell in a row and it add that value in the wrong position in the text file. What i want is even if a blank cell is found ,the next cell value should be at the correct specified position.

here is the code snippet.

Sub exporttext()
Dim i As Long
Dim fso As FileSystemObject
Dim hope As TextStream

Set fso = New FileSystemObject
Set hope = fso.CreateTextFile("C:\Users\Arafat\Desktop\os\test.txt", True)
With hope
i = 1
While Cells(i, 1).Value <> ""
.WriteLine Format(Cells(i, 1), "!" & String(10, "@")) & Format(Cells(i, 2), String(20, "@")) _
& Format(Cells(i, 3), "!" & String(5, "@"))
i = i + 1
Wend
.Close
End With

Dim x
x = Shell("notepad.exe C:\Users\Arafat\Desktop\os\test.txt", 1)
End Sub