• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

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
0
ZoltanSz
Asked:
ZoltanSz
1 Solution
 
pauloaguiaCommented:
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
0
 
pauloaguiaCommented:
Rereading your question I got confused... How wre you building the CSV file in the macro? Using SaveAs or building the file "manually"?
0
 
ZoltanSzAuthor Commented:
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 )";".
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
pauloaguiaCommented:
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
0
 
geofflilleyCommented:
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
0
 
SpazMODicCommented:
PAQed - no points refunded (of 50)

SpazMODic
EE Moderator
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now