Copy Excel to Notepad ANSI VBA

[austinandrei]

Hi,
Is there a way to specifically copy a range in excel say "A1:A20" (limited only to Column A) then paste to NotePad/Textfile and should be on ANSI format and save to a specific folder like "C:\Users\XXXXXX\Documents\TextFile Folder".
Thanks!

 

[Logit]

.

Option Explicit


Sub textSave()
Dim ws As Worksheet, wsName As String
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        wsName = ws.Name
        If ws.Name = "Sheet1" Then
            ws.Copy
            ActiveWorkbook.SaveAs Filename:="C:\Users\XXXXXX\Documents\TextFile Folder" & "\" & wsName & ".txt", FileFormat:=xlTextMSDOS, CreateBackup:=False
            ActiveWorkbook.Close False
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

I'm not certain about the path for saving but the macro works if the path is changed to save in the same folder as the workbook.
(i.e., ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & wsName & ".txt", FileFormat:=xlTextMSDOS, CreateBackup:=False)

 

[austinandrei]

Hi Logit,thanks for the quick response. Is this limited on the range in Column A only? Because I still have a lot of data in the other columns that is why I am copying only the data in Column A. Thanks!

 

[Logit]

.
My apologies .. I was concentrating too hard on the path portion of the macro ...

Try this one :

Option Explicit


Sub textSave()
Dim ws As Worksheet, wsName As String
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        wsName = ws.Name
        If ws.Name = "Sheet1" Then
            ws.Range("A1:A20").Copy
           ActiveWorkbook.SaveAs Filename:="C:\Users\XXXXXX\Documents\TextFile Folder" & "\" & wsName & ".txt", FileFormat:=xlTextMSDOS, CreateBackup:=False            
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[austinandrei]

Hi Logit,
Still not working.
My whole workbook keeps saving in text format and that whole data in the worksheet is being transferred to notepad.

 

[austinandrei]

Hi,
I made this one works by just creating a copy of the sheet and deleting the data not to be included but for unknown reasons, its putting a "" on the copied items which should not be. What I mean is it is enclosing the items in quotation marks.

 

[Logit]

.

Option Explicit

Sub textSave2()
Dim c As Range
Dim r As Range
Dim output As String


 For Each r In Worksheets("Sheet1").Range("A1:A20").Rows
     For Each c In r.Cells
         output = output & vbNewLine & c.Value
     Next c
 Next r


Open "C:\Users\XXXXXX\Documents\TextFile Folder" & "\" & "Sheet1" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 

Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , output


Close


End Sub

This one saves only the specified range to the desktop. At least it does here. I hope the path is correct for you.

Let me know how it goes.