topic Re: As promised a real excel 2000 macro thread: Control loop for data refresh in BackOffice Products

As promised a real excel 2000 macro thread: Control loop for data refresh

Steven E. Protter — Tue, 30 Nov 2004 22:25:43 GMT

I have a series of spreadsheets that produce data for a website. They pull some data on some stocks and publish the data in xml format on the website.

The process is simple

Refresh the data then record/save the data in xml format.

Here is the refresh/save macro code:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 11/30/2004 by YEVKA
'

'
ActiveWorkbook.RefreshAll
Application.Run "model.xls!modelpublish"
End Sub

Problem: The refresh takes a little time. If I run this code with run macro the publish code runs and the refresh code gets interupted.

I'd like to build some kind of control loop around the refresh so no more code executes until the control loop won't let the next line of code execute.

Here is what I want in English language:

ActiveWorkbook.RefreshAll
until refresh is done
do
sleep 1
done

if successful (return code 0 in unix-speak) then
Application.Run "model.xls!modelpublish"
fi

I've used Unix control structures.

The current process is manual. That requires me to log onto a computer and do work.

Two goals. Certain Jewish holidays prevent me from operating a computer. I'd like the site to get its update anyway with a windows scheduled job or whatever.

Second goal: I'd like the darn website to get updated even when I'm busy.

This publish part is great. It saves the data, fires up an ftp script and really does a very nice job.

Bunny for some macro code.

Have at it.

SEP

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Ganesh Babu — Wed, 01 Dec 2004 07:57:10 GMT

Hi SEP,
Is refreshall a function or procedure inside your current workbook??

Ganesh

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Steven E. Protter — Wed, 01 Dec 2004 08:59:03 GMT

Ganesh.

Its a built in function part of the external data tools that ship with excel. I get the code by doing macro record and pressing the refesh button.

If need be I can post the whole spreadsheet.

SEP

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Ganesh Babu — Wed, 01 Dec 2004 14:16:16 GMT

Hi SEP,
I read some more documents about this refreshall.. and looks like this command sometimes works as an asynchronous (meaning excutes the next steps before this is completed succesfully) command.

So as an workaround.. I was thinking to check for a value in a particular cell which gets updated by refreshAll.. so create a loop and check whether the cell is updated with the particular value and once it is done then we can run the publish procedure..

if this is ok let me know and i can write a script for that..

Ganesh

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Steven E. Protter — Wed, 01 Dec 2004 17:00:32 GMT

Sure this is okay.

I'd like to know how to write a control loop regardless. If you need the spreadsheet, indicate and I'll upload and attach it.

SEP

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Steven E. Protter — Thu, 02 Dec 2004 23:50:49 GMT

Um Dexter?

Bunny alert!

Dexter Filmore???

Regards,

Steven

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Steven E. Protter — Tue, 07 Dec 2004 12:15:07 GMT

This article seems to indicate that if I set background refresh to false, that my code will wait for the query to finish prior to moving on to the next line of code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffsol02/html/odc_WellkeptSecret.asp

I'm going to try it and see what happens. At this point I have no idea how to do this and will award a bunny for step by step on how to do so.

SEP

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Ron Kinner — Tue, 07 Dec 2004 19:31:36 GMT

Since you are not getting much of a response I'll put my 2 cents in. Have you tried just making two separate macros and then calling each from your main macro?

Something like:

Sub Macro4()

ActiveWorkbook.RefreshAll

End Sub

Sub Macro5()

Application.Run "model.xls!modelpublish"

End Sub

I have a few other ideas but would need your Excel program and macros to try them out so go ahead and attach it or you can send it directly to me at
rkinner at att dot net

Most of what little VBA experience I have is in Access but it is about the same.

Ron

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Steven E. Protter — Tue, 07 Dec 2004 19:52:21 GMT

Ron,

Thanks for posting in.

I have discovered that each data area in the spreadsheet has a property for background data refresh. It apparently defaults to enable.

I just went through a spreadsheet disabled it and got a macro to run and it waited for the data to update before going to the next step.

So apparently a control loop, may not be necessary. I'm still looking to make further improvements though so I'm attaching my spreadsheet.

I'm wondering now how to program the Windows scheduler to kick the spreadsheet once a day running a particular macro.

I can now actually set up the websites stock indices to update themselves during the day, though that will tie up a computer.

I'm wondering whether the better idea is to use a control loop to run the collect and publish functions, leaving the spreadsheet open all day long.

Here is the spreadsheet I just fixed. This is not closed and I'm handing Ron a solution quality pointage, because you met the original question critera. Thanks for letting me get to point assignment prior to Merijn's statistics.

SEP

Re: As promised a real excel 2000 macro thread: Control loop for data refresh

Ganesh Babu — Wed, 08 Dec 2004 12:22:03 GMT

Hi SEP,
I am really sorry for not sending the control loop before.. here is one which u can use it..

Sub controlloop()
'Test
Dim strdatetime As Date
Dim curdatetime As Date
Dim intdiff As Integer
strdatetime = Now
intdiff = 1
Do While True
curdatetime = Now
intdiff = DateDiff("n", strdatetime, curdatetime)
If intdiff >= 1 Then
strdatetime = Now
'ActiveWorkbook.ActiveSheet.Cells(1, 1).Text = curdatetime
msgbox curdatetime
Exit Do
End If
Loop
'yyyy Year
'q Quarter
'm Month
'y Day of year
'd Day
'w Weekday
'ww Week
'h Hour
'n Minute
's Second

The above loop is an infinite loop which will exit only if "Exit Do" statement is called. So u can right a condition when u want to come out of the loop..

Also i have put in a condition for running the data refresh & publish to for every minute (assuming they complete before 1 min)... u can change it to hours or days based on your frequency..

hope this helps..

Ganesh