The only thing I can’t do is to get XL to open the existing IE window so I can ‘sendkeys’ to do that part. I can only see similar posts for “create.object” related to IE, and nothing for “get.object” (or whatever will work).
According to XtremeVBTalk, GetObject doesn’t work with IE and you have to use Shell. You need to set a reference to Microsoft Internet Controls and Microsoft Shell Controls and Automation.
Then this sub will give one of the IE instances the focus. In this example, it’s the IE instance whose title contains the phrase “Daily Dose”, but you’ll have to change to suit your situation.
Dim objShell As Shell
Dim objIndex As InternetExplorer
Set objShell = New Shell
For Each objIndex In objShell.Windows
If TypeName(objIndex.Document) = “HTMLDocument” Then
If InStr(1, objIndex.Document.Title, “Daily Dose”) > 0 Then
objIndex.Visible = True
Of course I almost never advocate the use of SendKeys. There is almost certainly a better way to get that html into Excel than copying and pasting, but it’s probably beyond the scope of a blog post.
Also, there’s some question in my mind about whether I can declare objIndex as InternetExplorer. From ExtremeVBTalk, it seems like both Windows Explorer and Internet Explorer windows are in the Shell.Windows collecton. If that’s the case, declaring objIndex as Object would be more prudent (similar to looping through sheets in a workbook – you don’t know if you’ll get worksheets, chartsheets, or something else). At msdn, it says that Shell.Windows returns a ShellWindows collection object, which is a collection of InternetExplorer windows, and doesn’t mention Window Explorer windows. I tried the code as written with multiple IE and Windows open and it didn’t fail. It did loop through both types of windows, but it happily coerced Windows Explorer windows into an InternetExplorer object, I guess.