.CSV file format - comma or semi-colon?

[bavjean]

Hello,

I have an existing file that is in .csv file format. The comma ( , ) is used
to seperate the values. When I open the file in Excel, it works fine and
displays the values seperately in cells.

Now I am creating my own .csv files through VBA, with the SaveAs method for
Worksheet objects.
E.g. I use the following syntax:

objWS.SaveAs Filename:="c:\temp\" + gstrNameVP + ".csv",
FileFormat:=xlCSVWindows

The file saves as .csv BUT the values are seperated by a semi-colon( ; )
which is not what I want. I want comma-seperated. Otherwise when I open the
..csv in Excel, the values do not display correctly in the cells.
I also changed the list seperator character to a comma in my XP's Regional
settings->Number settings.

I cannot understand how the original file was saved using commas for
seperating the values.
When I create a file in Excel, and save it as .csv, it always uses
semi-colon seperators.


Can somebody please tell me how I can seperate the values by commas in the
..csv properly?
Thanks in advance and for paying attention to my post.

Jean

 

[Bernie Deitrick]

Jean,

Try changing your list separator value, which is set by your operating
system.

Use this (though the specific names and tabs, etc. may change with Windows
version - these are for Win XP Prof.)
Start | Settings | Control panel | Regional and Language Options | Regional
Options | Customize.... | Number tab | List Separator - make sure it is a
comma.

HTH,
Bernie
MS Excel MVP

 

[bavjean]

Thanks Bernie, but I stated in my message that I already tried that what you
suggested:

I also changed the list seperator character to a comma in my XP's Regional
settings->Number settings.

However, I found article in the MSKB which sort-of told me: NOT POSSIBLE
it is here if you or anyone's interested:

http://support.microsoft.com/default.aspx?scid=kb;en-us;288839

 

[Bernie Deitrick]

Sorry, I missed that.

In that case, try a macro, example below.

HTH,
Bernie
MS Excel MVP

Sub ExportToCSV()

Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer

FName = "C:\CSVTest.csv"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If WholeLine = "" Then
WholeLine = Cells(RowNdx, ColNdx).Text
Else
WholeLine = WholeLine & "," & Cells(RowNdx, ColNdx).Text
End If
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

 

[JE McGimpsey]

take a look here:


http://www.mcgimpsey.com/excel/textfiles.html

 

[bavjean]

Thanks for the help guys, I will give it a try!

take a look here:


http://www.mcgimpsey.com/excel/textfiles.html