Macro to Save Current Sheet as Tab Delimited

[coolhand333]

Hello,

I've been searching for code that will perform the following:

1. Save the current workbook then,

2. Save the current sheet as a tab delimited file to the desktop with the same name as the current workbook then,

3. Return to .xlsx file format

The file saved to the desktop can be overwritten each time the macro is run.

I've found code in the forum that does part of what I want to do but not exactly and I'm not experienced enough to tweak the existing code. I assume the above is possible, but if not please let me know.

Thanks for the help!

 

[Leith Ross]

Hello coolhand333,

This macro will do what you want.

Sub SaveAsTSV()

  ' Save the Activesheet as a Tab Separated Values text file.

    Dim Filename As String
    Dim Filepath As String
    
        ActiveWorkbook.Save
        
            With ActiveSheet
                Filepath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
                Filename = Filepath & "\" & ActiveWorkbook.Name & ".txt"
                If Dir(Filename) <> "" Then Kill Filename
                .SaveAs Filename:=Filename, FileFormat:=20
            End With
        
End Sub

"Cool Hand Luke" is one of my favorite movies.

Dragline: Nothin'. A handful of nothin'. You stupid mullet head. He beat you with nothin'. Just like today when he kept comin' back at me - with nothin'.
Luke: Yeah, well, sometimes nothin' can be a real cool hand.

 

[coolhand333]

Thanks, Leith. Is there a way to return to the .xlsx file type? As-is I'm left with a .txt file.

In other words, if I open a file called test.xlsx after I run the macro the open file is called test.xlsx.txt. I'd like to return to the original file.

Thanks,

Luke

 

[Leith Ross]

Hello Luke,

A slight change is all that is needed to bring you back.

Sub SaveAsTSV()

    Dim Filename As String
    Dim Filepath As String
    Dim Wkb As Workbook
    
        Set Wkb = ThisWorkbook
        ActiveWorkbook.Save
        
            With ActiveSheet
                Filepath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
                Filename = Filepath & "\" & ActiveWorkbook.Name & ".txt"
                If Dir(Filename) <> "" Then Kill Filename
                .SaveAs Filename:=Filename, FileFormat:=20
            End With
            
        Wkb.Activate
        
End Sub

 

[coolhand333]

Leith,

I tried the new code but it still leaves the open file as .txt instead of .xlsx.

I can use it as-is (it will still save me a lot of time.)

Luke