Is there a way to disable asynchronous functions?

Support
#1

I've been using the latest Beta versions of 64-bit SeoTools and have been mostly satisfied with the product's development. However, the new asynchronous functions work great for small sets of ad hoc queries, but they don't behave well within VBA scripts.

I've developed many projects using SeoTools and VBA to automate data collection and reports. Most of my scripts have to loop over hundreds (sometimes hundreds of thousands) of URLs to 1) apply formulas, 2) recalculate the active sheet, then 3) flatten the formulas.

Unfortunately, the async functions don't seem to report their calculation status to Excel as previous versions of SeoTools always have. When the scripts recalculate the active sheet, they proceed to copy and paste as values the current set of formulas, but those formulas haven't completed or are still in the #GETTING_DATA status. I've tried various combinations of recalculates, doevents and sleep delays (even for 15+ minutes!), but nothing has worked as of yet.

I just tried the latest v6.0.3 general release, but still no improvement, so I'm forced to continue using v5.1.4 since it's the latest reliable version.

Is there any way to disable asynchronous mode and reinstate the previous behavior, perhaps within HttpSettings() or even within SeoTools.config.xml?

Cache issues in SeoTools 6.0.x when used in VBA Macros
#2

Hi,

I also have issues with 6.0 but I don't have v5.1.4 anymore :confused:

Could you send it to me at samuelviet@gmail.com please?

Thanks a lot!!!

#3

I have sent you the 5.1.4 version.

/Victor

#4

Awesome!

Thanks a lot :slight_smile:

#5

I will look into this and see if there's a way to disable the async.

Can you send me an simple VBA example?

#6

Here's a simple VBA sub that gets HttpStatus() for URLs in column A, then pastes the results as values:

Sub StatusCheck() Dim row As Integer Dim col As Integer row = 2 col = 2 While ActiveSheet.Cells(row, 1) <> "" ActiveSheet.Cells(row, col) = "=HttpStatus( A" & row & " )" ActiveSheet.Cells(row, col).Copy ActiveSheet.Cells(row, col).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False row = row + 1 Wend End Sub

It works fine in older versions of SeoTools without the async functions, but only copies the #GETTING_DATA message in v6.0.x.

#7

Thanks. We're working on a solution for this.

XPathOnUrl inside and out of UDF
#8

Hey, Niels. Just checking in on this issue. Any ETA?

#9

Try the this: Help beta test SeoTools 7.0 - Lots of new Connectors!

To activate open SeoTools.config.xml and set this:

To "true".

VBA example for CsQueryOnUrl:

Sub Foo

Dim row As Integer
Dim col As Integer
row = 2
col = 2
Dim range As Variant
Dim addr1, addr2 As String

While Sheets("Errors").Cells(row, 1) <> ""
    
    Dim xArray() As Variant
     
    xArray = Evaluate("=TRANSPOSE(CsQueryOnUrl(A" & row & ", ""a"", ""href""))")
    addr1 = Sheets("Errors").Cells(row, col).Address
    addr2 = Sheets("Errors").Cells(row, col).Offset(0, UBound(xArray) - 1).Address
    
    Sheets("Errors").range(addr1 & ":" & addr2).Value = xArray
    
row = row + 1
Wend

End Sub
1 Like
#10

That's a perfect solution - my VBA scripts now run as expected.

Thanks for the quick turnaround!!

#11

Hey Victor - could you forward the 5.1.4 version to me as well if you still have it - am trying to upgrade but finding rewriting all my scripts pretty rocky...it would be much apprecated - thanks! - tedives@gmail.com

#12

Hi Ted,

Have you tried the async disabling method? It makes SeoTools handle the functions the same way as 5.1.4. Here is a guide:
https://seotoolsforexcel.com/working-with-vba/

Also, what works really well for me with VBA is to output SeoTools formulas "as formulas" then copy paste the ranges as values after completion. An easy way to know when the calculations are complete is to use a counter which counts number of cells containing "Loading_Data".

#13

Victor - thanks for the help, it was invaluable.

Anyone else upgrading from old version though, here are two things you might watch out for:

Old code, doesn't work anymore:
=Dump(GoogleAnalytics("ga:123456789","ga:visitors","1/1/2017","5/31/2017","ga:week","","","",1,52,FALSE,FALSE))

New code, that works:
=Dump(GoogleAnalytics("ga:123456789","ga:users","1/1/2017","5/31/2017","ga:week","","","",52,FALSE,FALSE))

The two changes:

  • GA deprecated "visitors" in the API and switched it to "users"....somehow I didn't get the memo on that :wink:
  • For some reason, specifying that 1:52 range isn't necessary anymore (for 52 weeks), eliminating the extra "1" parameter completely did the trick.

Thanks!!!! - Ted

#14

Hi Ted,

Thanks for the feedback, great suff! :smile:

I get the same value for both users and visitors, but I trust you when it comes to GA. We'll look into it and try to get a more updated library of requests.

bild.png1261×160 12.5 KB

I'm not following you on the second point. Can you explain that in more detail?

#15

I think what I mean is, 6 years ago the =GoogleAnalytics connector had a different number of parameters, or a different set of them. I don't remember that DEFAULT being at the end, and I used to have to specify 1,10 for 10 results, but now the "1," is extraneous. I think one parameter was dropped and another one added (the "DEFAULT" one is optional though, it looks like).

It would be helpful if the connectors had a little help documentation as far as - what are the parameters? Current approach is to just insert the formula and examine what came back, usually you can figure it out, but explicitly explaining the parameters somewhere would speed the process of figuring things out.