Hello YehWei,

Here is the macro to copy and convert the DAT file into TXT file (CSV).
Add a VBA module to your project and place this code in it. You can then
run it by selecting "*CsvToTxt*" from the Macro List (press ALT and F8
to display the list).

______________________________

Sub CsvToTxt()

Dim Data As String
Dim Ext As String
Dim FF1 As Integer
Dim FF2 As Integer
Dim FileFilters As String
Dim File_Name
Dim FileName1 As String
Dim FileName2 As String
Dim I As Long
Dim Msg As String

'Display the Open File dialog
FileFilters = "Data Files (*.dat),*.dat,Text Files (*.txt),*.txt,All
Files (*.*),*.*"
File_Name = Application.GetOpenFilename(FileFilters)

'Get the file's extension
I = InStr(1, File_Name, ".")
If I 0 Then
Ext = Right(File_Name, Len(File_Name) - I + 1)
End If

'Check extension is .CSV
If Ext < ".csv" Then
MsgBox "Error - Source file must have .csv extension",
vbCritical + vbOKOnly
Exit Sub
End If

'Set source and destination file names
FileName1 = File_Name
FileName2 = Left(File_Name, I) & "txt"

On Error GoTo FileError

'Replace semicolons with commas
FF1 = FreeFile
Open FileName1 For Input As #FF1
FF2 = FreeFile
Open FileName2 For Output As #FF2
Do While Not EOF(FF1)
Line Input #FF1, Data
Data = Replace(Data, ";", ",")
Write #FF2, Data
Loop
Close #FF2
Close #FF1

Exit Sub

FileError:
Msg = "The following error has occurred" & vbCrLf _
& " Error #" & Err.Number & vbCrLf _
& " " & Err.Description
MsgBox Msg, vbCritical + vbOKOnly

End Sub
______________________________

To prevent Excel from converting the numbers, format the worksheets as
TEXT.

To remove the both leading and trailing spaces from a string, use
TRIM(" Harry Lee "). The result is "Harry Lee".

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=488952