Open csv file delimited by pipe character "|" or not common delimiter

Question

I'm trying to set up an Excel VBA code that opens up some .csv files and split into columns the information contained and delimited by the character |. I manage to open the file but the code I use opens my files without splitting the text according to the delimiter. So far I have tried the following code:

Sub OpenCSV()

Dim wkbTemp As Workbook
Dim sPath As String, sName As String

sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.csv"

Set wkbTemp = Workbooks.Open(Filename:=sPath & sName, Format:=6, Delimiter:="|")

End Sub

Answer 1

I remember this has driven me insane some time back.
It seems that Excel has an uncontrolled greed for .csv files. If you just change the ending (.txt, .dat or whatever), it will work!