0

I'm currently using VBA to automate pulling of large data-sets. However, it often happens that either

  1. the sub simply gets stuck and does not update any more/connection breaks (application is still responsive) or
  2. the application becomes totally unresponsive.

Is there a way to implement a time-out function which automatically closes the application after say 15 minutes elapse?

In other programming languages, I would have solved this using multi-threading. But not sure if something similar is feasible in VBA given that it is entirely single-threaded.

The basic algorithm I employed to automate the data looks like this:

Sub automateFetchData()
Dim requestTable As Workbook

Set requestTable = Workbooks.Open(requestTablePath)
Run ("'" & requestTable.Name & "'" & "!fetchData")
' This is where the application starts fetching data and occasionally gets stuck
debug.print "Data retrieval complete"


End Sub

For those familiar with Thomson Reuters Datastream, I am using request tables and the pre-programmed macro within the request table file to connect and fetch data from the datastream server. Therefore, I unfortunately do not know/understand in detail what is happening when I run:

Run ("'" & requestTable.Name & "'" & "!fetchData")

I just know that once I call it, a progress bar appears looking like this: enter image description here

Which then transitions to: enter image description here

If the data retrieval is successful, the progress bar disappears, output is pasted to a worksheet and the code above reaches the 

debug.print "Data retrieval complete"

Statement.

If it is not successful, it just freezes with the progress bar from the second screenshot still open and running. At this point, I would want the time-out to kick-in and exit the function which called 

Run ("'" & requestTable.Name & "'" & "!fetchData")

Please excuse me for being so vague in the description of the issue. I'd be glad if someone could nonetheless help me out! Always happy to share more info where I can. Cheers!

braX
  • 11,506
  • 5
  • 20
  • 33
rajomato
  • 1,167
  • 2
  • 10
  • 25
  • 1
    So you're opening your own workbook, and at some point your code calls `automateFetchData`. This sub then opens another workbook with it's own built-in macros, of which you're requesting the `fetchData` method to execute. Unfortunately, it's the other workbook's macro that is hanging, not yours. And even more unfortunately, VBA doesn't have any multi-threaded process calls built in. So your workbook will hand until the second workbook's code finishes. The only possible "out" I can think of is if the second workbook's code makes calls to `DoEvents` which would let you issue a break of some kind – PeterT Oct 08 '18 at 18:11
  • 1
    My other (untested) idea is an external timer that would somehow figure out the second workbook process has hung and then will kill the process from the Task Manager. – PeterT Oct 08 '18 at 18:12
  • Thanks for the comment! I guess it's as you said: I'll have to kill the application from outside of Excel. I tried to trace the functions which are called by the other workbook and it seems to be calling come method within a COM Interface. Unfortunately, I have no clue on how to see what gets executed within that interface .. – rajomato Oct 09 '18 at 15:20
  • Did you consider using the API instead of Excel? https://developers.thomsonreuters.com/eikon-apis/datastream-web-service – PythonSherpa Oct 12 '18 at 07:44
  • @Hoenie, yes I did. Unfortunately, the API only allows downloading 10 million datapoints a month. Given the size of my sample, that would be too little, which is why I chose the method using request tables. – rajomato Oct 13 '18 at 12:24

0 Answers0