I have a sheet set up with 2 columns of data (both numerical) and I am
using the code listed below to extract into a txt file, which works
fine.

0000000011000+00030+
0000000051000+00030+
8711000055380+00030+
8711000075371+00030+
8888021100013+00030+
etc etc etc

What I need to do is extract the data into a txt file which looks like

0000000011000+00030+0000000051000+00030+8711000055 380+00030+8711000075371+00030+
8888021100013+00030+ etc etc etc

I have not had experience before extracting from excel into a text file
and would appreciate some pointers.

Thanks,
Dean

Here is the code I have been testing (www.mcgimpsey.com)


Const DELIMITER As String = "+"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub

Not that I've tested it but wouldn't you just move the Print statement out
of the loop to below the Next? Getting rid of the sOut=empty needless to
say.

--
Jim
"Ledge" wrote in message
oups.com...
|I have a sheet set up with 2 columns of data (both numerical) and I am
| using the code listed below to extract into a txt file, which works
| fine.
|
| 0000000011000+00030+
| 0000000051000+00030+
| 8711000055380+00030+
| 8711000075371+00030+
| 8888021100013+00030+
| etc etc etc
|
| What I need to do is extract the data into a txt file which looks like
|
|
0000000011000+00030+0000000051000+00030+8711000055 380+00030+8711000075371+00030+
| 8888021100013+00030+ etc etc etc
|
| I have not had experience before extracting from excel into a text file
| and would appreciate some pointers.
|
| Thanks,
| Dean
|
| Here is the code I have been testing (www.mcgimpsey.com)
|
|
| Const DELIMITER As String = "+"
| Dim myRecord As Range
| Dim myField As Range
| Dim nFileNum As Long
| Dim sOut As String
|
| nFileNum = FreeFile
| Open "Test.txt" For Output As #nFileNum
| For Each myRecord In Range("A1:A" & _
| Range("A" & Rows.Count).End(xlUp).Row)
| With myRecord
| For Each myField In Range(.Cells(1), _
| Cells(.Row, Columns.Count).End(xlToLeft))
| sOut = sOut & DELIMITER & myField.Text
| Next myField
| Print #nFileNum, Mid(sOut, 2)
| sOut = Empty
| End With
| Next myRecord
| Close #nFileNum
| End Sub
|

If you put a semicolon after your print # statement, it suppresses the
CR LF.

Print #nFileNum, Mid(sOut, 2);

Len


Ledge wrote:
I have a sheet set up with 2 columns of data (both numerical) and I am
using the code listed below to extract into a txt file, which works
fine.

0000000011000+00030+
0000000051000+00030+
8711000055380+00030+
8711000075371+00030+
8888021100013+00030+
etc etc etc

What I need to do is extract the data into a txt file which looks like

0000000011000+00030+0000000051000+00030+8711000055 380+00030+8711000075371+00030+
8888021100013+00030+ etc etc etc

I have not had experience before extracting from excel into a text file
and would appreciate some pointers.

Thanks,
Dean

Here is the code I have been testing (www.mcgimpsey.com)


Const DELIMITER As String = "+"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub