Copy data from IE to Excel
 

The code given below opens Internet Explorer, goes to my internet web
page, uses the userid and password that I have suppied, logs in, then
again goes to a specific page and pulls it up. Then it copies the
entire data on that page (turn on Microsoft Internet Controls from
Tools-References) and paste it in A1 of Sheet1.

The above code works fine if I go step by step using the F8 key in the
VBA macro window but when I run the code entirely then it goes so fast
that it does not run the <<Do While .busy: DoEvents: Loop and <<Do
While .ReadyState < 4: DoEvents: Loop lines and it does not login to
my asp page hence cannot pull the specific page (second url)

Any ideas???

TURN ON MICROSOFT INTERNET CONTROLS FROM TOOLS-REFERENCES
================================================== ========

Public Declare Function ShowWindow& Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Integer)

Sub LoginWebReports()

Dim ie As Object

On Error GoTo 1

Set objWSS = CreateObject("WScript.Shell")
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "http://www.MyIntranetASPPage.com"
Do While .busy: DoEvents: Loop
Do While .ReadyState < 4: DoEvents: Loop
With .Document.Forms(0)
.UserName.Value = "MyUsername"
.Password.Value = "MyPassword"
.Action.Click
End With

' <<I am navigating below once again to a different URL only because, I
have to login to the above page first"

With ie
.Visible = True
.navigate
"http://www.MyIntranetASPPage.com/Mypage.asp?&EndDate=3-5-06&StartDate=3-5-06"
Do While .busy: DoEvents: Loop
Do While .ReadyState < 4: DoEvents: Loop
End With

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ie.Quit

Range("A1").Select
ActiveSheet.Paste

End With

Set ie = Nothing

Exit Sub

1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub