Solved: OpenText Method and Find Replace Syntax

[mdmackillop]

Try importing with this code (fixing the path of course). If you look at the image below, you will see you can set the import options for individual columns. That should get you round the date conversion issue.

[vba]
Sub Macro1()
Dim MyPath As String
MyPath = "C:\AA\C_H__.txt" '<=== Change to suit
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyPath, _
Destination:=Range("A1"))
.Name = "C_H__"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(1, 1, 2, 2, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Call Cleans
End Sub
Sub Cleans()
Dim Cel As Range
For Each Cel In ActiveSheet.UsedRange
Cel.Value = Trim(Cel.Value)
Next
End Sub
[/vba]

[shrivallabha]

It worked perfectly. Thank you very much.

Sorry for being naive but how do you mark it as "Solved"?.