asked on

VB macro save xls to csv with wrong separator. How could i set it in the macro?

I wrote a small Visual Basic macro, wich saves xls files in csv format. The problem is, that the macro use ,(comma) as separator, but in the Windows Regional Settings is ;., so if i do the same manualy the separator in the file is ;(semicolon). How could i set it in the macro?

Thanks
Zoltan

pauloaguia

You can retrieve this separator by using

Application.International(xlListSeparator).

However the International collection is ReadOnly and therefore cannot be directly changed through code. And I found no other way to do that.

It seems the only way is to actually change it in the regional settings.

Paulo

pauloaguia

Rereading your question I got confused... How wre you building the CSV file in the macro? Using SaveAs or building the file "manually"?

ZoltanSz

ASKER

In the regional settings the list separator is ";" (semicolon). When i open an xls file and call SaveAs manually it works properly, the file contains semicolons as separator. I have to convert more file in the same way, so i recorded (and changed) a small macro:

Sub Save_as_CSV()
Dim l_name As String
l_name = "C:\conv\" + Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".")) + "csv"

ActiveWorkbook.SaveAs FileName:=l_name, FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close

End Sub

This macro saves the csv files with "," (comma), however the regional settings was ( unmodified )";".

pauloaguia

I sure would like to know how you did it because when trying your code (in XL2K) I still got the ; as the separator...

Could there be any code running, on startup for instance, that somehow changes the regional settings for that document only?

Paulo

geofflilley

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Delete question, refund points
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Geoff Lilley
EE Cleanup Volunteer

ASKER CERTIFIED SOLUTION

SpazMODic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial