?
Solved

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

Posted on 2003-03-06
7
Medium Priority
?
471 Views
Last Modified: 2007-12-19
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
Comment
Question by:ZoltanSz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8083177
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8083218
Rereading your question I got confused... How wre you building the CSV file in the macro? Using SaveAs or building the file "manually"?
0
 

Author Comment

by:ZoltanSz
ID: 8086612
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Expert Comment

by:pauloaguia
ID: 8090909
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
 
LVL 1

Expert Comment

by:geofflilley
ID: 10032458
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
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 10315380
PAQed - no points refunded (of 50)

SpazMODic
EE Moderator
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question