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

[Frank]

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-

 

[Ron de Bruin]

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

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

 

[Frank]

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!

 

[Joel]

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

 

[Ron de Bruin]

Hi Frank

If you are new to macros read this page first
http://www.rondebruin.nl/code.htm

See also the link on the bottom of the page

Try the code first from J.E's site and post back if you have problems.
If you not try it you will never learn VBA.

 

[Frank]

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!

 

[Frank]

dear Ron,
Thanks a lot for your guiding I went through the page you showed me!
And now I know where to copy and paste!
but I neet to create a button in da excel sheet and then get da text data
file open when clicked. I know to create a button on the excel sheet but I am
scared to mention I still have problems with the code!
Can U kindly help me with this?
Thanks!

 

[Ron de Bruin]

This will open the file after it create it
Is that what you want ?

Public Sub TextNoModification()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
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

Shell "notepad.exe Test.txt", vbNormalFocus

End Sub

 

[Frank]

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-

 

[Frank]

Dear Joel,

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

Thank you very much again!
Frank

 

[Frank]

Dear Ron,

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

Thank you very much again!
Frank

 

[Frank]

Daer Ron,

Your code solves my problem but the thing is it prints only the first row of
the sheet how do I print many rows?
Please reply!
Thanks!
-Frank-

 

[Frank]

Dear Ron,
Sory I was confused!
Your code exactly way the I want.
And sory previouse message was a mistake it prints the whole sheet!
But now my problem is, If I want to modify the code to get da text file with
the exact cell spaces, how do i do that?

Now da model acts like this if I take an example,

Price,Qty,Total
2,4,8
5,3,15


But what I want is this,

Price,Qty,Total
2, 4, 8
5, 3, 15


Is this possible? Using tab instead "," doesn't solve the problem
Thanks for your kindness! Hope you will reply me fast!
Thanks a lot!

-Frank-

 

[Frank]

Dear Ron,

I am sorry for sending so many posts!
But now I have solved the problem as you have instructed me in the previous
post I used vbTab command. and that workd!
Hooray!
I am very happy thanks a lot!
I thank both Ron and Joel!

Thank you again for spending your valuble time on this!

Keep up your good service!

-Frank-