I'm getting a problem with excel switching UK date fomat to US date format. The code I have is opening the most recent downloaded file in a folder
and automatically switching the UK date format to a US date format.
I'm using an excel 2016 xlsm to open an excel 2007-2013 xls file
The file in question has a UK date format date already and there is no problem if I open this file manually
so logic tells me there is something that the code is or isn't doing to prevent
the format switch from occurring or maybe its something to do with the two different types of excel.
I've had (along with many others I suspect) the annoying never ending UK/US date format problem
with excel before and managed to work round it but this code is something I have copied from
somewhere else and I'm unfamiliar with most of it.
I've tried using the text to columns trick but it seems to behave differently in code than when used manually which is not something I want to do
as automation of all the processes is what is needed.
The code switches the date formats as soon as the file is opened so I'm struggling to see where I can put any code in place to stop this happenning
If its just a code thing then with my very limited coding ability I am guessing the Dir$ or the wildcard are the culprits.
This code works fine until I encounter a UK day of 12 or under then it switches them around to US format
The code switches the date formats as soon as the file is opened so I'm struggling to see how I can put any code in place to stop this happening
Sub ProcessResults() 'Opens most recent download of results.xls file & copies relevant data to results sheet Dim xpath As String Dim xfile As String Dim InternalWB As Workbook Dim LatestFile As String Dim LatestDate As Date Dim LMD As Date Dim wb As Workbook Application.ScreenUpdating = False Set wb = Nothing xpath = Sheets("Tables").Range("L14").Value 'path If Right(xpath, 1) <> "\" Then xpath = xpath & "\" xfile = Dir$(xpath & "results*" & ".xls", vbNormal) '* Wildcard is for date/hour/min parameters. If Len(xfile) = 0 Then MsgBox "Can't find what your looking for!", vbExclamation Sheets("Today").Select Exit Sub End If Do While Len(xfile) > 0 LMD = FileDateTime(xpath & xfile) 'process for finding all files above using FileDateTime function. loops thru until most recent date AND name match "like" above If LMD >= LatestDate Then LatestFile = xfile LatestDate = LMD End If xfile = Dir Loop On Error Resume Next Set wb = Workbooks(LatestFile) On Error GoTo 0 If wb Is Nothing Then Set InternalWB = Workbooks.Open(xpath & LatestFile) 'Opens file above. Else Set InternalWB = Workbooks(LatestFile) MsgBox LatestFile & " is already open." End If Application.ScreenUpdating = True End Sub