I'm cerating an Excel app which currently reads info from various format Excel files (xls, xlsx, xlsm etc). I want to add text (and csv) type files to this list.

To open a selected file an UDF is used:
----------------------------------------------------------------
Function strOpenFile(parPath, parExt, parTitle) As String

Dim strCurrDir As String
Dim strFile As String
Dim oWB As Workbook

strCurrDir = CurrDir

ChDrive parPath
DhDir parPath

strFile = Application.GetOpenFileName(FileFilter:=parFilter, Title:=parTitle)

If strFile <> "" Then

strFile = Right(strFile,Len(strFile)-InStrRev(strFile,"\",,1))
If Application.Workbooks(strFile) Is Nothing Then

Set oWB = Nothing
Set oWB = Workbooks.Open(strFile)

Else

Workbooks(strFile).Activate

End If

End If

ChDrive strCurrDir
ChDir strCurrDir

strOpenFile = strFile

End Function
----------------------------------------------------------------

The problem with text files (and with csv's probably too) is, that when International settings are used, then Excel messes up with dates.

I (and many of users in Europe) have dates in format dd.mm.yyyy. In text file the date string columns may have different formats - in my test file I have a column of dates as text in format dd-mm-yyyy (without any quotes). The formats in text files must remain as they are.

Now when I open the test text file from Excel manually, the dialog window is opening to initialize delimitters, column formats, etc. I don't change anyting, click on Finish, and the text file is opened. All is OK. Date column is converted to dates in my Windows Date Format dd.mm.yyyy.

When I open the same text file from my App, the file is simply opened, but:
a) the dates where day number is > 12 are displayed as date stings in format dd-mm-yyyy. It is no problem, as my procedure can handle them - CDate() function converts them to right dates;
b) the dates where day number is <= 12 are converted to dates, but day and month are switched - i.e. 01-05-2017 is converted to 05.01.2017 (instaead 1st of May I get 5th of January).

Of course the user can convert the text file manually to Excel file, and read in the Excel file instead of text one, but I'm trying to minimize manual work here.

So is there some way to force VBA to open text file without converting dates at all, using my current strOpenFile() function? I googled a bit, but the solutions I did find were very different.

I decided for a workaround here, as I couldn't find a way to stop excel trying to convert datestrings to dates.

I have a client setup table in my app, where for every client it's forecast file structure is described. I added a column into this table with value TRUE when months and days are switched when the client file is opened from VBA, and FALSE or empty otherwise. When the dates are read in from source file, the last step is to check, is the check value for this client TRUE and the day number less than 13 - when yes, then the reverse switching is made.