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