Hey All,
Have just been doing a big search of the internet to see what I can do to put a web query into Excel that will first log into a password protected site and then retrieve the data I want.
I have gone through all the posts on Mr Excel which are excellent but as they were in 2005 Im wondering if there has been any updates, is there anything new which will help me do this.
I tried to use the following code on an open event when a workbook is open
and then hoped that if the page is now active the webqueries I had put in earlier would work, they did not.
So does anyone have any knowledge of this area that may help, i.e how can I get the web query to refresh every time it is opening on a password protected site.
Thanks
Have just been doing a big search of the internet to see what I can do to put a web query into Excel that will first log into a password protected site and then retrieve the data I want.
I have gone through all the posts on Mr Excel which are excellent but as they were in 2005 Im wondering if there has been any updates, is there anything new which will help me do this.
I tried to use the following code on an open event when a workbook is open
Code:
Private Sub Workbook_Open()
'Open site if it requires a PassWord Model!
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
'Go to this Web Page!
IE.Navigate "Webpage"
'Check for good connection to web page loop!
Do
If IE.ReadyState = 4 Then
IE.Visible = True
Exit Do
Else
DoEvents
End If
Loop
'Wait for window to open!
Application.Wait (Now + TimeValue("0:00:05"))
'MsgBox "Done"
IE.Visible = True
'Send logg-on information!
'May need additional SendKeys as needed?
'Determine by the actual key-strokes needed!
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "Username", True
SendKeys "{TAB}", True
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "Password", True
SendKeys "(TAB)", True
Application.Wait (Now + TimeValue("0:00:05"))
SendKeys "{ENTER}", True
End Sub
and then hoped that if the page is now active the webqueries I had put in earlier would work, they did not.
So does anyone have any knowledge of this area that may help, i.e how can I get the web query to refresh every time it is opening on a password protected site.
Thanks