Macro for saving Excel column in Notepad

Question

My range in Excel is as follows:

Range("S15").Select Range(Selection, Selection.End(xlDown)).Select, and I have to save this range automatically in Notepad with a macro.

I have already this code:

Option Explicit

Sub Step_6_test()

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

CellData = ""
FilePath = Application.DefaultFilePath & "\auth.csv"

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

Close #2
MsgBox ("Done")

End Sub

But this code is for saving it in a Excel text file, but I want to save my range in Notepad automatically. How can I Change the code for saving my range in notepad?

You *are* saving the data as a simple text file (that is what a csv file is). If you want to have it with a .txt extension -- just do it. It is your code that is telling it to use .csv. Just change that to .txt. — John Coleman, Dec 29 '16 at 10:55

score 1 · Answer 1 · answered Dec 29 '16 at 11:31

Following-up on @Limak comment, Just save the file as .txt.

Also, you have CellData = CellData + Trim(ActiveCell(i, j).Value), but you are not advancing the ActiveCell in your loop (nore do you need to use ActiveCell). Instead you could use CellData = CellData + Trim(Cells(i, j).Value).

Full Code

Option Explicit

Sub Step_6_test()

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
Dim i As Long, j As Long

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

CellData = ""
FilePath = Application.DefaultFilePath & "\auth.txt"

Open FilePath For Output As #2

For i = 1 To LastRow

    For j = 1 To LastCol
        If j = LastCol Then
            CellData = CellData + Trim(Cells(i, j).Value)
        Else
            CellData = CellData + Trim(Cells(i, j).Value) + ","
        End If
    Next j

    Write #2, CellData
    CellData = ""
Next i

Close #2
MsgBox ("Done")

End Sub

@Hakan Yilmaz to further explain what Shai was saying about not needing to use `ActiveCell` take a look at [this SO question and its answers](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — PartyHatPanda, Dec 29 '16 at 15:50

Macro for saving Excel column in Notepad

1 Answers1