vba to append to a text file

[hccatmo]

I have cells like these:
A2
A3
A4
....
A54
which are not all 53 char in length.
I'd like to append only the content of A2 to A54 that is 53 char in length
to a text file.
Thx.
cg

 

[Rick Rothstein \(MVP - VB\)]

This code should do what you want...

Sub AppendText53()
Dim X As Long
Dim FF As Long
Dim Rnge As Range
Dim FileName As String
FileName = "C:\TEMP\Test.txt"
FF = FreeFile()
Open FileName For Append As #FF
For Each Rnge In Range("A2:A54")
If Len(Rnge.Value) = 53 Then Print #FF, Rnge.Value
Next
Close #FF
End Sub

Place it in the code window for the sheet you want to run it from and change the example filename I used to the filename of the actual file you want to append to. Of course, as you should always do with code you get from others, test it out on a sample file before applying it to your real file.

Rick

 

[hccatmo]

Thanks, Rick. I forget to mention that I'd like to have a new line for each
cell content.
Will try and keep you posted.
cg

 

[JP]

I think you would change it to

If Len(Rnge.Value) = 53 Then Print #FF, Rnge.Value, ""

but let's see if anyone else is able to assist.


HTH,
JP

 

[Rick Rothstein \(MVP - VB\)]

I assumed that... the code I posted already puts each cell's content onto it own line in the file it is appending to.

Rick

 

[hccatmo]

It works well, as expected. Thx. cg

This code should do what you want...

Sub AppendText53()
Dim X As Long
Dim FF As Long
Dim Rnge As Range
Dim FileName As String
FileName = "C:\TEMP\Test.txt"
FF = FreeFile()
Open FileName For Append As #FF
For Each Rnge In Range("A2:A54")
If Len(Rnge.Value) = 53 Then Print #FF, Rnge.Value
Next
Close #FF
End Sub

Place it in the code window for the sheet you want to run it from and change the example filename I used to the filename of the actual file you want to append to. Of course, as you should always do with code you get from others, test it out on a sample file before applying it to your real file.

Rick