Solved

DownloadComplete Event in VBA

Posted on 2010-11-20
17
1,941 Views
Last Modified: 2012-05-10
Dear Expert,

I use IE.nagivate to web URL and use .readystate and .busy
to check whether the webpage is loaded completely. But is not
stable at all, sometimes, it works or sometimes not. I heard one EEvthread  at
http://www.experts-exchange.com/Database/Miscellaneous/Q_21086529.html
to solve the issue, it shoud use DownloadComplet events. But I don't
know how to use and what is different using between DownloadCOmplet
and .readystate and .busy ?And I check information from Microsoft,
http://msdn.microsoft.com/en-us/library/aa752084(VS.85).aspx,
it just  reports DonwloadComplete events and
doesn't have any example  to show how to use it

The question is
QUestion-1: What is different between .readystate and DownloadComplete ?
QUestion-2 : How to use DownloadComplete event in VBA using IE object
Question-3, Could you provide some code for me to follow for tutorial ?
Question-4, in  just mentioned  EE thread above , I just see the subroutine, objIE_doucmentComplete,
but don't see how to activate the subroute ,Why?
Is it automation calling without VBA users to call it ?
 
Private Sub objIE_DocumentComplete(ByVal pDisp As Object,
                                         URL As Variant)
   If (pDisp Is objIE.Object) Then
      Debug.Print "Web document is finished downloading"
   End If
End Sub


Please advise
Duncan
0
Comment
Question by:duncanb7
  • 10
  • 7
17 Comments
 
LVL 13

Accepted Solution

by:
gbanik earned 500 total points
ID: 34180433
QUestion-1: What is different between .readystate and DownloadComplete ?

readyState is a property that signifies the state of the IE control which has been instructed to fetch a page. After instructing it to load a web page, this property is checked to find the status of the fetch. If the control has completes the fetch, the state turns to 4 (which is "completed"). On the other hand, DownloadComplete is an event attached with the IE control. This event gets fired when the control completes the fetch operation.
 
Effectively, when the fetch operation is completed, the DownloadComplete event fires and readyState changes to 4.

QUestion-2 : How to use DownloadComplete event in VBA using IE object

While writing code, both "readystate" or "DownloadComplete" could be used. Do realize that by nature, "readyState" would need you to check the status repeatedly in a loop to track "when" the operation completed. Whereas with DownloadComplete, the event would be generated from within the control. So no tracking required. Hence, DownloadComplete is the way to go!

Question-3, Could you provide some code for me to follow for tutorial ?

Please see attached sample code.

Question-4, in  just mentioned  EE thread above , I just see the subroutine, objIE_doucmentComplete,
but don't see how to activate the subroute ,Why?
Is it automation calling without VBA users to call it ?

You would have to load the IE object WithEvents to be able to see the Events exposed by the IE object. If done correctly, the event would fire if the Download Completes. Please take a look at the code in the attached file.
Automate-IE.xlsm
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34180443
By the way, without knowing the kind of problem you are trying to solve, I have a suggesstion. Web Browser Control is definitely a choice u should look at. You can have much more control as the WebBrowserControl embeds inside your form. Thus allowing you a lot of freedom in your code.... and a much tighter environment from an user interaction point of view.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180697
Thanks it is really good in detail. Just clarify one more ppint.
If we have no issue to use do loop to do  such as Do unitl .ready=4 and not busy loop that is exactly same function and purpose
for DownloadComplete Event, Right ?

Since I myiE.nagivate one download webiste and the webiste need me to enter product Number  with enter keys and display
the inofrmation on the IE web page first according to my input product number and then I can click one download button
to popup download menu to save the file in PC.  Since I get 300 product files to download, so I want to reduce  total loading time
for automation file download by API function
So I doing a test and I found when enter product key and enter key, and the product NO information is shown on my IE,  
and then disconnect Internet, and I am able to save information into the file. The test is telling me the popup donwload
diaglog is just for save file not related to internet fetch, so I think the issue is display data information in IE webpage.
 
And I think my code as follows has issue since the loop is just checking internet fetech and doesn't
check whether the data is completed on the IE display page and memory so I always click the downlaod button only
save previouse product NO information. That is why I want to find a way to check when the data is ready on IE display
or memory completely . So I goggle the site to find out DownloadComplete for solving the issue.
It seems DownloadComplete event is similar operation as ready state, Right ?

Please advise and go ahead to quick study your material,  Now I Knwo Withevents is only able on Form, Class not module.

Duncan

My Part of Code for fetch data issue
============================================
With myie
.navigate "www.mywebsite.com"
Do until .ready=4 and not busy loop
hdlg=findwindow ("Mywebsite Tile", vbnullstring)
SendMessageSTRING hdlg, WM_SETTEXT, 6, productNo  'Enter Product NO
postmessage hdlg, WM_KeyDown , &HD, vbnullstring   'Data is coming after enter key
Do until .ready=4 and not busy loop   ' Want to check whetter data is on momery and IE display first , How to fix here code ?
PostMessage(hdlg, WM_LBUTTONDOWN, 0, vbNullString) "Click download button  "The bug is here to display previous productNO information.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180715
If downloadComplet Event operation is same as looping .readystate and busy , I think it is hard
to check is product information data coming is completely on IE page or memery.
Why I care this because some file is small some is big and I don't want to add
idle time such as sleep(1000) to wait the data on memory or IE completely that will
increase program download file time.

With myie
.navigate "www.mywebsite.com"
Do until .ready=4 and not busy loop
hdlg=findwindow ("Mywebsite Tile", vbnullstring)
SendMessageSTRING hdlg, WM_SETTEXT, 6, productNo  'Enter Product NO
postmessage hdlg, WM_KeyDown , &HD, vbnullstring   'Data is coming after enter key
Do until .ready=4 and not busy loop   ' Want to check whetter data is on momery and IE display first , How to fix here code ?
Sleep(3000) ' Adding Sleep(3000) It works but total time is increasing a lot about 10 TimesPostMessage(hdlg, WM_LBUTTONDOWN, 0, vbNullString) "Click download button  "The bug is here to display previous productNO information.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180739
Your Example is good, but I have to run my code in module, and WithEvents is only on Class, form, Thisworkbook,
and now I know Raise events happen if the name is oIE  with _DownnlaodComple(0
and I try to put it in  oIE_DownloadComplete in Class module  attached as code area but  fail to display
msgbox "DownloadComple Evenet Done", please advise the mistake if possible


Your Code:
Option Explicit

Dim WithEvents oIE As InternetExplorer
Dim oDoc As HTMLDocument
Dim oA As HTMLAnchorElement

Public Sub OpenIE()
Set oIE = New InternetExplorer
oIE.Visible = True
oIE.Navigate "www.google.com"
End Sub

Private Sub oIE_DocumentComplete(ByVal pDisp As Object, URL As Variant)
Set oDoc = pDisp.Document
If InStr(1, URL, "www.google.com/search?") > 0 Then
    oIE.Navigate oDoc.getElementById("ires").Children(0).getElementsByTagName("a")(0).href
ElseIf InStr(1, URL, "www.google.com") > 0 Then
    oDoc.getelementsbyname("q")(0).Value = "Disneyland"
    oDoc.getelementsbyname("btnG")(0).Click
End If
End Sub

'Class Module , name "Class1"

Option Explicit

Public WithEvents oIE As InternetExplorer



Sub oIE_DownloadComplete()



MsgBox "DownloadComplete Event  Done"

End Sub



'In general module 

===========================

Sub RunMe()

Dim myie As Class1

  Set myie = New Class1

  Set myie.oIE = New InternetExplorer

  

  myie.oIE.Visible = True

  myie.oIE.navigate "http://www.hsbc.com.hk/1/2/chinese/home"



    End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34180771
Here is what u want. Go to the module and run OpenIE(). After google.com opens, msgbox pops.
Automate-IE.xlsm
0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180802
Sorry it si not working if I put the _DocumentComplet in classmodule
and the run code in genernal module. I don't wan't to put it Thisworkbook
since it will open file and running the code.

Please help to put it in modMisc for _ducumentComplet() and
other run code in Moduel1 for testing


Duncan
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34180810
Please provide a sample file and suggest (indicating in the code) what u want.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 13

Author Comment

by:duncanb7
ID: 34180814
SOrry again

Please hep to put  _ducumentCompplete() and WithEvents code
in Class module, others can keep it in modMISC  ebcause
I don't want to test WithEvents in Thisworkbook that might not
simulate my code need , and try it and report error.




0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180821
See file attached
Automate-IE2.xls
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34180832
There.... the WithEvents code now in a class and no code in ThisWorkbook. Is this what u r looking for?
Automate-IE.xlsm
0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180843
Yes, Coud you answer this

DownloadComplet Event operation is same as looping .readystate and busy
like as follows ?

.nagviate "www.yahoo.com"
Do unti <> .busy and readystate=READYSTATE_COMPLETE
Doevents
Loop
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34180862
The DownloadComplete Event is definitely better than tracking the readystate property. Why not let IE do its work and tell you when the work is done!! (see my first comment... Q2)
Plus, u save a lot of processing there (CPU) by not doing the loop!!!
0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180879
If you go to read my thread again, you know better , be reminded
the typing error.
The question is, The webiste is already navigated and just need one time to do it.
And when I enter Product NO m the IE display will change on screen information
according to Product No. and I simulate Click button by Postmessage faster
than the screen information , the popup download  menu will still show
the save filename for the previous edit of product NO. Hope you see my
issue.

It seems DownloadComplete Event is working only on .naviagte or .naviagte2
are activated. If I just need to do one time .navigate it might not help my
problem

0
 
LVL 13

Author Comment

by:duncanb7
ID: 34180882
Try image the data is streaming data and change data information
according to your input edit and enter keys press
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34180940
.readyState and DocumentComplete are 2 different approaches, u r free to use whatever suits u. Both would work as good as each other. See attached for the readyState implementation.
Hope your queries are answered.
Automate-IE.xlsm
0
 
LVL 13

Author Closing Comment

by:duncanb7
ID: 34180963
Thanks for your reply and file

it reply what I ask even though
it might not solve my issue
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

#Citrix #Internet Explorer #Enterprise Mode #IE 11 #IE 8
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
How to create a custom search shortcut to site-search Experts Exchange using Google in the Firefox browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch your Bookmark Menu: Press 'Ctrl +…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now