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

Saved from a previous post:

You could concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Drag it down the column to get all that fixed width stuff.

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/groups?threadm=015...0a% 40phx.gbl

Rushna wrote:

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

--

Dave Peterson

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

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

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

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

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

Hi Rick,
Thanks for the prompt reply.
Sorry. Data starts from Col C as you have pointed out. It was my
mistake.

Since we import the data, other columns are blank. I need to only
export the columns (digit count shown) as follows:
Col C 1-9 5894 – should display as 5894(and 5 spaces) – total
9
Col F 10-19 Invoice number 12345678 – should display as
001234567 – total 10
Col H 20-27 Invoice date 21.02.2008 should display as 20080221 –
total 8
Col I 28-35 Payment date 21.02.2008 should display as 20080221
– total 8
Col K 36-50 Payment amount, leading zeros, 2 decimals, no
decimal sign – total 15
Col L 51-55 Currency code – EUR, should display EUR(and 2
spaces) – total 5
Col Q 56-66 BIC code bank, with or without branch – total 11

Thanks

On Jul 20, 8:34*pm, "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