excelvbaexport

VBA Print #1 select range (for exporting from Excel to a file)?


I have a simple macro that exports desired cells to a txt-file. Can I instead of individually telling the macro what cells I want to export, choose a range of cells?

Here's the very simple code I have:

Sub TEST()
    Open "C:\text.txt" For Output As #1
        Print #1, Cells(1, 1) & Cells(1, 2)
        Close
End Sub

With that I can export Excel sheet cells A1, A2. But if I want to export wide range of cells, this method isn't too convenient. So is there a way I could, let's say easily export cells A1:A100?

Thanks!


Solution

  • I see joseph4tw has already responded, but my answer is a little different and might give you another idea, so I'll put it here anyway.

    Sub TEST()
        Dim c As Range, r As Range
        Dim output As String
        For Each r In Range("A1:C3").Rows
            For Each c In r.Cells
                output = output & c.Value & ","
            Next c
            output = output & vbNewLine
        Next r
        Open "H:\My Documents\text.txt" For Output As #1
        Print #1, output
        Close
    End Sub