I can't save excel as csv file with semicolumns when I use macro for that action

Hi,
Please help me !
I am trying to save excel file as csv semicolon delimited file. While I was doing it manualy (File->Saveas->CSV(commaDelimited)) it worked fine, but with macro it gives me file with commas (without semicolumns)!?
My macro (main part) is:
Activeworkbook.SaveAs Filename:=..., Fileformat:=xlCSV, Local:=True
I set Windows Regional Settings List Separator to ";" (as it was suggested in some Excel forums) but it still wont not work properly :(
Then I asked for advice in one excel forum and try what they have suggested to me: (...In Excel 2010, under File-Options-Advanced, deselect “Use System Separators” and enter a “,” for Decimal Separator, and a space for Thousands Separator...and remove this Local:=True) but i still get csv with commas instead of semicolumns.
I also tried to use 'xlCSVMSDOS' and 'xlCSVWINDOWS' but the result is the same, csv with commas and not with semicolumns.
However, as I mentioned before in my post, when I try to do it manually (without macro) it works fine, and when I use macro(which is same as manual action File->SaveAs->CSV(comma delimited)) it gives me different type of separator!?
Only difference is that when I use File->SaveAs->CSV(comma delimited) Excel gives me notification "Filename...may contain features that are not compatible with CSV (comma delimited). Do you want to keep the workbook in this format ?" When I record macro for that action (File->SaveAS...) this notification is not in VBA and I don't know a code for it ? Maybe that's the reason why I can't get csv with semicolumns ?
I read somewhere that this is a some kind of bug in Excel ?
I use Excel 2010.
Thanks in advance for any suggestions !

Vishesh's picture

Semicolon Delimited

In Windows:
1. Go to Start>Settings>Regional And Language Options
2. Click on the Customize button
3. Next to List Separator type in a semi-colon (;)

If you are using Windows 7 then
go to Control Panel -> Clock, Language, and Region -> Region and Language -> click “Additional settings…”, and change the value next to “List separator:”

semicolon delimiter

Hi Vishes,
Thank you for your suggestio.
I have done as you suggested, but my problem still remains :(

Vishesh's picture

This should solve... Sub

This should solve...

Sub SaveSemiColonSeparatedFile()
Const ForReading = 1, ForWriting = 2
Dim fso, MyFile, FileName
Dim AllText As String
Set fso = CreateObject("Scripting.FileSystemObject")

'Save your file as csv and
'provide your own Text file path
FileName = "K:\Delete.txt"

' Open the file for input.
Set MyFile = fso.OpenTextFile(FileName, ForReading)

' Read from the file.
If MyFile.AtEndOfStream Then
AllText = ""
Else
AllText = MyFile.ReadAll
End If

' Open the file for output.
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True)

' Write to the file.
MyFile.Write Replace(AllText, vbTab, ";")

MyFile.Close

End Sub

Could you check my files ?

Hi Vishesh,
Thank you for your reply !
I tried with your code, but it seems to me that I was doing something wrong because I can't get csv with semicolon.
So I put my files on Skydrive (http://sdrv.ms/MBa63v) and I would appreciate it If you could download it and see what's wrong with my code.
First file is named 'Input' and it has yours code with some modification. It contains the data that I would like to save in a CSV semicolon delimited file.
Second file is 'Output_SD' and I made it from 'Input' file via File->SaveAs->CSV(comma delimited) and it has semicolon delimiter. I made this file manualy and I would like to automate this operation with some VBA code.

Vishesh's picture

Further modified code...this

Further modified code...this code first creates a txt file and renames it to csv.

Sub SaveSemiColonSeparatedFile()
Const ForReading = 1, ForWriting = 2
Dim fso, MyFile, FileName
Dim AllText As String
Set fso = CreateObject("Scripting.FileSystemObject")

'Save your file as csv and
'provide your own Text file path
FileName = Application.DefaultFilePath & "\" & "Output"
Sheets(1).Copy
ActiveWorkbook.SaveAs FileName, xlText
ActiveWorkbook.Close True

' Open the file for input.
Set MyFile = fso.OpenTextFile(FileName & ".txt", ForReading)

' Read from the file.
If MyFile.AtEndOfStream Then
AllText = ""
Else
AllText = MyFile.ReadAll
End If

' Open the file for output.
Set MyFile = fso.OpenTextFile(FileName & ".txt", ForWriting, True)

' Write to the file.
MyFile.Write Replace(AllText, vbTab, ";")

MyFile.Close

'Remove this line if you don't want to convert txt to csv
Name FileName & ".txt" As FileName & ".csv"

End Sub

File already exists (Error 58)

I get an error for this part of code:
Name FileName & ".txt" As FileName & ".csv"

Vishesh's picture

Try deleting the file

Try deleting the file first...
or put
Kill FileName & ".csv"
before the line which is giving error