Excel VBA output multiple columns to text file

Question

I'm trying to create an Excel VBA script which will take 2 columns as input and output the values of into a text file.

e.g

A1: 123 B1: avenue

A2:231 B2: another lane

I'm after an output in a txt file that looks something like this:

House number: 123 Street name: avenue

----.

House number: 231 Street name: another lane

----.

Basically what i'm looking for is: "House number: "+A1+ " Street name: "+B2 "------"

I've got this to work with just 1 column however I'm stuck with adding a second.

Sub CreatetestScript()

    If Dir("C:\test.txt") <> "" Then
     Kill ("C:\test.txt")
    End If

    Sheets("test").Select
    Dim acolumn As String

    r = 2
    Do While Cells(r, 1) <> ""
        acolumn = Cells(r, 1).Value
        run (Cells(r, 1))
        r = r + 1
    Loop

End Sub
Sub run(ByVal acolumn As String)

Dim fileName As String
fileName = "C:\test.txt"

Open fileName For Append As #1

    Print #1, ""
    Print #1, "House Number = " + acolumn
    Print #1, "--------------"

Close #1

End Sub

Any help would be appreciated

Jimmy Smith · Accepted Answer · 2014-12-22 16:36:00Z

1

You could go many directions with this, one way would be creating a bcolumn variable and setting it to the next cell's index in your row:

Sub CreatetestScript()

    If Dir("C:\test.txt") <> "" Then
     Kill ("C:\test.txt")
    End If

    Sheets("test").Select
    Dim acolumn As String
    Dim bColumn As String
    r = 2
    Do While Cells(r, 1) <> ""
        acolumn = Cells(r, 1).Value
        bColumn = Cells(r,2).value
        run (Cells(r, 1), Cells(r,2))
        r = r + 1
    Loop

End Sub
Sub run(ByVal acolumn As String, ByVal Bcolumn As String)

Dim fileName As String
fileName = "C:\test.txt"

Open fileName For Append As #1

    Print #1, ""
    Print #1, "House Number = " + acolumn
    Print #1, "--------------"
    Print #1, ","
    Print #1, "Street = " + Bcolumn
    Print #1, "--------------"
Close #1

End Sub

Your output may need formatted differently, but this should get you on track. For a little reusability, You could setup the function to take an array of these strings too.

edited Dec 22, 2014 at 16:36

answered Dec 22, 2014 at 16:28

Jimmy Smith

2,4511 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tuffy G Over a year ago

HI, Thanks for the answer, I get a compile error Argument not optional error message

Jimmy Smith Over a year ago

I wasn't providing the new value to the run function. Please see my change and let me know if we're on the right track here :)

Tuffy G Over a year ago

you're too fast. Just tried that. Thanks for your help

Collectives™ on Stack Overflow

Excel VBA output multiple columns to text file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related