Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query
What is today’s date, and what is the current time of day? Here, you create a Web query to import a display of the current day and time for several North American time zones.
I came up with this solution out of necessity a few years ago while developing a client project that called for no changes to be allowed in workbooks after certain dates and times. Some users were circumventing the rule by changing the system clock on their computers, so I used a reliable, external source by which to programmatically monitor actual dates and times.
You access the website to the United States Naval Observatory, where the day and time are recorded on the Master Clock of the United States Navy. The Web query will place the information onto Sheet1 from a macro named TimeAfterTime, similiar to what is shown in the above picture..
Sub TimeAfterTime()
'Open a With structure for the destination worksheet.
With Worksheets("Sheet1")
'Declare a String type variable for the web site address.
Dim strURL As String
'Define the web site address, from which the information
'will be imported to your worksheet.
strURL = "http://tycho.usno.navy.mil/cgi-bin/timer.pl"
'For consistency, I prefer to activate the worksheet
'that will receive the web data.
'Cell A1 is a convenient cell to situate yourself.
Application.Goto .Range("A1"), True
'Clear the cells in the worksheet so you know the data
'being imported will not be confused with other data
'you may have imported previously and not yet deleted.
Cells.Clear
'Open a With structure for the Add method of your new
'Query Table. The connection, URL, and destination sheet,
'and other information that follows, must be specified.
With .QueryTables.Add _
(Connection:="URL;" & strURL, Destination:=.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
'Close the With structure of Query Table's Add method.
End With
'Close the With structure for the destination worksheet.
End With
End Sub
Dear Tom, I had to tweek the macro as strURL = “http://tycho.usno.navy.mil/timer.pl” did not open the URL.
Instead, I made it:
strURL = “https://www.usno.navy.mil/USNO/time/display-clocks/simpletime”.
While executing this macro, I got the message “Javascript must be Enabled”. (Must be becuase I have not downloaded Javascript).
The Navy changed their URL which they said back in 2020 they were working on but never said what that new URL would be. Thanks for your observations. It looks like they imposed a javascript component which is harmless, but frankly I expect the Navy will change something else about this URL before they settle on a final link.