Save Excel VBA as tab delimited text file without quotes
kakisami14 wrote:
Today I use this code to save my sheet as a tab delimited text file:
ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filnamn,
FileFormat:=xlText
It works for 90% of the data but some cell values get surrounded with
quote marks? Why is this and how can I fix it?
That generally happens when the cell contains a comma. Excel does that
automagically, no workaround possible that I'm aware of. Instead, after
saving the file, read it into a string and remove the quotes in code:
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & filnamn, _
FileFormat:=xlText
Open ActiveWorkbook.Path & "\" & filnamn For Binary As 1
contents = Space$(LOF(1))
Get #1, 1, contents
Close 1
'note that this removes *all* quotes;
'if you want to keep any you'll need to deal with that yourself
contents = Replace(contents, """", "")
Open ActiveWorkbook.Path & "\" & filnamn For Output As 2
Print #2, contents;
Close 2
--
If they were servants by nature,
you wouldn't have to beat them to get them to obey.
|