ExcelBanter - Send worksheet to text file

Try if this suits:

Sub RangeToText()

Dim arr
Dim varDialogResult
Dim strFile As String
Dim strFileName As String

strFileName = Replace(ActiveWorkbook.Name, ".xls", ".txt", 1, -1,
vbTextCompare)

varDialogResult = _
Application.GetSaveAsFilename(InitialFileName:=str FileName, _
FileFilter:="Text Files (*.txt), *.txt")

'to take care of a cancelled dialog
'----------------------------------
If varDialogResult = False Then
Exit Sub
Else
strFile = varDialogResult
End If

If bFileExists(strFile) Then
If MsgBox(strFile & _
vbCrLf & vbCrLf & _
"Already exists, overwrite this file?", _
vbExclamation + vbYesNo + vbDefaultButton2, _
"save range to text file") = vbNo Then
Exit Sub
End If
End If

arr = ActiveWindow.RangeSelection

SaveArrayToText strFile, arr

End Sub

Sub SaveArrayToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal LBRow As Long = -1, _
Optional ByVal UBRow As Long = -1, _
Optional ByVal LBCol As Long = -1, _
Optional ByVal UBCol As Long = -1, _
Optional ByRef fieldArr As Variant)

'this one organises the text file like
'a table by inserting the right line breaks
'------------------------------------------
Dim R As Long
Dim C As Long
Dim hFile As Long

If LBRow = -1 Then
LBRow = LBound(arr, 1)
End If

If UBRow = -1 Then
UBRow = UBound(arr, 1)
End If

If LBCol = -1 Then
LBCol = LBound(arr, 2)
End If

If UBCol = -1 Then
UBCol = UBound(arr, 2)
End If

hFile = FreeFile

Open txtFile For Output As hFile

If IsMissing(fieldArr) Then
For R = LBRow To UBRow
For C = LBCol To UBCol
If C = UBCol Then
Write #hFile, arr(R, C)
Else
Write #hFile, arr(R, C);
End If
Next
Next
Else
For C = LBCol To UBCol
If C = UBCol Then
Write #hFile, fieldArr(C)
Else
Write #hFile, fieldArr(C);
End If
Next
For R = LBRow To UBRow
For C = LBCol To UBCol
If C = UBCol Then
Write #hFile, arr(R, C)
Else
Write #hFile, arr(R, C);
End If
Next
Next
End If

Close #hFile

End Sub

RBS

"mtlpp" wrote in
message ...

Hi all,

How to write codes or to send one worksheet to textfile.txt ?
or in plain word to save as textfile.txt outside excel itself.

Thanks in advance.

--
mtlpp
------------------------------------------------------------------------
mtlpp's Profile:
http://www.excelforum.com/member.php...o&userid=23348
View this thread: http://www.excelforum.com/showthread...hreadid=376426