CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010
Tony Bergeson
Tony Bergeson

Reputation: 99

How to save as .txt in vba

I am looking to have my Macro save a new sheet that i created as a .txt file. this is the code i have so far. 

Sub Move()  
'  
' Move Macro  
'  
' Keyboard Shortcut: Ctrl+m  
'  
Sheets("Sheet1").Select  
Range("A1").Select  
Range(Selection, Selection.End(xlToRight)).Select  
Range(Selection, Selection.End(xlDown)).Select  
Selection.Copy  
Workbooks.Add  
ActiveSheet.Paste  

ActiveWorkbook.SaveAs Filename:="e:" & _
"HDR" + Format(Now(), "YYYYMMDDhhmmss") & ".txt"

End Sub

That includes my macro. I am having trouble with the last part where it saves as a .txt file.
I am currently getting a bunch of crap on my .txt file, here is an example,
"PK ! !}ñU{ Š [Content_Types].xml ¢(  ÌTÝNÂ0¾7ñ–Þš­€‰1†Á…⥒ˆPÚ3¶ÐµMOÁñöž•Ÿ¨".
Any help would be great.

Upvotes: 9

Views: 77669

Answers (3)

cyberponk
cyberponk

Reputation: 1766

The ActiveWorkbook.SaveAs method adds double quote to the beginning and end of every line in the file.

This method parses each line from a given range and transforms it into a CSV file:

Sub SaveSheetToCSVorTXT()
    Dim xFileName As Variant
    Dim rng As Range
    Dim DelimChar As String

    DelimChar = "," 'The delimitation character to be used in the saved file. This will be used to separate cells in the same row

    xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "CSV File (*.csv), *.csv, Text File (*.txt), *.txt")
    If xFileName = False Then Exit Sub

    If Dir(xFileName) <> "" Then
        If MsgBox("File '" & xFileName & "' already existe.  Overwrite?", vbYesNo + vbExclamation) <> vbYes Then Exit Sub
        Kill xFileName
    End If

    Open xFileName For Output As #1
    'Save range contents. Examples of ranges:
    'Set rng = Activesheet.Range("A1:D4")   'A rectangle between 2 cells
    'Set rng = Activesheet.columns(1)       'An entire column
    Set rng = ActiveSheet.Range("B14").CurrentRegion   'The "region" from a cell. This is the same as pressing CTRL+T on the selected cell

    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            lineText = IIf(j = 1, "", lineText & DelimChar) & rng.Cells(i, j)
        Next j
        Print #1, lineText
    Next i
    Close #1

    MsgBox "File saved!"
End Sub

Upvotes: 2

Degustaf
Degustaf

Reputation: 2670

Manually changing the extension of the file name does not actually change the file type. The SaveAs method takes a file type argument. The code you want is 

ActiveWorkbook.SaveAs Filename:="e:" & "HDR" + Format(Now(), "YYYYMMDDhhmmss") _
& ".txt", FileFormat:= xlTextWindows

Doing a search from within Excel help for XlFileFormat will get you (almost) the full list of possible file formats, including 6 text formats, and 4 CSV formats.

Upvotes: 9

AJY
AJY

Reputation: 188

Adding txt to the name does not automatically encode the word document into plain text format.

Instead attempt 

ActiveWorkbook.SaveAs Filename:="e:" & _
"HDR" + Format(Now(), "YYYYMMDDhhmmss") & ".txt", FileFormat:=wdFormatText, Encoding:=1252

Upvotes: 2

Related Questions