How to create a text file using excel VBA without having double quotation marks?

Question

I am having a problem with converting columns from a certain column to text file. I found a code where it uses selection of cells and it works but I am trying to accomplish this using a range of cells from a certain sheet from a certain workbook rather than selecting cells. Anyway I can accomplish this?

I tried using a range selection but it does not work. it gives me object error. I think I have to define the name of the active workbook and sheet but I am not sure how to do that.

Dim r As Range, c As Range
Dim sTemp As String

Open ThisWorkbook.Path & "\2019.txt" For Output As #1
Range2 = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Copy
For Each r In Range2
    sTemp = ""
    For Each c In r.Cells
        sTemp = sTemp & c.Text & Chr(9)
    Next c

    'Get rid of trailing tabs
    While Right(sTemp, 1) = Chr(9)
        sTemp = Left(sTemp, Len(sTemp) - 1)
    Wend
    Print #1, sTemp
Next r
Close #1
End Sub

My expected result is to have no quotation marks when converting to txt file, and that is working. I am having trouble pulling from certain worksheets and certain columns.

HackSlash HackSlash · Accepted Answer · 2019-08-27T18:05:37

Declare your variables near their usage so you can keep track of them. Use "Option Explicit" to force you to declare variables. This will prevent you from making simple mistakes. You can't assign a range object to the ".copy" method because a method doesn't return a value or object. It fills the clipboard, which you don't want. Use an error handler to ensure that your file gets closed properly.

I'm not sure if this code will do exactly what you want but it will at least compile. Good luck.

Option Explicit

Public Sub Example()
    On Error GoTo cleanup
    Open ThisWorkbook.Path & "\2019.txt" For Output As #1

    Dim Range2 As Range
    Range2 = Range("A2", Cells(Rows.Count, "A").End(xlUp))

    Dim r As Range
    For Each r In Range2
        Dim sTemp As String
        sTemp = ""
        Dim c As Range
        For Each c In r.Cells
            sTemp = sTemp & c.Text & Chr(9)
        Next c

        'Get rid of trailing tabs
        While Right(sTemp, 1) = Chr(9)
            sTemp = Left(sTemp, Len(sTemp) - 1)
        Wend

        Print #1, sTemp
    Next r

cleanup:
    Close #1
End Sub

How to create a text file using excel VBA without having double quotation marks?

1 Answers