MrExcel Publishing
Your One Stop for Excel Tips & Solutions

CSV Format and list separator

Posted by Jose Antonio Gonzalez on March 16, 2001 1:34 AM

Hello all!

I want to save an Excel sheet in CSV format (list separator should be ; due to european number format used and although regional settings are set to English (United States) the list separator is changeg from , to ; ).

When doing manually I get this in the CSV file (account number; amount with two decimals;debit o credit; concept):

700000300;676961,00;H;Ventas dia 05-March-2001

But when recording and playing the macro this is what I get:

700000300,"676961,00",H,Ventas dia 05-March-2001

The procedure I do is select the range, copy, open new book, paste special value and save as CSV.

This is the code from the macro:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ChDir "C:\tmp"
ActiveWorkbook.SaveAs FileName:=Nombre, FileFormat:=xlCSV, _

I do not understand why doing manually I get what I want but not when playing the macro (the list separator is changed to , intead of ; and therefore " is used for separating the number.

Any idea?

Jose Antonio Gonzalez
Hotel Maria Cristina RES 086 - FRS 347 - SAP F220

Posted by Mark W. on March 20, 2001 4:14 PM

C(omma) S(eparated) V(alues)

Quoted values such as "676961,00" prevent Excel
from interpeting the comma as the start of a new
column. Excel says, "Oh, I see a quotation
mark...okay, same column until I see the closing