export excelsheet into text file

[Mili]

How to export excel work sheet into a text file.

Regards

Mili

 

[jaf]

File>save as. There are several text format options.

 

[Mili]

Hi
I am using following code, not getting how to save the
text file to a directory.

Code :
-------------------------
Sub FileSaver()
Dim FileSaveName As String
Dim TextExportExcel As ThisWorkbook
MsgBox "Text File Name := " & ActiveSheet.Name
FileSaveName = Application.GetSaveAsFilename
(InitialFileName:=CStr(ActiveSheet.Name),
filefilter:="Text Files (*.txt), *.txt")
MsgBox " FileSaveName :" & FileSaveName
'TextExportExcel.SaveAs Filename:=FileSaveName
End Sub

Regards

Mili.

 

[jaf]

Hi Mili,

Sub FileSaver()
Dim FileSaveName As String
Dim TextExportExcel As ThisWorkbook
MsgBox "Text File Name := " & ActiveSheet.Name
FileSaveName =
Application.GetSaveAsFilename(InitialFileName:=CStr(ActiveSheet.Name),filefi
lter:="Text Files (*.txt), *.txt")
MsgBox " FileSaveName :" & FileSaveName
ThisWorkbook.SaveAs FileSaveName '<<<<<<<<<<<<<<<<<<<<<
End Sub

OR...

Sub FileSaver()
Dim FileSaveName As String
Dim TextExportExcel As Object '<<<<<<<<<
Set TextExportExcel = ThisWorkbook '<<<<<<<<<<<<
'MsgBox "Text File Name := " & ActiveSheet.Name
FileSaveName =
Application.GetSaveAsFilename(InitialFileName:=CStr(ActiveSheet.Name),
filefilter:="Text Files (*.txt), *.txt")
'MsgBox " FileSaveName :" & FileSaveName
TextExportExcel.SaveAs Filename:=FileSaveName
End Sub

 

[Mili]

Hi
Thanks for the help. I want store only the text
present in the selected Cell in Excel.

and for importing text file to Excel I am using following
code. How to proceed ???

code :
---------------------------------------------------------
Public Sub DisplayLastLogInformation()
Const LogFileName As String = "C:\test\test.txt" 'content
of test file are column1,column2,column3,column4,clumn5
Dim FileNum As Integer, tLine As String
FileNum = FreeFile ' next file number
Open LogFileName For Input Access Read Shared As
#FileNum ' open the file for reading
Do While Not EOF(FileNum)
Line Input #FileNum, tLine ' read a line from the
text file
Loop ' until the last line is read
Close #FileNum ' close the file
MsgBox tLine, vbInformation, "Last log information:"
End Sub

 

[Mili]

Hi
Thanks for the help. I want store only the text
present in the selected Cell in Excel.

and for importing text file to Excel I am using following
code. How to proceed ???

code :
---------------------------------------------------------
Public Sub DisplayLastLogInformation()
Const LogFileName As String = "C:\test\test.txt" 'content
of test file are column1,column2,column3,column4,clumn5
Dim FileNum As Integer, tLine As String
FileNum = FreeFile ' next file number
Open LogFileName For Input Access Read Shared As
#FileNum ' open the file for reading
Do While Not EOF(FileNum)
Line Input #FileNum, tLine ' read a line from the
text file
Loop ' until the last line is read
Close #FileNum ' close the file
MsgBox tLine, vbInformation, "Last log information:"
End Sub

 

[jaf]

Hi Mili,
This should do what you want.
You may need to do/add a texttocolumns. I don't know how your data is
formatted.
(watch the line wraps)

Sub FileSaver()
Dim FileSaveName As String
Dim TextExportExcel As Object
Set TextExportExcel = ThisWorkbook
Dim c As Object
Dim MyRange As Object
Set MyRange = ActiveCell.CurrentRegion.Rows
mypath = "c:\test\" 'set path to folder here
'or use mypath=Application.DefaultFilePath
'MsgBox "Text File Name := " & ActiveSheet.Name
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=CStr(mypath &
ActiveSheet.Name),

filefilter:="Text Files (*.txt), *.txt")
'MsgBox " FileSaveName :" & FileSaveName

WriteFile MyRange, FileSaveName
End Sub

Sub WriteFile(MyRange, FileSaveName)


Dim FF As Integer, MyLine As String
FF = 0
FileNum = FreeFile ' next file number
Open FileSaveName For Append As #FileNum ' open the file & add currently
selected data to the file

(or create it)
'use output instead of append if you want to overwrite the entire file
each time
For Each c In MyRange 'c=rows in range
'assuming five columns of data to be written to file
Print #FileNum, Cells(c.Row, c.Column).Text, Cells(c.Row, c.Column +
1).Text, Cells(c.Row, c.Column +

2).Text, Cells(c.Row, c.Column + 3).Text, Cells(c.Row, c.Column + 4).Text
Next

Close #FileNum ' close the file
'MsgBox MyLine, vbInformation, "Last log information:"

End Sub

Public Sub DisplayLastLogInformation()
Const LogFileName As String = "C:\test\test.txt" 'contentof test file are

column1,column2,column3,column4,clumn5
Dim FileNum As Integer, tLine As String
FileNum = 0: n = 0
FileNum = FreeFile ' next file number
Open LogFileName For Input Access Read Shared As #FileNum ' open the
file for reading

ThisWorkbook.Sheets("sheet2").Activate
Cells(1, 1).Select

Do While Not EOF(FileNum)
Line Input #FileNum, tLine ' read a line from the Text File
n = n + 1 'row counter

Sheets("sheet2").Cells(n, 1) = tLine

Loop ' until the last line is read
Close #FileNum ' close the file


MsgBox tLine, vbInformation, "Last log information:"
End Sub