how to export a column as text separated by comma?

  • Thread starter Thread starter dinodeblasio
  • Start date Start date
D

dinodeblasio

Hello I am new in excel 2007,
how i can export a column of one excel file into text objects
separated by comma?
For example if i have:
column1 = a b c d e f

how i can obtain a text like this?

data = a,b,c,d,e,f

Thanks, you can contact me also by email.
Dino.
 
Sub WriteColumn()

Const Folder = "C:\temp\test"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteCol = "A"
'open files
ChDir (Folder)
FName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")

If FName <> False Then
fswrite.CreateTextFile FName
Set fwrite = fswrite.GetFile(FName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
LastRow = Range(WriteCol & Rows.Count).End(xlUp).Row
OutputLine = ""
For RowCount = 1 To LastRow
If OutputLine = "" Then
OutputLine = Range(WriteCol & RowCount)
Else
OutputLine = OutputLine & "," & Range(WriteCol & RowCount)
End If
Next RowCount
tswrite.Close
End If
End Su
 
Dino

Use text to columns, with fixed width, separate each character. Then
save as a csv file. When you open it in a text editor it may have
some trailing commas if all the strings in your column do not have the
same number of characters. In the text editor you should be able to
replace ,, wtih nothing.

Good luck.

Ken
Norfolk, Va
 
Hi
You can do Edit, Find, Replace on the column to find " " (put a space
bar in the Find box) and replace with "," (put a , in the Replace
box).
regards
Paul
 
How did you dimension the variables.

What I used didn't work:

Dim fswrite As Object
Dim WriteCol As String
Dim Fname As Boolean

Dim fwrite As Object
Dim tswrite As Object
Dim lastrow As String
Dim outputline As String
Dim rowcount As Long
 
Back
Top