VBA Excel - Saving .csv with semicolon as separator

[tvbolt]

Hi everyone,

I came across a problem with the task currently at hand:

I need to save a file as .csv with a semicolon as separator. My Windows regional settings also have a semicolon as list separator. When I manually save the file as csv (or even manually save a macro generated .csv before closing it) it works totally fine, I get a nice file that has a semicolon as separator and looks like a normal excel table.

Nevertheless, when I save and close the file with VBA code, and then reopen it I get a very curious picture: the data is separated by commas.

Below is the piece of code that I use regarding the workbook I need to save as .csv. Any tips on how to fix this?

Thanks a lot for your help!

Set csv = Workbooks.Add(xlWBATWorksheet)
csv.SaveAs Filename:=path & "\template.csv", FileFormat:=xlCSV
'some code to fill the .csv file with data
csv.Save
csv.Close

 

[Joe4]

Welcome to the Board!

When I manually save the file as csv (or even manually save a macro generated .csv before closing it) it works totally fine,

If it works when you do it manually, try turning on the Macro Recorder and record yourself performing those steps. Then stop the recorder and view the VBA code that creates. Compare that to the code you have, and see if it is just a matter of making a few simple changes.

Nevertheless, when I save and close the file with VBA code, and then reopen it I get a very curious picture: the data is separated by commas.

What program are you using to view the CSV file?
If you REALLY want to see what is contained in the CSV file, DON'T use Excel to view it! Excel does automatic conversions on data as it sees fit. Use a Text Editor, like NotePad or WaordPad to view the CSV file to see what it really looks like.

 

[FormR]

Hi, welcome to the forum!

You could try this addition to your saveas line;

csv.SaveAs Filename:=Path & "\template.csv", FileFormat:=xlCSV, Local:=True

 

[jim may]

Check out:

https://superuser.com/questions/476...adsheet-as-a-semi-colon-separated-values-file

 

[tvbolt]

Thanks everyone for the welcome!

What program are you using to view the CSV file?
If you REALLY want to see what is contained in the CSV file, DON'T use Excel to view it! Excel does automatic conversions on data as it sees fit. Use a Text Editor, like NotePad or WaordPad to view the CSV file to see what it really looks like.

Thanks a lot for your reply, but I was actually opening it with Notepad, and still commas were there. Recording a macro didn't help either. The code is completely the same.

Hi, welcome to the forum!

You could try this addition to your saveas line;

csv.SaveAs Filename:=Path & "\template.csv", FileFormat:=xlCSV, Local:=True

Thanks, I totally forgot about Local parameter. For a minute there I actually thought it was the solution, but unfortunately it didn't work either.

 

[Joe4]

Did you take a look at the options presented in the link Jim posted?

 

[tvbolt]

Did you take a look at the options presented in the link Jim posted?

Yeah, I have. By the way, thanks, Jim.

The only acceptable option for me there is actually the VBA code which goes row by row and concatenates all cells in a row with a semicolon between them.

I already had something similar in my code for the users who have a comma as separator in Windows regional settings. Currently I have removed the 'comma' condition and had the code running regardless of global region settings. So, my VBA code works fine, but I would still love to know why it was working this way. I'm kind of confused with this and would really appreciate it if someone could explain what was happening there and why.