View Full Version : need a save as text macro with delimiter
yoitsmejy
04-07-2011, 12:02 PM
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
04-07-2011, 12:22 PM
try xlCSV
yoitsmejy
04-07-2011, 12:45 PM
i need to import the files into a program and it only takes in text file :(
BrianMH
04-07-2011, 12:47 PM
Csv is text
yoitsmejy
04-07-2011, 12:48 PM
yea but the program doesnt read csv files. got any suggestions?
yoitsmejy
04-08-2011, 07:59 AM
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
04-08-2011, 09:03 AM
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.
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
04-11-2011, 11:57 AM
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
04-11-2011, 12:30 PM
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
04-12-2011, 09:19 AM
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
04-12-2011, 09:24 AM
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
04-12-2011, 12:08 PM
how about the opposite, discard any changes made.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.