Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*