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

Marino_CRO

New Member
Joined
Jun 17, 2012
Messages
3
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 ?:confused:
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 !
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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]
Code:
Dim MyFile as String
MyFile = Mystring
ActiveWorkbook.SaveAs Filename:= _
   MyFile, FileFormat:=  xlCSV, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
   , CreateBackup:=False, Local:=True
 
Upvote 0
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 :(
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,852
Messages
6,104,367
Members
447,902
Latest member
chriswebs23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top