VBA Code to open text file

Question

I am trying to write code in VBA that will copy a range of cells. This I have done successfully but got stuck on the next step. This is the first part:

Range("L1", Range("L1").End(xlDown)).Select 
Selection.Copy

Next, I would like the macro to open a new text file (.txt) with the specified name from the excel tab and insert the copied cells.

How do I open a text file?

Thank you

Answer 1

With your column L data in the first tab, try:

Sub dural()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Add

    wb1.Activate
    Sheets(1).Select
    tabname = ActiveSheet.Name

    Range("L1", Range("L1").End(xlDown)).Copy

    wb2.Activate
    ActiveSheet.Paste
    wb2.SaveAs Filename:=tabname & ".txt", FileFormat:=xlTextWindows
    wb2.Saved = True
    wb2.Close
End Sub

Answer 2

Here is one of many possible references (via Google): http://www.homeandlearn.org/write_to_a_text_file.html

Here is the example they give - you're better reading the article and then adapting to your scenario:

Open FilePath For Output As #2
For i = 1 To LastRow 
  For j = 1 To LastCol  
    If j = LastCol Then

      CellData = CellData + Trim(ActiveCell(i, j).Value)

    Else

      CellData = CellData + Trim(ActiveCell(i, j).Value) + ","

    End If

  Next j    

Write #2, CellData
CellData = ""
Next i