**hardest question on fourms***Web Query returned no data

[walshd231]

Hi,

I have set up an excel document that is using web query's to pull data off a password protected website. The web queries work fully for the week and when I come in on Monday I get a error that start "web query returned no data' and then it tells me to go and edit the query. when I go to the query I have to log back into the website and reset 30 different web queries.

I have entered a VBA that I have been told should get past the password protected website and I have been told the code looks good to work. I have copied the code below.

If any one can help it would be great as my job may depend on the result of the project.

Thanks.

Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub CompanyName()


Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
On Error GoTo Err_Clear
MyURL = “CompanyWebsite/“
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.ID.Value = "2" 'Enter your email id here
HTMLDoc.all.UserName.Value = “****” 'Enter your username here
HTMLDoc.all.Password.Value = “****” 'Enter your password here
For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
Next
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If




End Sub

 

[John_w]

I don't think you can use that IE automation code to log in to the website and expect the web queries to work because Excel's internal IE instance/session is separate from the IE browser opened by your code. You have two options:

1. Set the website as the IE home page, log in manually and tick the 'remember me' or similar box to remain logged in. Excel's web queries may then work with IE already logged in to the website.

2. Extend the IE automation code to navigate to the required web page and extract the data.