On Jul 20, 9:23*pm, Joel wrote:
You description has some wholes. *I did the best I could. *The code may
require minor changes.

Sub WriteText()
Const myFileName = "c:\temp\myfile.csv"
Const ForReading = 1, ForWriting = 2, _
* *ForAppending = 3

fileSaveName = Application.GetSaveAsFilename( _
* * fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
* * MsgBox ("Cannot Save File - exiting Sub")
* * Exit Sub
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile _
* *(fileSaveName, True)

LastRow = Range("C" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow

* *MyNumber = Format(Range("C" & RowCount).Value, "####")
* *outputLine = MyNumber & " * * "
* *MyNumber = Format(Range("F" & RowCount).Value, " *########")
* *outputLine = outputLine & MyNumber
* *MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD")
* *outputLine = outputLine & MyNumber
* *MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD")
* *outputLine = outputLine & MyNumber
* *MyNumber = Format(Abs(Range("K" & RowCount).Value), _
* * * "#000000000000000.00")
* *outputLine = outputLine & MyNumber
* *outputLine = outputLine & Range("L" & RowCount).Value & " *"
* *If Range("Q" & RowCount) < "" Then
* * * outputLine = outputLine & Range("Q" & RowCount) & _
* * * * *String(10 - Len(Range("Q" & RowCount)), " ")
* *End If
* *f.writeline outputLine
Next RowCount
f.Close
End Sub

"Rick Rothstein (MVP - VB)" wrote:



Can you clarify a couple of things? You say your data is in the range
D10:Q(20000+rows), but your field description shows the first column as C,
not D... which is it? If C is correct, what about column's D and E; if C
should have been D, then what about column E? I added up all the "data" you
laid out and get 63 characters, not the 66 you said would be there... if 66
is correct, and assuming I did not miscount, where do the additional 3
spaces go... in front or at the back of the 66 character record?

Rick

"Rushna" wrote in message
....
Hello All,
I am using Excel 2003 and have data in Range D10:Q(20,000+ rows).
I wish to export the all active rows in the range in the Excel file
to a text file (Notepad).

Details are as follow:

Col C (currently 4 digits) – to be exported (total 9 digits) with
trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank)
(blank)(blank)
Col F (currently 8 digits) – to be exported (total 10 digits) with two
leading blanks (eg 12345678 should show as 0012345678)
Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col K (currently -43,413.20) – to be exported as total 15 digits with
leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should
display as 000000004341320 and -1,234.23 should display as
000000000123423 etc)
Col L – to be exported with two trailing blanks (currently EUR –
should display as EUR(blank)(blank).
Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is
blank nothing should be exported

The output on each line in the text file should be a total width of 66
(including spaces) from various Columns in Excel as explained above
and displayed as follows:

1234(blank)(blank)(blank)(blank)
(blank)00123456782008022120080221000000004341320EU R(blank)
(blank)AABBCCDDXXX (this counts to 66 including blank spaces)

Thanks in advance.

Rushna- Hide quoted text -

- Show quoted text -

Hello Joel
When I run your macro it gives me "Run time error-13. Type Mismatch"

Please help

Thanks a lot