davidbenson3
03-16-2008, 06:32 AM
Within MS Excel, I am using a macro to extract the current date from the source code of a web page and convert it to an accurate date-type value that is assigned to a variable in VBA. The code initializes an Internet Explorer object, then loads the source code into it. The date is then extracted and converted.
The strange thing is that after I close Excel and open IE7, the web page from which the date was extracted is shown as a tab and the page I opened is shown as a second tab. If I close and re-oen IE7, it's still there. It's as if the process never stopped or did not clear the searched site after the marco was finished and is displaying the site as a tab. What's even stranger is that it only happens when IE7 is opened by using Start - Favorites... then the web page shortcut. It does not happen when a shortcut is double clicked on the desktop. The only thing that makes it go away is restarting the computer.
Here is the code used to extract the date:
Dim currentDate As Date
Dim ieApp As Object
Dim i As Long
Dim sDate As String
Dim lFirstCom As Long, lSecCom As Long
Dim sInner As String
Const sURL = "Web Page Address"
Const lElement As Long = 36
Const ieREADYSTATE_COMPLETE As Long = 4
Set ieApp = CreateObject("InternetExplorer.Application")
ieApp.navigate sURL
Do
DoEvents
Loop Until ieApp.ReadyState = ieREADYSTATE_COMPLETE
sInner = ieApp.document.all(lElement).innertext
lFirstCom = InStr(1, sInner, ",")
lSecCom = InStr(lFirstCom + 1, sInner, ",")
sDate = Mid(ieApp.document.all(lElement).innertext, lFirstCom + 1, lSecCom - lFirstCom + 5)
If Len(sDate) < 10 Or Len(sDate) > 20 Then
currentDate = Now()
Else
currentDate = CDate(sDate)
End If
ieApp.Quit
Set ieApp = Nothing
Is there a way to prevent this from happening? Thanks in advance for any help provided.
David
The strange thing is that after I close Excel and open IE7, the web page from which the date was extracted is shown as a tab and the page I opened is shown as a second tab. If I close and re-oen IE7, it's still there. It's as if the process never stopped or did not clear the searched site after the marco was finished and is displaying the site as a tab. What's even stranger is that it only happens when IE7 is opened by using Start - Favorites... then the web page shortcut. It does not happen when a shortcut is double clicked on the desktop. The only thing that makes it go away is restarting the computer.
Here is the code used to extract the date:
Dim currentDate As Date
Dim ieApp As Object
Dim i As Long
Dim sDate As String
Dim lFirstCom As Long, lSecCom As Long
Dim sInner As String
Const sURL = "Web Page Address"
Const lElement As Long = 36
Const ieREADYSTATE_COMPLETE As Long = 4
Set ieApp = CreateObject("InternetExplorer.Application")
ieApp.navigate sURL
Do
DoEvents
Loop Until ieApp.ReadyState = ieREADYSTATE_COMPLETE
sInner = ieApp.document.all(lElement).innertext
lFirstCom = InStr(1, sInner, ",")
lSecCom = InStr(lFirstCom + 1, sInner, ",")
sDate = Mid(ieApp.document.all(lElement).innertext, lFirstCom + 1, lSecCom - lFirstCom + 5)
If Len(sDate) < 10 Or Len(sDate) > 20 Then
currentDate = Now()
Else
currentDate = CDate(sDate)
End If
ieApp.Quit
Set ieApp = Nothing
Is there a way to prevent this from happening? Thanks in advance for any help provided.
David