if you want a macro solution I have posted this code many times before. It
works when Excel starts to do crazy things with CSV files like your problem.
Modify the 2nd and 3rd lines as required.
Sub WriteCSV()
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"
Const Delimiter = ","
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount
tswrite.Close
Exit Sub
End Sub
"Khayae" wrote:
The previous programmer created an excel template file(.xlt) in lower version.
If we save this template file as [CSV (Comma delimited)(*.csv)] format in
(Excel 97-2003), the delimited comma will be until the last column that has
the data.
But if we save this template file as [CSV (Comma delimited)(*.csv)] format
in (Excel 2007), the delimited comma will be until the column at the end of
the sheet.
How can I do to be exactly the same as the lower versions (Excel 97-2003)?
Please see the attached files.
http://www.4shared.com/file/32023208/2dc0346d/Data.html
Thanking you in advance!
Khayae