On Jul 21, 7:57*pm, Rushna wrote:
On Jul 21, 12:20*am, "Rick Rothstein \(MVP - VB\)"





wrote:
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

Works perfect. Thanks a lot for your help.

Rushna.- Hide quoted text -

- Show quoted text -

Hello All,
The macro below provided by Mr. Rick Rothstein works perfect. It
export the worksheet to a text file. I wish to change it further to
suit to my needs.

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


Changes required:
The file should be saved in the given Folder Path using the Cell Value
(E5) with date and time stamped e.g if cell value (E5) in the excel
file is 1234 then the file should be saved as "1234 (date)
(time)".txt . If the value in E5 is 3456 then it should be saved as
"3456 (date) (time).txt"

At present it saves as filename.txt and it overwirtes the previous
version too. I need to have all the old files with date and time
stamped for future references using the Cell Value in E5.

Can someone help me please?

Thanks in advance

Rushna