Problem with InStr() or Mid$()... again

[jm314]

I'm having a problem with getting , I believe, either the InStr or Mid$ functions to work the way I want them to. My intention is to get the US Naval Observator's Eastern Standard time directly from their web page you can use to check the time they are keeping.

I posted about this problem once before for a different project and figured out how to work around it in the previous instance. This time it's proving more difficult, but more importantly, I still don't know what's going on to produce the illogical results. This is code that is giving me strange results:

Cells(1, 1).Value = USNavyNow
End Sub
Public Function USNavyNow() As String

Dim oIE As New SHDocVw.InternetExplorer
Dim sText As String
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer
Dim sTime As String

On Error GoTo ERR_USNAVYNOW

Set oIE = New SHDocVw.InternetExplorer 'open hidden instance of IE

'Navigate to US Naval Observatory's current time web page
oIE.Navigate "[URL unfurl="true"]http://tycho.usno.navy.mil/cgi-bin/timer.pl"[/URL]
Do Until oIE.ReadyState = READYSTATE_COMPLETE
  DoEvents
Loop

'get HTML of page into a variable
sText = oIE.Document.body.innerhtml

'close IE and release memory
oIE.Quit
Set oIE = Nothing

'search sText for beginning and end of required information
iStart = InStr(1, sText, "<BR>") + 4
iStart = InStr(iStart, sText, "<BR>") + 4
iEnd = InStr(iStart, sText, "<BR>") - 2

'Parse sText for required information
If iStart <= 0 Or iEnd <= 0 Then
  sTime = "TIME NOT AVAILABLE"
Else
  sTime = Mid$(sText, iStart, iEnd)
End If

USNavyNow = sTime
Exit Function

ERR_USNAVYNOW:
USNavyNow = "ERROR #" & Err.Number & ": " & Err.Description
End Function

This is an example of my intended result:
Oct. 11, 03:45:05 PM EDT

This is an example of the strange, actual result:
Oct. 11, 04:57:31 PM EDT <BR>Oct. 11, 03:57:31 PM CDT <BR>Oct. 11, 02:57:31 PM MDT <BR>Oct. 11, 01:57:31 PM

This is an example of the HTML of target web page:
<TITLE>What time is it?</TITLE>
<H2> US Naval Observatory Master Clock Time</H2> <H3>
<BR>Oct. 11, 19:45:05 UTC
<BR>Oct. 11, 03:45:05 PM EDT
<BR>Oct. 11, 02:45:05 PM CDT
<BR>Oct. 11, 01:45:05 PM MDT
<BR>Oct. 11, 12:45:05 PM PDT
<BR>Oct. 11, 11:45:05 AM YDT
<BR>Oct. 11, 09:45:05 AM AST
</H3></B><P><A HREF="

http://tycho.usno.navy.mil">Time

Service Department, US Naval Observatory</A>


Can anyone provide any insight into what I'm doing wrong or into a better way to search the string "sText" for the appropriate HTML tags?

 

[PHV]

Replace this:
sTime = Mid$(sText, iStart, iEnd)
By this:
sTime = Mid$(sText, iStart, iEnd - iStart + 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jm314]

Thanks, PHV!

I figured I was doing something wrong!

 

[fumei]

Could you tell what reference I need to get SHDocVw.InternetExplore to work? Thanks.

Gerry

 

[PHV]

what reference I need to get SHDocVw.InternetExplorer
Microsoft Internet Controls

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244