Hi All,

I am using a script to convert an Excel sheet to a txt file and it works ok.
But some fields need to be aligned to the right in stead of default left. Can
anyone tell me how to do this? Not all values but only some of the fields in
the excel sheets

The code used:

Public Sub FixedFieldTextFile()
Const DELIMITER As String = ""
Const PAD As String = " "
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String


vFieldArray = Array(4, 8, 4, 4, 15, 4, 13, 8, 8, 25, 40, 123)
nFileNum = FreeFile
Open "c:\NOB50850.txt" For Output As #nFileNum

For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))

Next i
POLine = POLine + 1
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With

Next myRecord
Close #nFileNum
End Sub

The excel sheet:
Left Left Right Left
Right
5010 12345678 1 Custpart1 0 10
Left Left Left
01/07/07 19/06/07 G16SA3C

I might be missing the boat here - but arent txt files meant to strip all
formating?

What is it that you are doing with the txt files?
If its just as a "storage" file to be imported into another excel sheet,
would it not be better to format the data once its in a program that supports
formatting?

Regards
Steve

"Frank" wrote:

Hi All,

I am using a script to convert an Excel sheet to a txt file and it works ok.
But some fields need to be aligned to the right in stead of default left. Can
anyone tell me how to do this? Not all values but only some of the fields in
the excel sheets

The code used:

Public Sub FixedFieldTextFile()
Const DELIMITER As String = ""
Const PAD As String = " "
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String


vFieldArray = Array(4, 8, 4, 4, 15, 4, 13, 8, 8, 25, 40, 123)
nFileNum = FreeFile
Open "c:\NOB50850.txt" For Output As #nFileNum

For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))

Next i
POLine = POLine + 1
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With

Next myRecord
Close #nFileNum
End Sub

The excel sheet:
Left Left Right Left
Right
5010 12345678 1 Custpart1 0 10
Left Left Left
01/07/07 19/06/07 G16SA3C

Hi Steve,

I know what you mean, but this file will be imported into a program, but the
program on supports the file if the format is correct. So I need a way to map
the fields in the correct way. Is there any possibility for this?

Thanks!

"steve_doc" wrote:

I might be missing the boat here - but arent txt files meant to strip all
formating?

What is it that you are doing with the txt files?
If its just as a "storage" file to be imported into another excel sheet,
would it not be better to format the data once its in a program that supports
formatting?

Regards
Steve

"Frank" wrote:

Hi All,

I am using a script to convert an Excel sheet to a txt file and it works ok.
But some fields need to be aligned to the right in stead of default left. Can
anyone tell me how to do this? Not all values but only some of the fields in
the excel sheets

The code used:

Public Sub FixedFieldTextFile()
Const DELIMITER As String = ""
Const PAD As String = " "
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String


vFieldArray = Array(4, 8, 4, 4, 15, 4, 13, 8, 8, 25, 40, 123)
nFileNum = FreeFile
Open "c:\NOB50850.txt" For Output As #nFileNum

For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))

Next i
POLine = POLine + 1
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With

Next myRecord
Close #nFileNum
End Sub

The excel sheet:
Left Left Right Left
Right
5010 12345678 1 Custpart1 0 10
Left Left Left
01/07/07 19/06/07 G16SA3C

to the best of my knowledge no - but I satand to be corrected

What file formats does the importing program support?
Is it possible that it could support an .xls file?

A possible further option might be XML - but I know very little about XML
yet, as to whether it would support the formatting you are after - no idea,
sorry

"Frank" wrote:

Hi Steve,

I know what you mean, but this file will be imported into a program, but the
program on supports the file if the format is correct. So I need a way to map
the fields in the correct way. Is there any possibility for this?

Thanks!

"steve_doc" wrote:

I might be missing the boat here - but arent txt files meant to strip all
formating?

What is it that you are doing with the txt files?
If its just as a "storage" file to be imported into another excel sheet,
would it not be better to format the data once its in a program that supports
formatting?

Regards
Steve

"Frank" wrote:

Hi All,

I am using a script to convert an Excel sheet to a txt file and it works ok.
But some fields need to be aligned to the right in stead of default left. Can
anyone tell me how to do this? Not all values but only some of the fields in
the excel sheets

The code used:

Public Sub FixedFieldTextFile()
Const DELIMITER As String = ""
Const PAD As String = " "
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String


vFieldArray = Array(4, 8, 4, 4, 15, 4, 13, 8, 8, 25, 40, 123)
nFileNum = FreeFile
Open "c:\NOB50850.txt" For Output As #nFileNum

For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))

Next i
POLine = POLine + 1
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With

Next myRecord
Close #nFileNum
End Sub

The excel sheet:
Left Left Right Left
Right
5010 12345678 1 Custpart1 0 10
Left Left Left
01/07/07 19/06/07 G16SA3C

Do you mean a fixed width format, rather than a delimited format ?
Otherwise your question makes little sense for a text file.

NickHK

"Frank" ...
Hi All,

I am using a script to convert an Excel sheet to a txt file and it works
ok.
But some fields need to be aligned to the right in stead of default left.
Can
anyone tell me how to do this? Not all values but only some of the fields
in
the excel sheets

The code used:

Public Sub FixedFieldTextFile()
Const DELIMITER As String = ""
Const PAD As String = " "
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String


vFieldArray = Array(4, 8, 4, 4, 15, 4, 13, 8, 8, 25, 40, 123)
nFileNum = FreeFile
Open "c:\NOB50850.txt" For Output As #nFileNum

For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))

Next i
POLine = POLine + 1
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With

Next myRecord
Close #nFileNum
End Sub

The excel sheet:
Left Left Right Left
Right
5010 12345678 1 Custpart1 0 10
Left Left Left
01/07/07 19/06/07 G16SA3C