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
Exit For
End If
End If
Next objIndex
End Sub
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.
This may be more suited to Slashdot comment, but why would you want to give Internet Explorer the focus at all ?
Hui,
I use this method quite often. Not as he used it giving IE the focus.
I created a function called GetIE that grabs a Website if already open and returns the IE object, or creates a new IE object.
Pretty useful in my opinion.
Dim oShellWin As SHDocVw.ShellWindows, _
oWin As Object, _
ieRunning As Boolean
ieRunning = False
Set oShellWin = New SHDocVw.ShellWindows
If oShellWin.Count Then
For Each oWin In oShellWin
If Not sWindowTitle Like vbNullString Then
If oWin.LocationName Like “*” & sWindowTitle & “*” Then
ieRunning = True
Set GetIE = oWin
Exit For
End If
Else
If oWin.Name Like “Microsoft Internet Explorer” Then
ieRunning = True
Set GetIE = oWin
Exit For
End If
End If
Next oWin
End If
If ieRunning = False Then
Set GetIE = CreateObject(“InternetExplorer.Application”)
End If
Set objSW = Nothing
End Function
I think Hui meant why would anyone use IE with so many better browsers available. At least that’s how I took it.
Spot on Dick
so is there a way to do this with Firefox?
There’s a section in the book “Professional Excel Development” called “Working With Windows” that uses some pretty complicated API calls with multiple instances of Excel to determine which is the correct one to use. Perhaps it could be altered to work with IE.
I like David’s code, I do some work with IE occasionally so I’ll test it out.
Thx,
JP
HI,
I wanted to learn automation IE apps through EXcel.
below is one of the code that i saw in this site.
but, it doesnt work.
it throws an error “User-defined type not defined”
however, please provide good way to learn automating webapps.
Regarding, the references none of them are in my PC version Excel2007.
have to be download separately.?
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim i As Long
‘Create new instance of IE
Set IeApp = New InternetExplorer
‘Make it visible – some things don’t work
‘unless it’s visible
IeApp.Visible = True
‘define the page to open
sURL = “www.dicks – blog.com”
‘navigate to the page
IeApp.Navigate sURL
‘Pause the macro using a loop until the
‘page is fully loaded
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
‘store the Document object
Set IeDoc = IeApp.Document
‘Loop through the links collection. Most collections
‘seem to be zero based, but it’s pretty much trial and
‘error for me
For i = 0 To IeDoc.Links.Length – 1
‘write the linking url to a cell
Cells(i + 1, 1).Value = IeDoc.Links(i).href
Next i
‘Clean up
Set IeApp = Nothing
Thankss,
Venkat