Can't get csv with semicolon delimiter when use a macro for that action

[Marino_CRO]

Hi,
Please help me !
Few years ago there was some discussion on this forum (http://www.mrexcel.com/forum/showth...V-semicolon-delimited&highlight=csv+semicolon ) about the problem that I have but I hope that my problem deserves a new thread because it is slightly more specific.
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 semicolons)!?
My macro (main part) is:
Activeworkbook.SaveAs Filename:=..., Fileformat:=xlCSV, Local:=True

I set Windows (7) 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 another Excel forum (http://chandoo.org/forums/topic/i-need-help-with-macro-to-save-excel-file-to-csv-semicolon-delimited ) 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 semicolons.
I also tried to use 'xlCSVMSDOS' and 'xlCSVWINDOWS' but the result is the same, csv with commas and not with semicolons.

However, as I mentioned before, 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 semicolons ?
I read somewhere that this is a some kind of bug in Excel ?
Otherwise, use Excel 2010 and Windows 7.
Thanks in advance for any suggestions !

 

[CVDK]

Hi Marino,
I see you've already done some resarch on the topic so I'm not sure wether this will help. However if your regional delimiter setting in the control panel is ; (semicolon) it should work, and it was incredibly simple compared to all the other suggestions where you'd have to create an array and put in a delimiter etc. It's the Local:=True. I found the solution here in the last post in this thread http://social.msdn.microsoft.com/Forums/da/isvvba/thread/6c0c087e-4d9f-4b8e-844c-5803492542bd[/URL]

Dim MyFile as String
MyFile = Mystring
ActiveWorkbook.SaveAs Filename:= _
   MyFile, FileFormat:=  xlCSV, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
   , CreateBackup:=False, Local:=True

 

[Marino_CRO]

Hi CVDK,
Thank you for your suggestion, but it didn't solve my problem.
I tried that solution even before you posted it and it didn't work.
I guess that this is some kind of bug in Excel. I've asked many Excel experts and no one could find any solution for my problem

 

[Firefly2012]

I'm using Windows XP and xl2007 and changing the regional Settings works as expected for me (although I note I am using a different version and OS to you).

Try swapping to the Worksheet SaveAs method with:

Activesheet.SaveAs Filename:=..., Fileformat:=xlCSV, Local:=True

Alternatively, a workaround would be to write to a text file (using code) and explicitly write the semi-colons out - I can help you with code to do this should you wish.

 

[Marino_CRO]

I tried with yours suggestion, but it still doesn't work properly.
Thank you anyway!
There is no need to write some special code, because I've been doing this pocedure rearly, so I can do it manually.