Get last Windows/PC shutdown time with VBA

Question

Is there a way to get the last shutdown time of Windows with VBA?

I would like to follow the start and the end of the working time. The start of the working time is done, the specified excel file starts with windows and the macro runs automatically. But the shutdown time looks bit difficult.

If I am right: Event ID 1074 - this event is logged in two situations: either by a shutdown command from the Start menu or when an application causes the computer to restart or shutdown.

Is it possible to check it with VBA or is there any other options?

Answer 1

In VBA, you can access the Windows Event Logs to check for event IDs such as 1074 to determine the last shutdown time of your Windows computer. However, this would require using Windows Management Instrumentation (WMI) and querying the event logs, which can be a bit complex. Here's a general outline of how you can approach this:

Import the necessary libraries: To work with WMI in VBA, you'll need to reference the "Microsoft Scripting Runtime" and "Windows Script Host Object Model" libraries. To do this, open the Visual Basic for Applications (VBA) editor in Excel, go to "Tools" > "References," and check these libraries.

Write VBA code: Below is a sample VBA code snippet to retrieve the last shutdown time from the Windows Event Logs using WMI. This code assumes that you're looking for event ID 1074

  Sub GetLastShutdownTime()
    Dim objWMIService As Object
    Dim colEvents As Object
    Dim objEvent As Object
    
    ' Connect to WMI service
    Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
    
    ' Query for events with EventID 1074 (shutdown events)
    Set colEvents = objWMIService.ExecQuery("SELECT * FROM Win32_NTLogEvent WHERE LogFile='System' AND EventCode=1074")
    
    ' Iterate through the events to find the last shutdown
    For Each objEvent In colEvents
        ' The last shutdown time is in the 'TimeGenerated' property
        MsgBox "Last Shutdown Time: " & objEvent.TimeGenerated
        Exit For ' Exit after the first matching event (last shutdown)
    Next objEvent
    
    ' Clean up
    Set objWMIService = Nothing
    Set colEvents = Nothing
    Set objEvent = Nothing
End Sub

This code queries the "System" log for events with EventID 1074 and retrieves the last shutdown time from the "TimeGenerated" property of the event. You can customize this code to suit your needs, such as saving the shutdown time to a variable or a cell in your Excel file.

Keep in mind that this code will require administrative privileges on your computer to access the Event Logs. Additionally, the actual event log location and structure may vary slightly depending on your Windows version. Make sure to test and adapt the code as needed for your specific environment.

Answer 2

Please, use the next function, able to return the last ShutDown time as Local time. The code, as it is, needs a reference to 'Microsoft WMI Scripting V1.2 Library'. It can be run without it, declaring all involved variables/objects As Object (Late binding). But adding the respective reference offers intellisense suggestions and it can be used for more tasks:

Function GetLastShutdownTime() As Date
    'It needs a reference to 'Microsoft WMI Scripting V1.2 Library'
    Dim DateTime As WbemScripting.SWbemDateTime  'Object
    Dim objWMIService As WbemScripting.SWbemServicesEx  'Object
    Dim colEvents As WbemScripting.SWbemObjectSet ' Object
    Dim objEvent As WbemScripting.SWbemObjectEx ' Object
    
    ' Get WMI service object:
    Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
    
    ' Create a new datetime object.
    Set DateTime = CreateObject("WbemScripting.SWbemDateTime")
    
    ' Query for events having  EventCode=1074 (shutdown events). They are returnded from the last one to the first:
    Set colEvents = objWMIService.ExecQuery("SELECT * FROM Win32_NTLogEvent WHERE LogFile='System' AND EventCode=1074")
    
    ' Iterate through the events to find last shutdown (the first one found):
    For Each objEvent In colEvents
        Debug.Print objEvent.timegenerated 'it rturns the last shutDown as GMT (string) time!
        
        DateTime.value = objEvent.TimeGenerated 'place the GMT found time as DateTime value
        
        'return the event date time as LOCAL TIME:
        GetLastShutdownTime = DateTime.GetVarDate: Exit For
    Next objEvent
    
    ' Clean up memory
    Set objWMIService = Nothing: Set colEvents = Nothing: Set objEvent = Nothing: Set DateTime = Nothing
End Function

If adding the required reference looks difficult, I can place a piece of code able to add it automatically. In fact, I will post it now:

Private Sub WVIScriptingFromFile()
  'Microsoft WMI Scripting V1.2 Library'...
   On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromFile "c:\Windows\System32\wbem\wbemdisp.TLB"
    If Err.number <> 0 Then
        MsgBox "The reference already exists..."
    Else
        MsgBox "The reference added successfully..."
    End If
  On Error GoTo 0
End Sub

The above function can be tested using the next Sub:

Sub TestGetLastShutdownTime()
   Debug.Print GetLastShutdownTime
End Sub

Please, send some feedback after testing it.