• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need help with macro to save excel file to csv semicolon delimited

mbcroman

New Member
Hello,

I am trying to save excel file as csv semicolon delimited file. While I was doing it manualy (File->Saveas->CSVcommaDelimited) 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 :(

Does anyone knows what causes this problem and how to solve it ?
 
Mbcroman


Firstly, Welcome to the Chandoo.org forums.


You could also try changing the line to

Activeworkbook.SaveAs Filename:=..., Fileformat:=xlCSVMSDOS, Local:=True

or

Activeworkbook.SaveAs Filename:=..., Fileformat:=xlCSVWINDOWS, Local:=True


These may need or not need to be done in conjunction with the local settings you have already tried


I would also try removing the , Local:=True part
 
You may need to check under your options to see whether you are setup to use Regional Settings or not. If not, change your comma separator.

http://uk.answers.yahoo.com/question/index?qid=20101015231641AANpKAj


Edit: I have a feeling this is also related to the Local:=True issue that Hui pointed out.
 
You may want to also look at the Excel Options menu


"After modifying the code I discovered that there is an Excel option that allows the original functions to work, without substituting commas for decimal points. In Excel 2010, under File-Options-Advanced, deselect “Use System Separators” and enter a “,” for Decimal Separator, and a space for Thousands Separator."


Refer to the just posted: http://newtonexcelbach.wordpress.com/2012/06/14/return-of-excel-evaluating-commas/
 
Hello guys,

Thank you for your advices, but my problem still remains.

I tried what you suggested (...deselect “Use System Separators” and enter a “,” for Decimal Separator, and a space for Thousands Separator...) 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 that action (File->SaveAS...) this notification is not in VBA ?
 
Back
Top