PDA

View Full Version : Strange IE7 quirk when macro is run



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

Simon Lloyd
03-16-2008, 08:54 AM
Also posted here (http://www.ozgrid.com/forum/showthread.php?t=86669)

lucas
03-16-2008, 09:09 AM
David, Click here (http://www.excelguru.ca/node/7) for an explanation of cross-posting.

You will find that many will ignore your request for help if you don't provide a link when cross posting to multiple forums.....

davidbenson3
03-16-2008, 10:23 AM
Sorry about that. Didn't know it would be a problem to ask for help in two different forums. I didn't even know they were linked. I was trying to increase my odds of a solution, since no one I've asked up to now has had any idea what's going on. I'll put a link next time.
David