View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\)[_2343_] Rick Rothstein \(MVP - VB\)[_2343_] is offline
external usenet poster
 
Posts: 1
Default 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