CODEWITHSUNDEEP
vbaexcelsynchronization
user1871453
user1871453

Reputation: 119

excel vba synchronization issue

I'm using excel to do some data analysis work. the scenario is like this: I use some excel plug-in componnets to get data from remote site. for example, put =get_the_latest_data(ID) in A1, then this function would be called and ID's data would be filled in A2 to A11. My goal is 

for each id in id_array
    sheet1.range("a1") = get_the_latest_data(id)
    create a new sheet named id
    save data from a2 to a11 to thi new sheet
next

My problem is it would take some time for get_the_latest_data function to get all the data from remote. So, in my code the new sheet couldn't get the right result. I tried to use application.wait before copy data from sheet1 to the new sheet. but it didn't work.

Is there any way I can make sure that data is copied to the new sheet after the wanted data is got from remote? btw，I do not have access to the source code of get_the_latest_data.

Upvotes: 3

Views: 2312

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

It seems to be a typcal case with a third party data vendor where their generics data-requests are ashynchronus which is not very well exposed to the users. In such cases you may need to check with your vendor for the behaviour of that method you are calling. There has to be some boolean type of a variable to be set at least to choose to either have a asynch or synch request/retrieve.

Eg. if you are sending one index ticker to get top 10 members, then this method is right now collecting all your index tickers and shooting you back with all the data. So check if the returned data could be split e.g. it's a multi-dimensional array that you could separate the data into the sheets based on the id. Else talk to this application's API developers as DoEvents might be too weak to handle this.

I suggest you to use anarrayList/collection item to hold the data returned from your method for each id. Once it's completed you can start creating sheets as you already have caught the data locally.

Dim al as Object

Set al = CreateObject("System.Collections.ArrayList")
for each id in id_array
    al.add get_the_latest_data(id) '--possibly returning a variant
next

'--for each item in arrayList then you create a sheet by outputing the data    

for i = 0 to al.Count
   '--Create new sheet put your code here
   '--transpose the data 
   Sheets(i+1).Range("A2").Resize(UBound(al(i))) = Application.Transpose(al(i))
Next i

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17475

Normally, VBA only executes the next step after the former step is fully finished. That is unless the call triggers some background action (e.g. to Refresh a data connection that has "Background refresh" enabled.

You can do the following to solve the issue:

Disable background refresh: If you look in the properties of the connection, unselect "Enable background refresh"

Wait until refresh is finished with data query specific check: Modify your code the following way:

    Range("A11").Clear               'To enable the check
    subCallToTriggerDataLoadHere
    While Range("A11").Value  ""   'Or any other specific check to see that your refresh is finished
        DoEvents
    Wend
    ...

Use the AfterRefresh event of a query: See this post for details

Upvotes: 3

Related Questions