+ Reply to Thread
Results 1 to 22 of 22

Wait for winhttp request to be done (much like XML http request)

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Wait for winhttp request to be done (much like XML http request)

    Hi all,

    when I pull data through XML, I'll do something like:

    Please Login or Register  to view this content.
    However, winhttprequest doesn't let me do this do loop.
    I'd want this, since I'm pulling from a slow site and I don't want excel to give the impression it hangs.

    Is there any way I can do something similar?
    The following does not work...

    Please Login or Register  to view this content.
    Error returned is:

    Run-time error '-2147483638 (800000a)':
    The data necessary to complete this operation is not yet available.

    Any suggestions on how to overcome this?
    I figure I can use an 'on error resume next', but I'm sure there are more graceful ways to do this?

    Thanks in advance!

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Have a look here: https://stagesolutions.wordpress.com...http-with-vba/

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    I did and I tried... can't get that to work though.
    (Can't find the ActiveData Objects reference either).

    But I will try to put a simple winhttp request into a class so it can be called asynchronous (if it is that sample).

    Thanks,

    Jasper

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    You need both classes. Where did you get stuck?

    It's Microsoft activex data objects you're looking for

  5. #5
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    It tells me on "Option Compare Database" - Compile Error: Expected: Text or Binary
    It tells me on "Private WithEvents http As WinHttpRequest" some error too.

    I commented out the first line
    I removed WithEvents from the second line

    Then it would step through the following fine, but have no clue how/where to get the responsetext with that one.
    Please Login or Register  to view this content.
    Also, what if I need to pass on requestheaders, cookies, body text etc.... ?
    I am not smart enough to understand the whole class code and the jumping back and forth between different parts, so it's hard for me to change it.

    I basically want to get a json reply from one of our internal servers, but since it's 88k records, it takes long and don't want the sheet to 'hang' and go to 'Excel is not responding' while waiting for the request....

    Perhaps I should look at XML and manually handle the logins & cookies?

  6. #6
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Actually, I thought of a workaround.

    If I put my code in a modeless userform and have the code run in there in some random sub (I put it in the 'on doubleclick' code for now), then Excel allows me to continue working on the excel sheet apart from running any code.
    I could live with that limitation unless a better solution comes along.

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Actually it occurred to me that I've already given you an example of doing this here: http://www.excelforum.com/excel-prog...-a-second.html

    Here's a working example:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Kyle123; 07-09-2015 at 04:38 AM.

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Or perhaps a little less cryptically:
    Please Login or Register  to view this content.

  9. #9
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Hi Kyle,

    many thanks as always! Seems to work very well!
    However, I still have the problem I encountered on http://www.excelforum.com/excel-prog...-a-weburl.html

    (How to deflate a gzip response in winhttprequest) - so that occurs here as well.
    Any chance you know a solution to that?

  10. #10
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Thanks for the solution in the other thread. Is it possible that a very large CSV (14MB) download would time-out? Can I check for timeouts?

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Yes, absolutely, I think the default receive timeout is 30 seconds, you can overwrite it with the settimeouts property, have a look here https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx it needs doing before the open call

  12. #12
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    OK, so got everything working perfectly now.
    Last q on this topic - is it possible to set an 'array' of WinHTTP requests?

    Right now, it's
    Please Login or Register  to view this content.
    but in the url, there's settings that I need to change a few times. The settings are stored in a variable, so ideally I'd do something like this
    Please Login or Register  to view this content.
    My common sense and gut tells me that this isn't possible, but to be honest, I've seen you (and others on this forum) pull off more things that I held for impossible, so thought I'll just throw this question out there

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Not like that, you'd want something like the code from the link I posted that's designed for exactly that purpose (taking a load of urls and downloading them async)

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Simple working example of how you'd do it:
    Class asyncHTTP
    Please Login or Register  to view this content.
    Userform Code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Hi Kyle,

    you have no idea how much I appreciate this! It's really super useful - not just in this case, but in many ways where I need to use multiple web-queries.
    Thanks a bunch.

    This file is so useful for many people, that I'd think it warrants a sticky by the way!

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Glad it's working for you

  17. #17
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    One last question - is there a limit to setting the number of async http requests?
    What would prevent me from saying:
    Please Login or Register  to view this content.

  18. #18
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    There's no hard limit, but I suspect that at large numbers you'll run into trouble. You'll also consume loads of memory since the requests don't go out of scope until you close the form - though you could code round that. You're really into territory of needing to multithread, but you can't do that natively since VBA is single threaded.

  19. #19
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Is it possible to run this from a module, rather than a userform?
    I guess not, as you need to have the callback in the userform, but if it is possible, it would be useful as well

    Also - I randomly get "Run-Time Error '1004' - Application-defined or object-defined error' when running it.
    I guess that lies in the returns coming in together and colliding in the 'update' code?
    I tried capture what exactly causes the error, but as it appears random it's impossible to replicate it in a step-through cycle.
    Also trying 'debug' won't work, as that brings me to the asynchttp class rather than the userform code.

    It's frustrating, as when it doesn't error, it works like a dream!
    Last edited by JasperD; 07-16-2015 at 04:53 PM.

  20. #20
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Wait for winhttp request to be done (much like XML http request)

    Set your error trapping to "Break in Class Module" in the VBE options, it will then break where the error is.

    Called from a module, this is much more icky as it requires the asyncHTTP object be tightly bound, but it works:

    asnycHTTP
    Please Login or Register  to view this content.
    Module:
    Please Login or Register  to view this content.

  21. #21
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Alright, so everything works!

    But.... if I don't have focus on excel when there's a callback to the update sub in the module, it'll give me a 'Run-time error '50290': Application-defined or object-defined error'.

    It specifically errors out on this line:
    Please Login or Register  to view this content.
    I tried the following syntaxes as well:
    Please Login or Register  to view this content.
    All to no avail...

    I know I can set focus back to excel, but I that might interupt users doing other stuff and not wanting to wait for the update to be done.
    Is there a way that I can prevent this, so it works even if Excel is in the background?

    Thanks!

    Jasper

  22. #22
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Wait for winhttp request to be done (much like XML http request)

    Anyone any idea on the 50290 error when no focus on Excel?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] xml or winhttp - Can I send a POST request with a lot of parameters?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 04-02-2015, 04:52 AM
  2. SENDING HTTP REQUEST and GETTING RESPONSE
    By downtown1933 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2013, 09:43 PM
  3. VBA HTTP GET request
    By WAW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2012, 05:08 PM
  4. Http Request Repsonse Query
    By Killavirus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2010, 07:38 PM
  5. Sending HTTP request with parameter.
    By gajendra.gupta in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2007, 04:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1