need a save as text macro with delimiter

[yoitsmejy]

i tried using save as fileformat=xltext, but it is giving an error, Currently i am using xltextwindows. It works perfectly except that there are spaces between the columns, I would like it to be separated by commas (commas delimited). Please help. Below is the code i am using.


Sub SaveAllSheets2CSV()
Dim wsSheet As Worksheet

Application.DisplayAlerts = False
With ActiveWorkbook
For Each wsSheet In .Worksheets
wsSheet.SaveAs Filename:="C:\text" & "\" & wsSheet.Name, FileFormat:=xlTextWindows, AddtoMRU:=False
Next wsSheet
End With
Application.DisplayAlerts = True
End Sub

[BrianMH]

try xlCSV

[yoitsmejy]

i need to import the files into a program and it only takes in text file :(

[BrianMH]

Csv is text

[yoitsmejy]

yea but the program doesnt read csv files. got any suggestions?

[yoitsmejy]

This is my current code, but when it runs, it is not separating the data by commas.

Sub SaveAllSheets2TXT()
Const DELIMITER As String = ","
Dim wsSheet As Worksheet
Dim sOut As String

Application.DisplayAlerts = False
With ActiveWorkbook
For Each wsSheet In .Worksheets
wsSheet.SaveAs Filename:="C:\text" & "\" & wsSheet.Name, FileFormat:=xlTextWindows, AddtoMRU:=False
sOut = sOut & DELIMITER & wsSheet.Txt
Next wsSheet
sOut = Empty
End With
Application.DisplayAlerts = True
End Sub

[Kenneth Hobs]

You could just save it as a CSV file and then rename it. CSV files are text files. It is a common standard.

I don't know what the value of sOut is in your example code.

Seems like there was a recent thread about this topic.

[GTO]

Hi all,

I was thinking along the same lines as Kenneth, but gave something a quick shot. It seems we can alter the extension during the save.


Option Explicit

Sub exa5()
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("Sheet3")
wks.Copy
Set wks = ActiveWorkbook.ActiveSheet

wks.Parent.SaveAs Filename:=ThisWorkbook.Path & "\Test.txt", _
FileFormat:=xlCSV, _
CreateBackup:=False
wks.Parent.Close False
End Sub

[yoitsmejy]

Hey GTO, Thanks for the code, I tried your coding, it works great, except I need this macro to run 700+ sheets. I would prefer not having to write all of them up one by one. I was hoping to include something like "\" & wsSheet.Name to it. Is there anyway to update your coding.

[BrianMH]

Sub SaveAllSheets2TXT()

Dim wsSheet As Worksheet


Application.DisplayAlerts = False
With ActiveWorkbook
For Each wsSheet In .Worksheets
wsSheet.SaveAs Filename:="C:\text\" & wsSheet.Name & ".txt", FileFormat:=xlCSV
Next wsSheet

End With
Application.DisplayAlerts = True
End Sub


This should work

[yoitsmejy]

thanks brian that works perfectly, When i close excel, it asked if i want to save it, is there any code to just so that it will not show up?

[BrianMH]

Not that I can think of other than saving it at the end of the macro. Which would of course save any changes to the workbook. If your happy with this just add the below to the end.
thisworkbook.save

[yoitsmejy]

how about the opposite, discard any changes made.