如何从 Excel VBA 宏中获取 UTC 中的当前日期时间

How to get the current datetime in UTC from an Excel VBA macro

提问by Jon

Is there a way in an Excel VBA macro to get the current datetime in UTC format?

Excel VBA 宏中有没有办法以 UTC 格式获取当前日期时间？

I can call Now()to get the current time in the local timezone; is there a generic way to then convert this to UTC?

我可以打电话Now()来获取当地时区的当前时间；有没有一种通用的方法可以将其转换为UTC？

采纳答案by brad.huffman

http://excel.tips.net/Pages/T002185_Automatically_Converting_to_GMT.html

http://excel.tips.net/Pages/T002185_Automatically_Converting_to_GMT.html

There is a macro on that page with a LocalTimeToUTC method. Looks like it would do the trick. Also some formula examples if you wanted to go that route.

该页面上有一个带有 LocalTimeToUTC 方法的宏。看起来它会成功。如果您想走那条路，还有一些公式示例。

Edit - Another link. http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspxThis page has several methods for date/time. Pick your poison. Either should do the trick, but I feel like the second is prettier. ;)

编辑 - 另一个链接。 http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx这个页面有几种日期/时间的方法。选择你的毒药。两者都应该可以解决问题，但我觉得第二个更漂亮。;)

回答by gogeek

Simply, you can use COM Object to achieve UTC Time Information.

简单来说，您可以使用 COM Object 来实现 UTC 时间信息。

Dim dt As Object, utc As Date
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate Now
utc = dt.GetVarDate(False)

回答by InteXX

Granted this question is old, but I just spent some time putting together some clean code based on this and I wanted to post it here in case anyone coming across this page might find it useful.

当然，这个问题很老，但我只是花了一些时间基于此整理了一些干净的代码，我想将其发布在这里，以防遇到此页面的任何人可能会发现它有用。

Create a new Module in the Excel VBA IDE (optionally giving it a name of UtcConverteror whatever your preference may be in the Properties Sheet) and paste in the code below.

在 Excel VBA IDE 中创建一个新模块（UtcConverter可以选择在属性表中为其指定名称或您的偏好）并粘贴以下代码。

HTH

HTH

Option Explicit

' Use the PtrSafe attribute for x64 installations
Private Declare PtrSafe Function FileTimeToLocalFileTime Lib "Kernel32" (lpFileTime As FILETIME, ByRef lpLocalFileTime As FILETIME) As Long
Private Declare PtrSafe Function LocalFileTimeToFileTime Lib "Kernel32" (lpLocalFileTime As FILETIME, ByRef lpFileTime As FILETIME) As Long
Private Declare PtrSafe Function SystemTimeToFileTime Lib "Kernel32" (lpSystemTime As SYSTEMTIME, ByRef lpFileTime As FILETIME) As Long
Private Declare PtrSafe Function FileTimeToSystemTime Lib "Kernel32" (lpFileTime As FILETIME, ByRef lpSystemTime As SYSTEMTIME) As Long

Public Type FILETIME
  LowDateTime As Long
  HighDateTime As Long
End Type

Public Type SYSTEMTIME
  Year As Integer
  Month As Integer
  DayOfWeek As Integer
  Day As Integer
  Hour As Integer
  Minute As Integer
  Second As Integer
  Milliseconds As Integer
End Type


'===============================================================================
' Convert local time to UTC
'===============================================================================
Public Function UTCTIME(LocalTime As Date) As Date
  Dim oLocalFileTime As FILETIME
  Dim oUtcFileTime As FILETIME
  Dim oSystemTime As SYSTEMTIME

  ' Convert to a SYSTEMTIME
  oSystemTime = DateToSystemTime(LocalTime)

  ' 1. Convert to a FILETIME
  ' 2. Convert to UTC time
  ' 3. Convert to a SYSTEMTIME
  Call SystemTimeToFileTime(oSystemTime, oLocalFileTime)
  Call LocalFileTimeToFileTime(oLocalFileTime, oUtcFileTime)
  Call FileTimeToSystemTime(oUtcFileTime, oSystemTime)

  ' Convert to a Date
  UTCTIME = SystemTimeToDate(oSystemTime)
End Function



'===============================================================================
' Convert UTC to local time
'===============================================================================
Public Function LOCALTIME(UtcTime As Date) As Date
  Dim oLocalFileTime As FILETIME
  Dim oUtcFileTime As FILETIME
  Dim oSystemTime As SYSTEMTIME

  ' Convert to a SYSTEMTIME.
  oSystemTime = DateToSystemTime(UtcTime)

  ' 1. Convert to a FILETIME
  ' 2. Convert to local time
  ' 3. Convert to a SYSTEMTIME
  Call SystemTimeToFileTime(oSystemTime, oUtcFileTime)
  Call FileTimeToLocalFileTime(oUtcFileTime, oLocalFileTime)
  Call FileTimeToSystemTime(oLocalFileTime, oSystemTime)

  ' Convert to a Date
  LOCALTIME = SystemTimeToDate(oSystemTime)
End Function



'===============================================================================
' Convert a Date to a SYSTEMTIME
'===============================================================================
Private Function DateToSystemTime(Value As Date) As SYSTEMTIME
  With DateToSystemTime
    .Year = Year(Value)
    .Month = Month(Value)
    .Day = Day(Value)
    .Hour = Hour(Value)
    .Minute = Minute(Value)
    .Second = Second(Value)
  End With
End Function



'===============================================================================
' Convert a SYSTEMTIME to a Date
'===============================================================================
Private Function SystemTimeToDate(Value As SYSTEMTIME) As Date
  With Value
    SystemTimeToDate = _
      DateSerial(.Year, .Month, .Day) + _
      TimeSerial(.Hour, .Minute, .Second)
  End With
End Function

回答by user1454265

If all you need is the current time, you can do this with GetSystemTime, which involves fewer Win32 calls. It gives you a time struct, with millisecond precision, which you can format how you'd like:

如果您只需要当前时间，则可以使用GetSystemTime执行此操作，这涉及较少的 Win32 调用。它为您提供了一个毫秒精度的时间结构，您可以按照自己的方式格式化：

Private Declare PtrSafe Sub GetSystemTime Lib "Kernel32" (ByRef lpSystemTime As SYSTEMTIME)

Private Type SYSTEMTIME
  wYear As Integer
  wMonth As Integer
  wDayOfWeek As Integer
  wDay As Integer
  wHour As Integer
  wMinute As Integer
  wSecond As Integer
  wMilliseconds As Integer
End Type

Usage:

用法：

Dim nowUtc As SYSTEMTIME
Call GetSystemTime(nowUtc) 
' nowUtc is now populated with the current UTC time. Format or convert to Date as needed.

回答by skankyblackbird

My Access project works with mostly Access tables linked to MS SQL Server tables. It is a DAO project and I was having trouble even getting a SQL sproc with GETUTCDATE() to come back. But following was my solution.

我的 Access 项目主要使用链接到 MS SQL Server 表的 Access 表。这是一个 DAO 项目，我什至无法使用 GETUTCDATE() 获取 SQL sproc 返回。但以下是我的解决方案。

-- Create SQL table with calculated field for UTCDate
CREATE TABLE [dbo].[tblUTCDate](
    [ID] [int] NULL,
    [UTCDate]  AS (getutcdate())
) ON [PRIMARY]
GO

Create an Access table, dbo_tblUTCDate, linked via ODBC to the SQL table tblUTCDate.

创建一个 Access 表 dbo_tblUTCDate，它通过 ODBC 链接到 SQL 表 tblUTCDate。

Create an Access query to select from the Access table. I called it qryUTCDate.

创建一个 Access 查询以从 Access 表中进行选择。我称之为 qryUTCDate。

SELECT dbo_tblUTCDate.UTCDate FROM dbo_tblUTCDate

In VBA:

在 VBA 中：

Dim db as DAO.database, rs AS Recordset
Set rs = db.OpenRecordset("qryUTCDate")
Debug.Print CStr(rs!UTCDATE)
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

回答by tomasdeml

If you also need to account for daylight saving time, you may find the following code useful:

如果您还需要考虑夏令时，您可能会发现以下代码很有用：

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Windows API Structures
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Type SYSTEM_TIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(0 To 31) As Integer
    StandardDate As SYSTEM_TIME
    StandardBias As Long
    DaylightName(0 To 31) As Integer
    DaylightDate As SYSTEM_TIME
    DaylightBias As Long
End Type    

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Windows API Imports
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Function TzSpecificLocalTimeToSystemTime Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION, lpLocalTime As SYSTEM_TIME, lpUniversalTime As SYSTEM_TIME) As Integer

Function ToUniversalTime(localTime As Date) As Date
    Dim timeZoneInfo As TIME_ZONE_INFORMATION

    GetTimeZoneInformation timeZoneInfo

    Dim localSystemTime As SYSTEM_TIME
    With localSystemTime
        .wYear = Year(localTime)
        .wMonth = Month(localTime)
        .wDay = Day(localTime)
    End With

    Dim utcSystemTime As SYSTEM_TIME

    If TzSpecificLocalTimeToSystemTime(timeZoneInfo, localSystemTime, utcSystemTime) <> 0 Then
        ToUniversalTime = SystemTimeToVBTime(utcSystemTime)
    Else
        err.Raise 1, "WINAPI", "Windows API call failed"
    End If

End Function

Private Function SystemTimeToVBTime(systemTime As SYSTEM_TIME) As Date
    With systemTime
        SystemTimeToVBTime = DateSerial(.wYear, .wMonth, .wDay) + _
                TimeSerial(.wHour, .wMinute, .wSecond)
    End With
End Function