perry_fawn
Board Regular
- Joined
- Nov 18, 2010
- Messages
- 92
Hi,
I need to write a code to convert an excel spreadsheet to a txt file. I have found macro's that will do this in one shot but some column need to be formatted differently that others. For example col B needs to allow for 6 characters, left justified, with trailing blanks; col G needs to be 8 characters long in YYYYMMDD format; col I is a number that should be formatted to have no decimal points but needs to include 5 places before the decimal and 4 positions after, also it should be padded with leading and trailing zeros. Can anyone steer me on the right path?
Here is a bit of code that I found that preforms the conversion uniformly:
Thanks,
Fawn
I need to write a code to convert an excel spreadsheet to a txt file. I have found macro's that will do this in one shot but some column need to be formatted differently that others. For example col B needs to allow for 6 characters, left justified, with trailing blanks; col G needs to be 8 characters long in YYYYMMDD format; col I is a number that should be formatted to have no decimal points but needs to include 5 places before the decimal and 4 positions after, also it should be padded with leading and trailing zeros. Can anyone steer me on the right path?
Here is a bit of code that I found that preforms the conversion uniformly:
Code:
Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String
'vFieldArray contains field lengths, in characters, from field 1 to N
vFieldArray = Array(1, 6, 10, 2, 1, 1, 8, 8, 9, 9, 9, 2, 9, 2, 9, 1, 2, 2, 9, 8, 11, 9, 9, 9, 9, 3)
nFileNum = FreeFile
Open "Test.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
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
Thanks,
Fawn