Hello,

I try to export a selection of data from excel to a specified spool in format .txt , but unfortunately, my file appears with "," and not";" as requested (even after having modified my file in .CSV) and my data finish by ,,,,,,,,,,,
So I can not use it in an other software.

If somebody meet the same problem.



Partial Code :

ActiveWorkbook.SaveAs Filename:="K:/NAME", _
FileFormat:=xlCSV, CreateBackup:=True, Local:=True

ActiveWorkbook.SaveCopyAs Filename:="K:/NAME.txt"


ActiveWorkbook.Sheets.Select
Selection.Replace What:=",", Replacement:=";", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False




I have :


30/09/09,Client1,40148,100.0,100.0,name1,,,name4,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,




,,,,,,,,,,,,,,,,





Thanks for your help.

Julien

Hi Julien

Excel by default saves the file as comma separated; this is because of the regional settings (Comma is the list separator). You can try changing the same from Control Panel-->Regional Options-->Numbers-->List Separator to ';'

Since you are replacing comma with ";' in Excel it will replace the 'actual' comma's in Text and not the delimiters. Instead you can try replacing the Comma's with some unusual sequence of characters "$#$#$#" etc and save the Excel as CSV. Now you can open the CSV using Open command / FileSystemObject and replace

1. Comma's with Semicolon
2. $#$#$# with Comma

You can also try this link (http://www.cpearson.com/excel/ImpText.aspx)

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)