CODEWITHSUNDEEP
excelvbaexport
user2084296
user2084296

Reputation: 23

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!

Upvotes: 2

Views: 32903

Answers (4)

mkingston
mkingston

Reputation: 2718

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

Upvotes: 3

billmanH
billmanH

Reputation: 1426

I notice that the example by mkingston puts the comma first. This would cause everything to start in the second column of the .csv (first column would be blank). A little better would be to put the comma at the end of the statement. You could remove the last ',' with REPLACE() but it isn't necessary. 

   Dim c As range, r As range
        Dim output As String
        For Each r In range(DataRange).Rows
            For Each c In r.Cells
                output = output & c.Value & ","
            Next c
            output = output & vbNewLine

        Next r

Upvotes: 1

runlevel0
runlevel0

Reputation: 2963

If you want to select all non-empty cells (contigouos) use this: 

Range("A1").CurrentRegion.Select

You can then export it in whatever way you fancy ;)

Upvotes: 0

Joseph
Joseph

Reputation: 5160

You can use the InputBox and set the type to 8, which will allow the user to click in Excel and select a range.

Here is an example:

Sub test()
    Dim r As Excel.Range, cell As Excel.Range
    On Error Resume Next
    Set r = Application.InputBox("Select Range", "Select Range", Type:=8)
    On Error GoTo 0
    If r Is Nothing Then Exit Sub

    Open "C:\text.txt" For Output As #1
    For Each cell In r
        Print #1, cell.Value
    Next
    Close
End Sub

Upvotes: 1

Related Questions