VBA Save Worksheet # as a *.txt

[Pinaceous]

Good Day Everyone!

I'd like to create a sub where it can save the entire worksheet contents as a *.txt file.

I do have a sub now that seems to work but it is a work in progress.

Does anyone have any codes?

Many thanks,
Pinaceous

Sub saveText()

    ActiveWorkbook.SaveAs Filename:= _
        ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", FileFormat:=xlText, _
        CreateBackup:=False
      
    
End Sub

PS. This code does not work because it changes the worksheet name to

textfile-" & Format(Now, "ddmmyy-hhmmss")

and I cannot seem to prevent this!

 

[Logit]

The following writes highlighted Excel data to Notepad :

Sub Write2Text() Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer myFile = Application.DefaultFilePath & "\Sales.txt" Set rng = Selection Open myFile For Output As #1 For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count cellValue = rng.Cells(i, j).Value If j = rng.Columns.Count Then Write #1, cellValue Else Write #1, cellValue, End If Next j Next i Close #1 End Sub

 

[JEC]

Or try this line

ThisWorkbook.SaveAs ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", 20

 

[Pinaceous]

Or try this line

ThisWorkbook.SaveAs ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", 20

Hey JEC,

In experimenting with your code, I really appreciate that the code was able to capture all of the Worksheets data.

However, I do notice that the sub changes the Worksheets name to

textfile-" & Format(Now, "ddmmyy-hhmmss"

and it also changes the Workbooks name.

Do you know how to prevent your sub from changing both the Worksheet and the Workbook name to

textfile-" & Format(Now, "ddmmyy-hhmmss"

??

Please let me know and thanks again for the code!

R/
pinaceous

 

[Pinaceous]

Or try this line

ThisWorkbook.SaveAs ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", 20

Hi JEC,

Is there a way to specify a range that the text file captures?

For example, if I want range A1:AY38.

Please let me know.

And thank you!
pinaceous