breynolds0431
Active Member
- Joined
- Feb 15, 2013
- Messages
- 303
- Office Version
- 365
- 2016
- Platform
- Windows
Hello. I have a main file that updates data from an external file on
The problem comes to play when someone from another time zone opens the main file. I know how to convert local time to UTC, which could be added during the update to the main file's sheet. But, I think I would also need a way to convert the FileDateTime(ExternalFile) to UTC as well. Otherwise, there would likely be a couple hours not being considered. Anyone know of a way to convert FileDateTime() to UTC?
Workbook_Open
and Workbook_BeforeSave
. When it updates, it writes the date/time to a named range in the sheet of the main file (not the external file). I then use that update's date and time to evaluate if an update should be made when the main file is opened/closed using the FileDateTime()
of the external file. That is, if the date/time on the sheet in the main file is less than the date/time of the FileDateTime(ExternalFile), then the update should run. The problem comes to play when someone from another time zone opens the main file. I know how to convert local time to UTC, which could be added during the update to the main file's sheet. But, I think I would also need a way to convert the FileDateTime(ExternalFile) to UTC as well. Otherwise, there would likely be a couple hours not being considered. Anyone know of a way to convert FileDateTime() to UTC?
VBA Code:
Private Sub Workbook_Open()
'Will only run Hub Data update if the Hub modify date/time changed
Dim specs As Range: Set specs = ThisWorkbook.Sheets("UpdateSpecs").range("HubDtTm")
Dim FilePath As String: FilePath = "T:\FSD\AR\Hub Log\"
Dim MyFile As Variant: MyFile = Dir$(FilePath & "*.xlsb")
'specs is already UTC converted
'>>this is where (I'm thinking), FileDateTime would need to be converted to UTC
If specs < FileDateTime(FilePath & MyFile) Then
Call UpdateHubData
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub