How to export a range of cells on a worksheet to a text file
 

Can anyone describe how to export the values in range A1 to F20 on
worksheet 'Data' to a text file? The text file will be named
'routes.txt'. The text file already exists, and it should be
overwritten with the new values each time the macro is executed,
rather than the macro appending the values to the text file.

Thanks kindly.

Best Regards,
Joseph

How to export a range of cells on a worksheet to a text file
 

Sub CreateFile()
Dim sh as Worksheet, sh1 as Worksheet
set sh = Activesheet
workbooks.Add Template:=xlWBATWorksheet
set sh1 = Activesheet
sh.Range("A1:F20").Copy
sh1.Range("A1").PasteSpecial xlValues
Application.DisplayAlerts = False
sh1.parent.SaveAs "C:\Myfolder\Myfile.txt", xlCSV '<== Change
Application.DisplayAlerts = True
End Sub

there are many different formats for text files. You don't say which you
want. Also, specify the full path and name of your file.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Can anyone describe how to export the values in range A1 to F20 on
worksheet 'Data' to a text file? The text file will be named
'routes.txt'. The text file already exists, and it should be
overwritten with the new values each time the macro is executed,
rather than the macro appending the values to the text file.

Thanks kindly.

Best Regards,
Joseph

How to export a range of cells on a worksheet to a text file
 

For fun, here's another way. As Tom says, depends if this format is
suitable.

Private Sub CommandButton1_Click()
Dim DatObj As DataObject
Dim FileNum As Long
Dim TempStr As String

Const TEXTFILE As String = "C:\routes.txt"
Const TEXTFORMAT As Long = 1

Range("A1:F20").Copy

Set DatObj = New DataObject
With DatObj
.GetFromClipboard
If .GetFormat(TEXTFORMAT) = True Then
FileNum = FreeFile
TempStr = .GetText(TEXTFORMAT)
'Seem to get an extra vbCrLf from the DataObject, so remove
TempStr = Left(TempStr, Len(TempStr) - 2)

Open TEXTFILE For Output As #FileNum
Print #FileNum, TempStr
Close #FileNum
End If
End With

Application.CutCopyMode = False

End Sub

NickHK

wrote in message
oups.com...
Can anyone describe how to export the values in range A1 to F20 on
worksheet 'Data' to a text file? The text file will be named
'routes.txt'. The text file already exists, and it should be
overwritten with the new values each time the macro is executed,
rather than the macro appending the values to the text file.

Thanks kindly.

Best Regards,
Joseph

How to export a range of cells on a worksheet to a text file
 

Thanks, Tom. That's just what I needed. (BTW, the text type will be MS-
DOS text, which is what I think Microsoft notepad uses, correct?)

Thanks much to NickHK for his input in the next post, too. It is
appreciated.

Best Regards,
Joseph


On Feb 13, 8:19 pm, "Tom Ogilvy" wrote:
Sub CreateFile()
Dim sh asWorksheet, sh1 asWorksheet
set sh = Activesheet
workbooks.Add Template:=xlWBATWorksheet
set sh1 = Activesheet
sh.Range("A1:F20").Copy
sh1.Range("A1").PasteSpecial xlValues
Application.DisplayAlerts = False
sh1.parent.SaveAs "C:\Myfolder\Myfile.txt", xlCSV '<== Change
Application.DisplayAlerts = True
End Sub

there are many different formats fortextfiles. You don't say which you
want. Also, specify the full path and name of your file.

--
Regards,
Tom Ogilvy

wrote in message

oups.com...



Can anyone describe how to export the values inrangeA1 to F20 on
worksheet'Data' to atextfile? Thetextfile will be named
'routes.txt'. Thetextfile already exists, and it should be
overwritten with the new values each time the macro is executed,
rather than the macro appending the values to thetextfile.

Thanks kindly.

Best Regards,
Joseph- Hide quotedtext-

- Show quotedtext-

How to export a range of cells on a worksheet to a text file
 

I would try either of these and see what you get

xlTextMSDOS

xlTextPrinter



--

Regards,

Tom Ogilvy



wrote in message
oups.com...
Thanks, Tom. That's just what I needed. (BTW, the text type will be MS-
DOS text, which is what I think Microsoft notepad uses, correct?)

Thanks much to NickHK for his input in the next post, too. It is
appreciated.

Best Regards,
Joseph


On Feb 13, 8:19 pm, "Tom Ogilvy" wrote:
Sub CreateFile()
Dim sh asWorksheet, sh1 asWorksheet
set sh = Activesheet
workbooks.Add Template:=xlWBATWorksheet
set sh1 = Activesheet
sh.Range("A1:F20").Copy
sh1.Range("A1").PasteSpecial xlValues
Application.DisplayAlerts = False
sh1.parent.SaveAs "C:\Myfolder\Myfile.txt", xlCSV '<== Change
Application.DisplayAlerts = True
End Sub

there are many different formats fortextfiles. You don't say which you
want. Also, specify the full path and name of your file.

--
Regards,
Tom Ogilvy

wrote in message

oups.com...



Can anyone describe how to export the values inrangeA1 to F20 on
worksheet'Data' to atextfile? Thetextfile will be named
'routes.txt'. Thetextfile already exists, and it should be
overwritten with the new values each time the macro is executed,
rather than the macro appending the values to thetextfile.

Thanks kindly.

Best Regards,
Joseph- Hide quotedtext-

- Show quotedtext-