CSV file with a predefined text length

  • Thread starter Thread starter sujantoh
  • Start date Start date
S

sujantoh

Hi All,

I need to covert excel file to text file (.csv or tab format) with a
predefined text length.

So if in excel file there is a text "Commercial Bank" in column A, and
"Avenue 11" in column B, normally if i do export to .csv file the
result will become:

Commercial Bank;Avenue 11

However I need to insert blank character automatically during the file
is converted to .csv file so the result becomes:

Commercial Bank ;Avenue 11

Anyone can help me on this trick....
THANKS....

Sjt
 
One way:

Public Sub OutputFixedFields()
Const nFWIDTH As Integer = 20
Const sDELIM As String = ";"
Const sPATH As String = "<your path here>"
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open sPATH & "File1.csv" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & sDELIM & Left(myField.Text & _
Space(nFWIDTH), nFWIDTH)
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1
End Sub
 
Back
Top