Problem with VBA EXCEL and DUMP (Twitter Connector)


#1

Hello everybody.

I’m stuck on something and i cannot figure out how to resolve it.

I got some code that calls a Dump formula (using Twitter Connectors). The formula works well if i execute the code step by step (using f8). But if I run the code using F5, it fails, because the code goes faster than Dump.

I tried using Application.wait., using calculatestate, DoEvents, and a lot more of stuff. Also tried disabling and enabling the Addin, Screenupdatinng, and even a Wait time for an hour. I also tried what you can see in the help webpage http://seotoolsforexcel.com/working-with-vba/, enable and disabling Async and nothing works. I’m really lost.

It’s curioius because if I use and STOP command right after the dump, the worksheets calculates and then the code works, but the problem of using a STOP command in VBA is that an user needs to be in front of the computer to keep the code running and I want to avoid that. I want the code the code to run complete. I use DUMP but I’m not fetching a huge amount of info (around 100 rows of info, and just 1 column).

If you need more information, I’m just using connector Tweets search, and my query is really simple, because is “from:accountname since:2017-11-26 until:2017-11-27” and it’s limited to 150 results.

Is really frustrating to see that the code works PERFECT if I use a STOP command, but i cannot do it that way because i need it to be 100% auto.

Sorry about mi bad English. I hope anybody can help me, because I have no idea of what else to try.

Thanks in advance.

Foxfire


#2

Hi Foxfire,

Can you email the xlsm file to victor@seotoolsforexcel.com?


#3

Hi Victor.

Sure. I’ll mail you a clean version of that part of process, because my actual Excel file is linked to some databases.

Thanks!


#4

Hello everybody.

I fixed it. Not sure why it works, but now my macro works. PReviously, i tried with

Doevents

And also

Do Until Application.CalculationState = xlDone
DoEvents
Loop

but this not solved my problem. Finally, i used both of them right after every DUMP in my code, and now it works!

DoEvents
Do Until Application.CalculationState = xlDone
DoEvents
Loop

I don’t understand why both commands combined work better, but my code works for now, and I hope for this to be the final solution.

Anyways, if any of you find any other solution, I would love to hear about it.

Thanks.

Foxfire


#5

Very nice. Does that code make VBA wait until Dump and the Twitter functions have finished? I thought VBA couldn’t identify the fetching progress when SeoTools ran on async.


#6

Actually it’s true. VBA cannot identify the fetching process if runned async. You have to disable it adding true to config file. I had it that way all the time during my macros. And doing it that way, VBA is slower but my macros worked, until yesterday, that i don’t know why it did not until I used Doevents twice, what makes no sense for me.

But right know, the code is working. It takes a lot of time, indeed, but is 100% auto.


#7

have you tried to set as a value the result you get each time you download the data? I mean something like this:
[A1] = dump ...
DoEvents
[A1] = [A1] .value.
This works perfectly for me