Export Data to Text file with fix format
Your macro exported only one record i.e the last record as follows.
5894 003000096215.03.2015.03.20000000000049184USD AABBCCDDXXX
Please note the following errors
1) Col H and I - Date is exported as 15.03.20 (please note that the
year is 2006) - it should be displayed as 20060315 in both case.
2) Col K (value is -49,183.80) the output is displayed as
000000000049184 - it should be displayed as 000000004318380 (i.e
leading zeros, two decimal places without any decimal signs)
Argh! The single record print out was because I specified the wrong variable
in the Print statement. The other errors were because of my misreading what
you wrote. Give this revised code a try...
Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
Dim Dte As String
Dim Record As String
Dim TotalFile As String
Dim FileNameAndPath As String
FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
For X = 10 To LastRow
Record = Space$(66)
Mid$(Record, 1) = .Cells(X, "C").Value
Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
Dte = .Cells(X, "H").Value
Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
Dte = .Cells(X, "H").Value
Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value), _
"000000000000000")
Mid$(Record, 51) = .Cells(X, "L").Value
Mid$(Record, 56) = .Cells(X, "Q").Value
TotalFile = TotalFile & vbCrLf & Record
Next
FF = FreeFile
Open FileNameAndPath For Output As #FF
Print #FF, TotalFile
Close #FF
End With
End Sub
Rick
|