How to pause a macro whilst letting a web query finish and then resume macro

[Eno]

I am very new to office 2010 excel vba so please keep your answers simple.
I have a list of films starting at Cells(i, j) ( which is B12) and going downwards.
I have a web query setup that automatically gets film information from the web & puts it into cell AC60 & AC64 as soon as I enter the film title in B1.
So what the code does is gets the film title & pastes it to B1 which starts the web query, then it copies some stuff from the web query info & pastes it to 2 other cells, then it grabs the next film title etc etc.

But it takes approx 30 seconds for the web query to finish but the macro doesn't wait for this it just carries on.

I have tried using the wait function but it also stops the web query.

Is there a way of pausing the macro whilst still letting the web query work?

I've wasted a whole weekend on this, it's the best way to learn but enough is enough and I need help.

Here's the code:

Please Login or Register  to view this content.

[Kyle123]

Can you post the code for paste film? 30 second seems a long time, maybe we can improve that first

[Eno]

The pastefilm was something I had commented out, it is not part of the code.
I had thought that if I split the code in 2 & had a wait function to start the second half then this would work.
In theory it did but I did not know how to split the code in 2 whilst maintaining the code to work it's way down the list with the offset cell thingy.
For that reason I resorted to just trying to pause the macro but unfortunately the web query also pauses.
The actual code for pasting the film info is the lower half of the original posting, pasting the data from AC60 & AC64.

I am amazed someone replied so quickly, thank you Kyle123.

[Kyle123]

Ok, what we're actually trying to do here is bodge a workaround to stop code continuing whilst your webquery is refreshing. It is much better to address the underlying issue of why the code is so slow and improve this code so that further execution can't occur until it's finished (as well as make it faster). Using wait is usually undesirable since it either waits too long and makes your code slower than it should be or doesn't wait long enough and makes a mess

If you post your other code, we might be able to resolve the underlying issue and do away with the need for a wait altogether

[Eno]

I'm not aware of any other code, the web query was made using the excel menus so it's not something I've written.
I'll try & record a macro of the process & post it very soon.

[Kyle123]

Ah right, got you. Could you please post your workbook, poor a sample of it with the webquery set up so I can have a look?

[Eno]

Here's the web query code I collected during a record macro. The way I got this web query tool to work was to enter the word "question" in the websites search bar & then amend the URL by adding [" each end of the word question. Then it asks what "question" refers to & I put B1.
What this means is that every time I put a film title in cell B1 the web query automatically starts & gets the required info from the web sites own search. So the Macro doesn't know that the web query has started, so presumably this is why it doesn't wait for it to be completed.

Please Login or Register  to view this content.

[Kyle123]

Ok, and what data is it that you're actually trying to extract from the page?

[Eno]

Ok, here is the file, pwd bastard0

All relevant code in module 1.
I use the "get film information" button to start the code.

The data I need is the films title with year & the film revue.

It's getting late here in New Zealand so I'll reply to any posts in the morning, approx 9pm Leeds time.

Thanks Kyle123 for all your help so far, you are correct in trying to fix the underlying issue & not patch it up, thanks in advanve, Eno.

[Eno]

I just tried to save that last file version & it crashes excel each time I click save, never had that before.
I've attached another working version of the same file just in case the earlier upload had the same problem.
I'm definately off to bed now.

[Cutter]

@ Eno

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

[Eno]

I'm a step nearer fixing this, this is what I did:
In the web query setup via the menu sytem I unticked the automatic refresh if the contents of B1 changes.
Then I recorded a macro of what the refresh button does.
I put that code in the middle of my original code & now the macro waits for the refresh of the web query to complete before going to the next film.
I am very pleased with this, not because it works well because it doesn't, more because I fixed it without any real knowledge or experience.
Ok, so it kind of works, click on the "Get Film Information" button & see what it does.
You will notice that it's very very slow which is what Kyle123 mentioned earlier.

I've been fighting above my weight to get this far but I have no idea how to make this work any faster so I'm after some help.
I must admit that the web site I'm getting this data from may have to change because the film data is not always in the same place in the table but it is the idea of getting the data that I'm excited about because I can then use the same concept on other more worthy projects.

[Kyle123]

Morning Eno,

I'm having a look into this for you, as you suggest the website will probably have to change. What info are you actually looking for when you hit search as multiple films will be received, are you looking for one of these to return further detail on? Or is it just the list of returned matches that you want?

[Eno]

Hi Kyle123
I've attached 2 files, the excel is the latest version of the film database web query sheet & the word doc is my notes on how I got the web query to work.
The excel does work but it is very slow, I can only do approx 20 films at a time before excel crashes. I managed 50 earlier today in one go so I tried 70 and crashed excel again. So I'm back to doing 20 at a time.
I had a look around for a better web site to get the film info from but after checking out approx 10 sites I ended up back to the original again. But this time I used the web sites advanced search and ticked a few search filters which makes the data much better, but the web sites search does get it wrong sometimes for example searching for the film called '21' it returns with the film 21 Jump Street.
The film data I was after was the year & a synopsis, but I also need the title so I can double check it is the correct film.
It would be good to also get some kind of idea of how good the film is such as a star rating from the site etc etc but that would be just a nice to have.
So, as I said earlier, it does work but it's very slow and crashes excel if I do more than 50. Is it slow becaise the web sites slow? Even it was the web site why does excel crash, & how can we stop that happening.
I don't mind the slow speed because I can run it at night but the crashing is a real problem.
Do you remember earlier that I mentioned one of the excels I posted would crash when I try & save it, well I googled this & found that many people have the same problem & the common theme was that the excels all had web queries set up. The web queries were not being used at the time, actually the contents of all the whole workbook could be deled but it would still crash when saving. So because of this I've d eleted old & unused web queries & just kept the one working one & it seems to be fine.
I'm starting to ramble now so good luck to stopping the crashing, one last thing the code will carry on through all 1329 films until it hits a blank cell, so if you test it you should really make sure there is ablank cell early on in the list.

[Kyle123]

Ok, here's a sub that I've just put together that will pull back all the info that you're looking for. It should be relatively fast since it doesn't use a webquery. Instead it uses the theMovieDb API, so all the data is returned from there. This API returns data in the XML format, so the code below extracts the bits above you've mentioned that you're looking for, the data returned is actually pretty all inclusive and the sub only displays part of it for example images related to the film can also be displayed.

Anyhow, the below puts: Movie Name, Rating, Description, Date of Release and A link to the website (themoviedb)

Into sheet one starting at cell A1 so be sure to run this on an empty workbook first.

You'll also need an api key. You can get one by following the instructions here: http://api.themoviedb.org/2.1/

Don't worry about not having a website, I signed up for one this morning and just put no website in the website box and the key came through.

Once you have it, simply replace YOURAPIKEY in the code below with your actual Key:

Please Login or Register  to view this content.

[Eno]

Thanks Kyle123
I've got & inserted my own API, I know nothing about this API & don't really understand the code but excited to see it work.
But I'm not sure how to get the sub working.
Each time I try to run it asks me what Macro i want to run, so it doesn't recognize this as a macro.
I get the same error if I try to run a sub on a page with no subs.
I just know this is a stupid question & I'm going to kick myself when I find the answer, I'm googling for answers right now but haven't found any, please tell me what newbie mistake I'm making.

[Eno]

Thanks Kyle123
Never under estimate how much you know & how little others don't.
I spent all day trying to get this to work, it was a great learning curve but I know it would take others mere seconds to have done it.
You were spot on with this approach, the speed is almost instant, compared with 30 seconds waiting on a web query.
Unfortunately the sub does stop if it runs an unknown film title, try using excel forum as a film title to see what happens.
Any ideas on fixing that would be good.
I will have a look at incorporating these subs into my original workbook but it's getting late here so maybe tomorrow.
So here it is:

Please Login or Register  to view this content.

[Kyle123]

Glad you got it working, I've been trying to post to this thread for an hour, but keep getting permission denied.

Lets hope this one gets through, I'll have a look at some error handling for you and post back (if I can)


Ok, try this one:

Please Login or Register  to view this content.

---------- Post added at 08:36 AM ---------- Previous post was at 08:31 AM ----------

I'd recommend doing the search for movie data as you add the movie, that way you'll always get the right film for the one you searched for

[Kyle123]

Maybe something like:

[Eno]

The handling of the zero nodes work really well, I'm looking forward to incorporating it into the original sheet.
I agree that running this each time I add a film would be the best, as soon as I've run it on the list of films I'll change it to automatically populate the fields as I enter each new film.
Or at least that's the plan, it may take me a while to get it right.
Should we mark this thread as solved or closed or something, is that something you can do?
Maybe just leave it open for another day until I've got this finished!

Thank you for all your help, I really appreciate it, although I live in New Zealand I'm originally from the UK & I had installed the large address reading sorting machines at Royal Mail Leeds back in the late 90s!! Now I'm semi retired, & I run this from home www.markssupplies.com

[Eno]

I've just seen your last post, OMG.
Does this mean I have to learn all about User Forms, I'm not sure I'm ready!!
But I'll give it a go.

[Kyle123]

Thank you for all your help, I really appreciate it, although I live in New Zealand I'm originally from the UK & I had installed the large address reading sorting machines at Royal Mail Leeds back in the late 90s

I bet the weathers a little nicer there, right now we're having thunder storms! I work right next to one of the sorting offices in leeds actually.

Leave it open if you like. Have a look through the userform example, I've deliberately tried to make it as straightforward as possible, although the bit where it inserts the data into the worksheet may be a bit confusing!

I'm happy to explain how any of it works

[Eno]

It's winter here in NZ but still ok, all we have is a wood burning fire that we use maybe 3 times a week during the winter so that gives you an idea of what it's like.
I'm glad we left it open, the handling of the zero nodes when submitting an unknown film causes a problem with the __MyMovies.xlsm
I haven't looked into it too much because I've got bogged down with sorting out my list of films. I used the API thingy to automatically go through my entire list of films, although getting this far took me hours. It became evident that there were many titles that were not found, this was because of numerous reasons, bad spelling, formatting etc & all kinds of crazy things with film sequel titles which I've now sorted, but I'm still left with just over 100 titles that are documentaries or TV series which are not movies so they are not recognized on a movie database which makes sense.
I hadn't realized yesterday that the userform was actually set up to work, I kind of thought it was just an idea for me to try & sort out, so thank you very much for that. It looks much more professional and it's definitely something that I couldn't have easily done on my own.
To sum up so far, the userform & the API thing is fantastic, it took approx 17 minutes to grab all the data for over 1300 films, wow!!

[Kyle123]

Glad you're making progress

the handling of the zero nodes when submitting an unknown film causes a problem with the __MyMovies.xlsm

Ah yes, I should have allowed for that, change this:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

As for the TV stuff, I had a cursory glance for an API/Website to combine with what we've got and nothing stood out as returning all the data you're after. TheTVdb looked promising, but doesn't return a description per series. If you find a source that gets tv stuff, let me know and I'll add it to the above

[Eno]

Well I guess this project has come a long way since it's dodgy inception using slow web queries & bad VBA.
It now looks & feels like a proper piece of software, thanks Kyle123.
I've made a few simple changes to it today, the user form is larger & it's column widths adjusted, all the data has been inserted, it also sorts the films alphabetically after each entry & resets the search filters & saves when the workbook closes.
Just a few bits that I'm not sure on, is there a way of getting the film links to work by a single click, with a bit of clicking around it is possible to get them to work as hyperlinks but I'm sure it's something fairly simple, any ideas?

As for the TV & documentaries not working, I had thought at the time that it didn't matter too much but I'm now thinking that it would be good to get them to be at least recognized so they can be submitted, at present I have to add them manually along with copy/pasting all the info from web sites. A description of these would be good but it's not necessary, so how easy would it be to add another API link, so instead of exit sub it would then try another API link before giving up? All the TV & documentaries are already in the sheet with either the words TV Series or documentary in the description if that helps. I've attached the latest version.

[Kyle123]

Yes, we can do that but you'll need to find a source of data for the tv series.

For the hyper links, you just need to insert a line into the code so it looks like the below:

Please Login or Register  to view this content.

[Eno]

This TVDB link seems to provide all the info we require although a description is not completely necessary for TV shows:
http://www.thetvdb.com/api/GetSeries.php?seriesname="gold Rush"
Although working with TV shows & series names, numbers, episodes etc could be tricky, I went to the TVDB page but I couldn't get any info on Black Adder which raised more questions than it answered. I also tried to sort out the API thing myself with TVDB but to be honest it was way out of my league to understand how to do it. I had thought of calling the TVDB API only if the themoviedb couldn't find anything but most of the time if it can't find the film it's because of spelling or some other minor problem. So I was thinking of adding the TVDB as a separate button.
However it's not something that I have the skills to do myself so I'm happy to go for what ever is the easiest, maybe a button on the user form that defaults to movies but can be switched to TV shows if necessary. Any ideas?
The hyperlink code was spot on, thanks, I would have never have gotten this far by myself.

[Kyle123]

A tv database with no black adder?!?!?!?!?! That site should be shut down immediately!

Ok, we're getting somewhere, I've adjusted the code in the userform so that it uses the following logic. If no movies are found, you are prompted whether you would like to search tv, if yes then a tv search is made, if no then the listbox shows no results.

I've added a button to the form called btnSearchTV so you'll need to add this also - this allows for just searching TV series.

This is the code for the button click that you need to add.

Please Login or Register  to view this content.

This is the TV Show function that you need to paste into the userform:

Please Login or Register  to view this content.

And the no movies found bit in the original function needs changing so that it looks like this:

Please Login or Register  to view this content.

NB I've made some assumptions about the links for the tvdb, the ones I've tried work, but it's possible some might not.

Let me know what you think

[Eno]

Wow, the TVDB works great, unfortunately there are a few other problems.
First,I was unable to shut down the site, they do not deem Black Adder as important enough for such drastic action.
Second, there seems to be a small problem with the original no movies part with GetFilmData = GetTvData(strMovie)& the message box doesn't appear.
I will post back if I fix it but with a quick glance I couldn't see anything obvious.
Apart from that small glitch the concept is spot on yet again.
I've attached the excel containing all updates.
Thank you for all your help.

[Kyle123]

Ok, I missed out an end if, it the code should be:

Please Login or Register  to view this content.

Also, change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

Then everything should work

[Eno]

It's perfect. You are a true guru.
What can I say, I've learned loads & although I understand what the code does I couldn't write the majority of it myself so I'm in your debt.
I was thinking for a future small project to have an icon on my PC desktop that when clicked would delete the contents of 2 folders including the recycle bin, I have to delete the contents every day so it would be a handy thing to make. But what sort of leap do i have to make from doing a bit of VB in excel to doing something like this?
PS, I also learned that BlackAdder is one word!!
Thank you from a very grateful New Zealander.

[Eno]

It's a bit off topic bit I guess I've answered my own question on how to create an icon on your PC desktop that will delete the contents of 2 folders.
I typed the code below into notebook & saved as a batch file onto my desktop as DelTemp.bat
The /q is for quiet mode which means the batch file does not ask for 'are you sure Y/N', so I just double click this icon & it just works.
I really thought there would be more to it than this!

Please Login or Register  to view this content.

[Kyle123]

Glad you got everything sorted

As you've found out, your second request was substantially easier than your first

Thanks for the feedback and rep.

[Cutter]

@ Eno

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.