Macro for write a txt file



Hi all,

Thanks to you all I've been improving in VBA so thanks again and great this
forums exist.

Now I have a doubt and I'm not sure how to do it, I want to write a txt file
with the information of some cells separating them with commas. I have
already use the worksheets.saveas function, the text file is generated
correctly but I llike to see if there is any other option, maybe like the
open function in order to write only the txt file.

Do anyone have any idea for doing this?

Thanks again

Rick Rothstein

Are your cells in a contiguous range or are they scattered all around the
worksheet? If scattered all around, is there a regular pattern to them? It
would help if you could tell us the range you are wanting to save.

Mike H


You aren't clear about what you want to write but you can create a text file
like this

Sub write_Texts()
Dim SomeName As String
SomeName = "MyTextFile"
saveDir = "C:\" 'Change to suit
targetfile = saveDir & SomeName & ".txt"

Open targetfile For Output As #1
Print #1, Range("A1").Text

Close #1
End Sub


Sam Wilson

Hi, this should give you an idea of the basics:

Sub demo()

Dim fs As Object
Dim a As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.createtextfile("C:\demo.txt", True)

a.writeline ("line1, blah blah blah")
a.writeline ("line2, blah blah blah blah")


End Sub

It creates a 2 line text file in root C etc.

Patrick Molloy

instead of this
Print #1, Range("A1").Text
which is quiet correct, you could do this:

for each cell in Selection | Range("A1:H10")
Print #1, cell.Text

Patrick Molloy

Option Explicit

Sub pushdata()
Dim cell As Range
Dim txt As String

For Each cell In Range("C5:D21")
txt = txt & "," & cell.Text
Open "C:\temp\data.txt" For Output As #1
Print #1, Mid(txt, 2)
Close #1

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question