Export Specific Columns To Txt File

[excelnube]

Hi guys,

I have developed a piece of code that exports my entire worksheet to a .txt file.

Rather than exporting the entire sheet, all i am looking to achieve is export Column A, B and J.

Here's my current code:

Sub csvfile()     
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\lee\temp.txt", True)
    Dim r
    Dim c
     
    For r = 1 To Range("A65536").End(xlUp).Row
        s = ""
        c = 1
        While Not IsEmpty(Cells(r, c))
            s = s & Cells(r, c) & "|"
            c = c + 1
        Wend
        a.writeline s 'write line
    Next r     
End Sub

Any help appreciated.

 

[Andrew Poulsom]

Instead of:

c = c + 1

try:

If c = 1 Then c = 2 Else c = 10

 

[Peter_SSs]

Instead of:

c = c + 1

try:

If c = 1 Then c = 2 Else c = 10

Andrew, won't that get stuck on c = 10?

My suggestion:
<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> csvfile()<br>    <SPAN style="color:#00007F">Dim</SPAN> fs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, t <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, l <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, mn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> fs = CreateObject("Scripting.FileSystemObject")<br>    <SPAN style="color:#00007F">Set</SPAN> a = fs.CreateTextFile("c:\temp.txt", <SPAN style="color:#00007F">True</SPAN>) <SPAN style="color:#007F00">'<- I changed this path</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Cols<br>    <br>    Cols = Split("A B J")<br>     <br>    <SPAN style="color:#00007F">For</SPAN> r = 1 <SPAN style="color:#00007F">To</SPAN> Range("A" & Rows.Count).End(xlUp).Row<br>        s = ""<br>        <SPAN style="color:#00007F">For</SPAN> c = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Cols)<br>            s = s & Cells(r, Cols(c)) & "|"<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        a.writeline s <SPAN style="color:#007F00">'write line</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Andrew Poulsom]

Andrew, won't that get stuck on c = 10?

Doesn't it get reset to 1 on Next r?

 

[Peter_SSs]

Doesn't it get reset to 1 on Next r?

I don't think it ever gets to Next r

While Not IsEmpty(Cells(r, c))
    s = s & Cells(r, c) & "|"
    If c = 1 Then c = 2 Else c = 10
Wend

Start at r=1 and assume Cells(1,1), Cells(1,2) and Cells(1,10) all contain data.
First time through this loop c=1 and at the end c gets set to 2.
Second time through Cells(1,2) is not empty and c gets set to 10 (since it is not =1)
Third time through Cells(1,10) is not empty and c gets set to 10 (since it is not =1)
Fourth time through Cells(1,10) is not empty and c gets set to 10 (since it is not =1)
etc

 

[Andrew Poulsom]

Yes, I see now. I had completely missed the While Wend.