Internet Explorer Automation from Excel

M

mattdee

New Member
Joined
Oct 22, 2004
Messages
17
Hi,

I've been struggling with a problem for a number of days and would appreciate any help that could be offered. Extensive use of google is not alleviating my pain...

I have a VBA project in Excel that is storing parameters for some network equipment and presenting them in a series of userforms. Within the userforms I have some features such as ping to allow a support guy to check if the node is still alive. I also have a "Open Browser" command button that opens an IE instance and browses to the management UI of the network node. This is where the problems occur. The web UI requires a user name and password entering (basic authentication) in a popup window.
To achieve this I am using the following code:
________________________________________________________________________
Sub OpenBrowser()

Dim ie As InternetExplorer
Set ie = New InternetExplorer
Dim Header As String
Dim url As String


Header = "Authorization: Basic XXXXXXXXXXXXXX" & Chr$(13) & Chr$(10)
url = "http://10.10.3.2/"

With ie
.Navigate2 url, Nothing, Nothing, Nothing, Header
.Visible = True

Do Until ie.readyState = 4
DoEvents
Loop

End With

End Sub
_____________________________________________________________________

In so far as this goes it works fine, except I have been running packet captures (wireshark) to check the navigate2 command was adding the authorization header correctly, which it does.
The problem is that after launch from excel, every following HTTP GET from IE to the webUI has to have the authorization credentials in them. Hence when IE is launched you are prompted to enter the user / pw in another popup box for each frame in the browser window. It would appear that the instance of IE is not caching the details and submitting them with each GET.

Is anyone aware of a way of overcoming this issue and parsing the credentials from the Excel project to IE for the entire session?

I appreciate that this isn't the normal Excel / VBA problem so if it is deemed to be in the wrong forum then thats fine with me, but I would appreciate anyones advice.

If anyone requires further information then please don't hesitate to ask.
Thanks for your time. Cheers
Matt
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
It appears you are opening a new IE instance everytime you run this code, thus wiping all history. Can you try opening one IE instance, and make it publically available to all procedures?

Inefficient of course because you will probably have to open it before you need it, and you'll need a way to manually close it, but somehow you need to use the same session.

Not sure if this will work, just guessing!
 
Upvote 0
Cheers for the prompt response Chris,
I'm a bit on an amateur at this so apologies if I'm wide of the mark here.

Set ie=New InternetExplorer ..... actually starts IE up as a process.
ie.navigate2 .... navigates to the ip of a mgmt webui with the auth header in the HTTP header.
ie.visible..... brings the browser onto the desktop

The initial ie.navigate sends the correct HTTP GET with the authorization header information. I can see the HTTP GET on the wire in the packet captures. The webserver in the kit responds to IE with an HTTP 200 OK detailing the page title and the URI's for the frames in the main page. IE then sends HTTP GET's for these frames but without the HTTP authorization header thus you get prompted for the user password again for each frame you've requested.

I ran a comparison with a normal session, manually opening a browser and logging in. The packet capture showed that after the inital log in, every HTTP GET from IE has the authentication header.

I double checked with task manager and only one instance of iexplore was running and only one tab open.

It would seem logical that something triggers IE to do this and I'm guessing there is some kind of option that hopefully can be set from VBA with some IE code.

Cheers
matt
 
Last edited:
Upvote 0
Hi Matt. I don't really have a clue but do know that there is a difference in behavior and access when using automation as opposed to GUI only. I have run into issues before and they have always been related to security such as cross scripting violations. If you are running IE8, try the variations below that provide an IE object, but with different interfaces. This is one of those, "I don't know how or why, but it works". Maybe it will work for you...

Dim ie As InternetExplorerMedium
Set ie = Nothing
Set ie = New InternetExplorer

OR

Dim ie As WebBrowser
Set ie = Nothing
Set ie = New InternetExplorer
 
Upvote 0
Tom, thanks

I gave both suggestions a try, unfortunately with the same results as before. Both parse the header information in the inital GET but fail to do so thereafter.

I think I probably neeed to start looking into the IE object model and seeing if there is anything there.

Cheers
Matt
 
Upvote 0
Still another shot in the dark... I thought that the header was passed as a byte array in the nav method. You are using a unicode string. Once again, I am just shooting in the dark but trying to be helpful...
 
Upvote 0
Hi Tom,

Any help is greatfully received. I'll give it a try later on today when I'm back in the office. I have seen some snippets of code with what you suggest, I've also seen a microsoft page where its a string.... I'll post links just in case anyone is interested.

Thanks again Tom.
Regards
Matt
 
Upvote 0
Ok, after a quick hunt around I've amended the code to include string conversion from Unicode to a base64 encoded byte array. (Code borrowed from http://www.nonhostile.com/howto-encode-decode-base64-vb6.asp)

A quick check proves this works correctly for the first authorization but as with previous attempts, it doesn't submit the header information in the next set of HTTP GET's.

If anyone has any further suggestions they'd be greatfully received. Thanks
____________________________________________________________________________
Sub OpenBrowser()

Dim ie As InternetExplorer
Set ie = New InternetExplorer
Dim Header, EncHeader As String
Dim url As String

Header = "XXXX:XXXX"
url = "http://10.10.3.2"

EncHeader = EncodeBase64(StrConv(Header, vbFromUnicode))
EncHeader = "Authorization: Basic " & EncHeader & Chr$(13) & Chr$(10)

With ie
.Navigate2 url, , , , EncHeader
.Visible = True

Do Until ie.readyState = 4
DoEvents
Loop

End With

End Sub


Private Function EncodeBase64(ByRef arrData() As Byte) As String

Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement

' help from MSXML
Set objXML = New MSXML2.DOMDocument

' byte array to base64
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text

' thanks, bye
Set objNode = Nothing
Set objXML = Nothing

End Function
 
Upvote 0
Ok, a little more research but not much progress. According to Microsoft IE should cache credentials if you are using basic auth or NTLM. See the notes in this article:
http://support.microsoft.com/kb/264921

"When Internet Explorer has established a connection with the server by using Basic or NTLM authentication, it passes the credentials for every new request for the duration of the session."

In this case this doesn't appear to be happening so I started checking out the .navigate2 / .navigate flags to see if there is anything that may solve the problem. Again no success but for anyone interested here is the link for BrowserNav constants:
http://msdn.microsoft.com/en-us/library/dd565688(VS.85).aspx

If anyone has any ideas, however wild, I'd be greatful to hear them.
Thanks
Matt
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Autoamting Internet Explorer webpage using VBA, but stuck with a window popup to enter a value
Replies
0
Views
2K
SATZ
S
S
  • Question
Text dissappear from webform after click on submit button vba
Replies
0
Views
424
Shriyan
S
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
380
AndyEd
A
M
  • Question
VBA macro copy/paste text from Internet Explorer
Replies
0
Views
2K
MichaelFerguson
M
W
  • Question
VBA IE Automation Logon, Navigate multiple buttons and then scrape data back into excel
Replies
0
Views
2K
wid2001
W

Forum statistics

Threads
1,214,589
Messages
6,120,416
Members
448,960
Latest member
AKSMITH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top