Macro to save as txt

[Olga Goldberg]

Hi, everyone!

I'm new here, and I'm totaly ignorant.

I work with a read-only template, and desperately need a macro that would save it as a workbook, and every worksheet of it as a separate txt file, then close the whole thing. I will be re-using that workbook it just saved again, and I want to have the same macro there as well (that it should save my workbook as workbook and every worksheet as a separate txt file).

Can anyone PLEASE help?

Thank you very much in advance

Olga Goldberg

 

[xenou]

Hi Vog,

I think she doesn't want the hidden sheets to be copied...

AB

 

[VoG]

Thanks Alexander. In that case

Sub ExportSheetsAsText()
Dim ws As Worksheet, wsName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    wsName = ws.Name
    If ws.Visible = xlSheetVisible Then
        ws.Copy
        ActiveWorkbook.SaveAs wsName & ".txt", xlUnicodeText
        ActiveWorkbook.Close savechanges:=False
    End If
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[xenou]

Looks like great minds think alike...we went with the same solution again.

 

[Olga Goldberg]

Dear AB and VoG II,

Thank you so very much, everything works perfectly!
Fileformats I know, got them xlText, csv in one instance only.

THANK YOU SO MUCH!!!!!!!

 

[DBCox]

Hey guys,

Sorry for digging up this old post, but I found it when searching for a solution. I am working on something very similar, except some of my files need to be saved as CSV and some as tab delimited text files. I had a previous version working for only CSV files by definding the "fileformat" as xlCSV rather than "fileformatdef" in the following code:

ActiveWorkbook.SaveAs Filename:=savedfname, fileformat:=fileformatdef, CreateBackup:=False

"savedfname" is obviously definded as the filename with directory that I want to save. I have setup fileformatdef to change from "xlCSV" to "xlText" as necessary to save as the proper file type. However, I am getting an error code when using the variable fileformat. It is: "Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed. If I set it to xlCSV, it works fine for the CSVs and if I set it to xlText, it works fine for the tab delimited files, but not both.

How can I set it up such that I can change the saveas file format?

Thank you in advance for your help!