vba save file as txt file

[JMARIE5]

Hello,

I am new here and still learning VBA. I am struggling with my code.
I have multiple sheets that I need to save as a txt file in the same workbook. However, the code is only working when the sheet is selected. Can someone help me out with my code?

Sub DOTtxt()


Dim wb As ThisWorkbook
Set wb = ThisWorkbook
Dim j As Long

Dim ws As Worksheet
Set ws = wb.Sheets("DOT")

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject

Dim my_textfile As Scripting.TextStream
Set my_textfile = fso.CreateTextFile("file path to my folder.txt")

Dim rownum As Long, var As Variant
rownum = 2

For j = 2 To Range("a1000000").End(xlUp).Row


var = Cells(rownum, 1).Value

'going to write in the text file create above
my_textfile.WriteLine var

rownum = rownum + 1


Next

my_textfile.Close


End Sub

 

[BSALV]

you could copy that range to an empty sheet and then copy that sheet to a new workbook and save that new workbook as txt or csv with this macro

sub test
PathName = "C:\test.csv"
'Assuming you want to export your current Worksheet
ActiveSheet.Move 'Move the Worksheet to a new Workbook
ActiveWorkbook.SaveAs Filename:=PathName, FileFormat:=xlCSV, CreateBackup:=False
end sub

 

[Jon Peltier]

Keep it simple. This one-liner saves the active sheet as a tab-delimited text file.

ActiveWorkbook.SaveAs Filename:="C:\path\filename.txt", FileFormat:=xlTextMSDOS