Sunday, 15 April 2012

vba - ExcelVBA - HttpReq via MSXML2.XMLHTTP - fetch page after loading page -


I have a problem getting data from an internal web based dataset (Cognos). Actually I put together a GET request like "blah.com/cognosapi.dll?product=xxx&date=yyy ..." , send it to the server and get a webpage that I can store I'm HTML and later parsed in my Excel form.

I create a VBA program that worked very well in the past but the web service has changed, now they are displaying "Your report is running" page from 1sec Until 30sec so when I call my function, I always download the "Your report is running" page to download this data. How can the page be loaded automatically after the "Reporting is going on" page?

This is the DownloadFile function with the target path as GETstring and Parameter.

  object as Public Function Daunlodfail (as string sSourceUrl, the string as a string), Boolean Dim http or Actiteepiarisik = object set Actiteepiarisi = Object Set ( "Msaksmel 2 . XLTTP ") Dim HTML Webcams New MSHtml Html document htpc Open "GA", SSOSRUr, fail HTTPPREC Then send HttpReq.getAllResponseHeaders HtmlDoc.body.innerHTML = HttpReq.responseText Debug.Print HtmlDoc.body.innerHTML HttpReq.Status = 200 End download if 'file. BINDF_GETNEWESTVERSION Force to download from the specified source 'API' 'Passing 0 & amp; Due to being downloaded safely dw helps locally cached 'copies, if available. If API returns' error ERROR_SUCCESS (0), download file returns are correct. DownloadFile = URLDownloadToFile (0 & amp;, _ sSourceUrl, _ sLocalFile, _ BINDF_GETNEWESTVERSION, _ 0 & amp;) = ERROR_SUCCESS End Function   

Thanks David < P>

Finally you have given me the last link to solve your problem. I baked the code in my downloadfile function, so that I can remain with IE object till the end and then close it.

An error I have found that should be ready to be prepared before doing anything with HTMLObject. PublicFile (as sSourceUrl string, _sLocalFile as string) Boolean Dive IE InternetExplorer set IE = New InternetExplorer dim htmldoc New MSHTML.HTMLDocument dim collTables as MSHTML.IHTMLElementCollection dim collSpans as of the MSHTML.IHTMLElementCollection as in as dim objSpanElem MSHTML.IHTMLSpanElement as as integer with dim fnum IE it awaits the '.Visible = true for the page to load. Navigate (sSourceUrl) "false you see the browser window Change you want to do new "is" until .readyState = 4: DoEvents: set with loop end Set htmldoc = wait_for_html (sSourceUrl, "text / css") in htmldoc = IE.Document ' "span" tag Set collSpans = HtmlDoc.getElementsByTagName ("search for period") 'first span element cotains ... set objSpanElem = collSpans (0)' ... if the loading screen is displayed until the loop objSpanElem.innerHTML = "your report is running Just grab the table and leave the rest set collTables = HtmlDoc.getElementsByTagName ("table") fnum = FreeFile () Open sLocalFile for output fnum as' lt file. To add hedge and html and body tag print #fnum, "and; Html & gt; "Print #fnum," & lt; body & gt; "Print #fnum, collTables (15) .outerHTML 'title Print #fnum, collTables (17) .outerHTML' Date print #fnum, collTables (18) .outerHTML 'part, operation and so on. Print #fnum, collTables (19). OuterHTML 'Miscellaneous Print # FNUM,' & lt; / body & gt; Print # FNUM, "& lt; / Html> "Close #fnum IE.Quit" Close Explorer DownloadFile = True End Function

No comments:

Post a Comment