Solved: Read in Excel, Write out to Text

[kwaku101]

Help, I'm trying to accomplish the following.
- Read data from cell A2 to C9.
- Write the data to a .txt document on my c drive.

So far the code below simply writes the second row of data 8x, I can't get it to loop through the rest of the rows. Please help if you can.

Thanks you

[VBA]Sub writetext()
Dim outfile As String, Num As Integer, Code1 As String, Code2 As String
outfile = "c:\samplefile.txt"
Open outfile For Output As #1
Num = Worksheets("text file").Range("A2")
Code1 = Worksheets("text file").Range("A3")
Code2 = Worksheets("text file").Range("A4")

For x = 2 To 9
Write #1, Num, Code1, Code2
Next x

Close #1
End Sub[/VBA]

[MaximS]

Can you post sample file?

[CreganTur]

Welcome to the forum- always good to see new members.

The reason your code isn't working is because there's nothing for you to increment within your For...Next loop. You need to be assigning your variable values inside the loop so that your range changes with each iteration.

Using your above code as an example:
[vba]Sub writetext()
Dim outfile As String, Num As Integer, Code1 As String, Code2 As String
outfile = "c:\samplefile.txt"
Open outfile For Output As #1

For x = 2 To 9
Num = Worksheets("text file").Range("A" & x)
Code1 = Worksheets("text file").Range("A" & x + 1)
Code2 = Worksheets("text file").Range("A" & x + 2)

Write #1, Num, Code1, Code2
Next x

Close #1
End Sub[/vba]

The above code is untested and doesn't include anything for dealing with changing to a different column.

The other way to accomplish this is to use the Offset function built into VBA. Take a look at it in Excel VBA help (open the VBE in Excel, press F1 and search for Offset).

[kwaku101]

CreganTur. Thank you sooo much,

I made a slight tweak to what you gave me and it work perfectly.


For x = 2 To 9
Num = Worksheets("text file").Range("A" & x)
Code1 = Worksheets("text file").Range("B" & x)
Code2 = Worksheets("text file").Range("C" & x)