Question:
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 that was searched 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.
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 = "http://www.time.gov/timezone.cgi?Central/d/-6"
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
Answer1:
I was unable to replicate that behaviour using IE7 under WinXP Pro, with the macro in Excel 2003.
However I do not have a "Favorites" in my Start menu, so I could not exactly match your situation.
Two possibilities come to mind:
1. At some point while you were testing the code, the macro stopped partway through, so that the IE object had been created but the Quit command had not been run. That would leave the instance of IE open but not visible (as you did not set ieApp.Application.Visible=True), so perhaps the next time you start IE that url is occupying a tab. I couldn't replicate that either, but it may be dependent upon IE settings. I expect it would stop after a reboot.
2. If it only happens with one particular favorite, is the second url saved into that favorite? (Might have happened unintentionally if that tab was there when the favorite was saved.)
Note also - I could not get anything assigned to any of the variables that grabbed data from the web page. sInner and sDate were blank (""). That could be to do with differences in IE settings or security settings, but I suggest you watch those variables and see if they're actually working for you.
If a post solves your problem, please mark it as an answer.
Answer2:
Thanks for your reply! Perhaps you could temporarily enable 'Favorites' in your Start menu to replicate the behavior. It's also a mystery to me why it does not happen when I click a link on the desktop. The extra tab appears regardless of which Favorite I click. I always end up with two tabs showing - the first of which is the searched 'Date' page.
I don't know why you didn't get values for the variables. It was working well for me only recently. Perhaps the page changed or I pasted incorrectly. I'll check again to make sure. Thanks again.
David
Answer3:
I just visited that time site again and watched what my firewall did. It blocked a popup and some header info. That blocking might be why my variables don't set the same as yours - if I haven't completely downloaded the page then the position of the time on the page may be slightly different.
The popup could be related to your problem. If that url creates 2 instances of IE (the source page and the popup), and your macro only Quits one of them, where does that leave the other? It's still running, but not visible. I expect you'd see it listed if you ran Task Manager. I'm not sure how you'd get a handle on that second instance from your macro. What happens if you add a second ieApp.Quit (I'm guessing an error)? What about adding the navNewWindowsManaged flag to your Navigate command (that might block the popup)?
If that's what's happening then the reason you see it when opening IE via favorites and not via shortcuts is that (NB this might vary with your IE settings) the favorites add that url as a new tab in an existing instance of IE (in this case the invisible one that didn't get closed by the macro), while the shortcut would be starting a new instance of IE (check the shortcut - if it includes an IE exe then it's starting a new instance of IE). Starting IE directly from either the Start menu or a shortcut would also start a new instance of IE.
NB the flags you can set on the Navigate command are explained here:
BrowserNavConstants
If a post solves your problem, please mark it as an answer.
Answer4:
I think you're right that it is somehow connected to individual IE7 settings because the problem doesn't happen on two other computers I tested. I also noticed on the computer with the problem that even though the IE7 window was closed, the iexplorer process still shows up on the list of running processes in task manager. Once that's manually closed in task manager, the tab goes away. Is there a way to terminate or kill that process listed in the task manager using VBA? If so, I would add it to the end of the macro to make sure IE7 closed completely, regardless of individual settings. Thanks!
David
Answer5:
I found this code used to terminate any running process if it exists in the task manager list and placed in the workbook_deactivate section. It seems to have solved the problem.
Public Sub TerminateProcess()
'---------------------------------------------------------------------------------------
' : Terminates a process. First checking to see if it is running or not.
' : Uses WMI (Windows Management Instrumentation) to query all running processes
' : then terminates ALL instances of the specified process
' : held in the variable strTerminateThis.
' :
' : ***WARNING: This will terminate a specified running process,use with caution!.
' : ***Terminating certain processes can effect the running of Windows and/or
' : ***running applications.
'---------------------------------------------------------------------------------------
Dim strTerminateThis As String 'The variable to hold the process to terminate
Dim objWMIcimv2 As Object
Dim objProcess As Object
Dim objList As Object
Dim intError As Integer
strTerminateThis = "iexplore.exe" 'Process to terminate - any process in list can be substituted
Set objWMIcimv2 = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\.\root\cimv2") 'Connect to CIMV2 Namespace
Set objList = objWMIcimv2.ExecQuery _
("select * from win32_process where name='" & strTerminateThis & "'") 'Find the process to terminate
If objList.Count = 0 Then 'If 0 then process isn't running
Exit Sub
Else
For Each objProcess In objList
intError = objProcess.Terminate 'Terminates a process and all of its threads.
'Return value is 0 for success. Any other number is an error.
If intError <> 0 Then
Exit Sub
End If
Next
Set objWMIcimv2 = Nothing
Set objList = Nothing
Set objProcess = Nothing
Exit Sub
End If
End Sub
Thanks for the assistance!
David