Quotes and CSV files
When saving a CSV file and a cell contains quotes, Excel adds more quotes -
I guess this is the CSV standard. For example, if cells A1:A3 are "test" "" Test then Excel will save as ""test"","""",Test I want to save like this "test","",Test Anyone? |
Quotes and CSV files
Tr this macro
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 OutputLine = OutputLine & "," tswrite.writeline OutputLine Next RowCount tswrite.Close End Sub "PM" wrote: When saving a CSV file and a cell contains quotes, Excel adds more quotes - I guess this is the CSV standard. For example, if cells A1:A3 are "test" "" Test then Excel will save as ""test"","""",Test I want to save like this "test","",Test Anyone? |
Quotes and CSV files
Joel wrote:
Tr this macro 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 OutputLine = OutputLine & "," tswrite.writeline OutputLine Next RowCount tswrite.Close End Sub Thanks Joel, I will give that a go. |
All times are GMT +1. The time now is 05:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com