How to generate a text file from Excel using a macro or script
 

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open file
WritePathName = MyPath & WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

CellData = Range("C5").value
tswrite.writeline CellData
CellData = Range("C6").value
tswrite.writeline CellData
CellData = Range("C7").value
tswrite.writeline CellData

tswrite.Close
End Sub

"Frank" wrote:

dear Ron,

Thanks a lot for replying that fast!
But I dont know much about VB and Excel. Thus, can u kindly hepl me with the
coding of this problem? If you can give me the specific coding of that it
will be superb. and also, can I create a command button on the excel
sheet and click it and then get the text file generated with the data?
Please reply!

Thanks!
Sory about posting in different threads! I am new to dis community!
Regards!



"Ron de Bruin" wrote:

I answer this in your other thread
Please post in one group

http://www.mcgimpsey.com/excel/textfiles.html



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank" wrote in message ...
I need to know the way of generating a text file from data availbabe in an
Excel spreadsheet either using a Macro or a Script!

Asume if I have some data in few cells such as C5, C6 and C7
then if I have a simple formula like Sum in another cell like C8 to get the
total of the above mentioned cells. How do I display the values in those
cells in a text file generated by a macro or script?

Please be kind enough to reply me if you know this!
Thanks a lot!
-Frank-

How to generate a text file from Excel using a macro or script
 

Thanks for your reply!
I am scared to mention that I am totally new to VBA coding. Thus I tried
coping and pasting your code into code view. But once when I press ctrl+F8 it
high lights fswrite of
Set fswrite = CreateObject("Scripting.FileSystemObject")
I am sory that I don't understand how to go ahead! I seek your assistance
further.
Thanks.
Look forward for a fast reply!
"Joel" wrote:

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open file
WritePathName = MyPath & WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

CellData = Range("C5").value
tswrite.writeline CellData
CellData = Range("C6").value
tswrite.writeline CellData
CellData = Range("C7").value
tswrite.writeline CellData

tswrite.Close
End Sub

"Frank" wrote:

dear Ron,

Thanks a lot for replying that fast!
But I dont know much about VB and Excel. Thus, can u kindly hepl me with the
coding of this problem? If you can give me the specific coding of that it
will be superb. and also, can I create a command button on the excel
sheet and click it and then get the text file generated with the data?
Please reply!

Thanks!
Sory about posting in different threads! I am new to dis community!
Regards!



"Ron de Bruin" wrote:

I answer this in your other thread
Please post in one group

http://www.mcgimpsey.com/excel/textfiles.html



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank" wrote in message ...
I need to know the way of generating a text file from data availbabe in an
Excel spreadsheet either using a Macro or a Script!

Asume if I have some data in few cells such as C5, C6 and C7
then if I have a simple formula like Sum in another cell like C8 to get the
total of the above mentioned cells. How do I display the values in those
cells in a text file generated by a macro or script?

Please be kind enough to reply me if you know this!
Thanks a lot!
-Frank-

How to generate a text file from Excel using a macro or script
 

Dear Joel,
I think I am still confused! I know where to copy and paste da code but
certain things get high lighted when I try to compile it!
I am not good in VB programming can u kindly explain to me little bit how to
do this?
I mean all what need to do is just to generate a text file with some data on
the spreadsheet using a button on the same excel sheet. Infact I know how to
get a button on da sheet using tools.
Look forward for a fast reply! Thank you a lot for your valuble time!
-Indika-

"Joel" wrote:

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open file
WritePathName = MyPath & WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

CellData = Range("C5").value
tswrite.writeline CellData
CellData = Range("C6").value
tswrite.writeline CellData
CellData = Range("C7").value
tswrite.writeline CellData

tswrite.Close
End Sub

"Frank" wrote:

dear Ron,

Thanks a lot for replying that fast!
But I dont know much about VB and Excel. Thus, can u kindly hepl me with the
coding of this problem? If you can give me the specific coding of that it
will be superb. and also, can I create a command button on the excel
sheet and click it and then get the text file generated with the data?
Please reply!

Thanks!
Sory about posting in different threads! I am new to dis community!
Regards!



"Ron de Bruin" wrote:

I answer this in your other thread
Please post in one group

http://www.mcgimpsey.com/excel/textfiles.html



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank" wrote in message ...
I need to know the way of generating a text file from data availbabe in an
Excel spreadsheet either using a Macro or a Script!

Asume if I have some data in few cells such as C5, C6 and C7
then if I have a simple formula like Sum in another cell like C8 to get the
total of the above mentioned cells. How do I display the values in those
cells in a text file generated by a macro or script?

Please be kind enough to reply me if you know this!
Thanks a lot!
-Frank-

How to generate a text file from Excel using a macro or script
 

Dear Joel,

ooops!
Thanks a lot!
I got what I wanted!
U made my life easy!

Thank you very much again!
Frank

"Frank" wrote:

Dear Joel,
I think I am still confused! I know where to copy and paste da code but
certain things get high lighted when I try to compile it!
I am not good in VB programming can u kindly explain to me little bit how to
do this?
I mean all what need to do is just to generate a text file with some data on
the spreadsheet using a button on the same excel sheet. Infact I know how to
get a button on da sheet using tools.
Look forward for a fast reply! Thank you a lot for your valuble time!


"Joel" wrote:

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open file
WritePathName = MyPath & WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

CellData = Range("C5").value
tswrite.writeline CellData
CellData = Range("C6").value
tswrite.writeline CellData
CellData = Range("C7").value
tswrite.writeline CellData

tswrite.Close
End Sub

"Frank" wrote:

dear Ron,

Thanks a lot for replying that fast!
But I dont know much about VB and Excel. Thus, can u kindly hepl me with the
coding of this problem? If you can give me the specific coding of that it
will be superb. and also, can I create a command button on the excel
sheet and click it and then get the text file generated with the data?
Please reply!

Thanks!
Sory about posting in different threads! I am new to dis community!
Regards!



"Ron de Bruin" wrote:

I answer this in your other thread
Please post in one group

http://www.mcgimpsey.com/excel/textfiles.html



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank" wrote in message ...
I need to know the way of generating a text file from data availbabe in an
Excel spreadsheet either using a Macro or a Script!

Asume if I have some data in few cells such as C5, C6 and C7
then if I have a simple formula like Sum in another cell like C8 to get the
total of the above mentioned cells. How do I display the values in those
cells in a text file generated by a macro or script?

Please be kind enough to reply me if you know this!
Thanks a lot!
-Frank-