VB code to be run repeatedly

[03021979]

Let me explain what the matter is. I have a workbook with lots of macros. I work in logistics department and we send dvd films. Every title has its own Work Order Number. We have some Work Orders not finished or not started at the moment of sending others. The problem is to write a code which checks Work Orders status every minute or so. A code of refreshing Work Orders status is written. The matter is if VB can run a certain macro every minute without pressing any buttons.

 

[CautionMP]

In Excel you have to establish a timer with a callback function. Here is an example: How To Create a Timer Event Using the Windows API Functions

Hope this helps,
CMP

 

[03021979]

thanks a lot! But this macro to be run repeatedly in the background, that means that other macros can be run by a user and this macro to be run repeatedly is running irrespective of the others. Is it possible to run 2 macros in the same time?

 

[CautionMP]

Yes. The callback function runs in it's own space (outside Excel) until the timer event is triggered.

You will need to play with this but you may want to start/stop the timer as your other macros run/end to eliminate goofy results related to data updates during macro execution.

CMP

[small]P.S. This almost sounds like a job better suited to Access, external data source, periodic updates...[/small]

 

[03021979]

But is it possible to insert this time repeated macro into my excel workbook (it would run some sql queries) and run other macros in excel? Or only time repeated macro in VB as exe file and macros in excel?

 

[CautionMP]

The example in the link references making a Standard EXE project in Visual Basic.

The code modules in Excel is your Standard EXE project.

In your workbook create a new module and add (copy/paste) the first code block ([tt]SetTimer[/tt], [tt]KillTimer[/tt], & [tt]TimerProc[/tt]).

In the TimerProc prodedure you will need to replace

iCounter = iCounter + 1
Form1.Text1.Text = CStr(iCounter)

with your code/macro that checks order status.

Now add the two global form delcarations to this new module under the [tt]Option Explicit[/tt]

Dim lngTimerID As Long
Dim BlnTimer As Boolean

Now you need to start the timer. You can do it when the Workbook opens, or add a button for the user... Whatever you decide you will need to use a procedure similar to:

lngTimerID = SetTimer(0, 0, 6000, AddressOf TimerProc)
BlnTimer = True

As a last button up step you will need to call [tt]KillTimer()[/tt] before the workbook closes, when a user presses the Stop button (you create)...

Hope this helps,
CMP