I see my newsreader wrapped one of the statements, so I am guessing yours
did too. Here is the code again, this time using a line continuation so that
you can simply copy/paste the code in...
Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
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")
Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd")
Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd")
Mid$(Record, 36) = Format$(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, Record
Close #FF
End With
End Sub
Rick
"Rick Rothstein (MVP -
VB)" wrote in
message ...
Okay, give the following (untested, but it should work fine) macro a try.
Just assign the appropriate worksheet reference in the With statement and
assign the filename (with its path) in the variable named FileNameAndPath
(replacing my example text, of course).
Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
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")
Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd")
Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd")
Mid$(Record, 36) = Format$(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, Record
Close #FF
End With
End Sub
Rick
"Rushna" wrote in message
...
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