topic Re: Repeat function in AutoCAD VBA in VBA Forum

Re: Repeat function in AutoCAD VBA

kh.teow — Mon, 13 Sep 2021 11:52:08 GMT

I've written a VBA code to connect to MySQL to get information to do some calculation and drawing, and then export the results back to MySQL again.

I would like the code to run automatically on a fixed time interval without human intervention but was unsuccessful. I have tried the following:

1. Enabled the Microsoft Excel Object Library in Reference so that I could use the Excel command "Application.OnTime Now + TimeValue("00:00:01")" ;

2. Using AutoCAD Core Console with a script file that include the commands -vbaload and -vbarun, but the command returned "Unknown command "-vbaload"". My intention is to use accoreconsole.exe to run the script file and use a Windows batch file to run an infinity loop on the accoreconsole.exe every 5 minutes.

Is there any workable way out there which is easily implemented and maintained?

Thanks,

Best regards,

KH Teow

Re: Repeat function in AutoCAD VBA

norman.yuan — Mon, 13 Sep 2021 12:44:53 GMT

Well, AutoCAD core console does not support VBA, so you cannot use it with VBA code.

Using another app's feature (Excel's Application.OnTime, as you mentioned) is bad idea, to say the least.

If you have to use VBA for certain reason, VBA has built-in function - Timer, which you use to record a time period elapsed and run particular code at certain interval.

Re: Repeat function in AutoCAD VBA

kh.teow — Mon, 13 Sep 2021 13:02:11 GMT

Hi Mr.Yuan,

I have tried using timer command but what it actually does is to count the amount of time that the user responded instead of counting how many seconds have passed.

What I'm looking for is something to this effect, if it's written in Windows batch file:

echo off

:loop

echo Hi, how are you?

TIMEOUT /T 5 /NOBREAK
echo off
goto loop

The objective is to check with MySQL on a fixed interval to see if there are new jobs given in the database table (of course I don't mean "hi, how are you", but I intended to run accoreconsole /s abc.scr -en US).

Could you correct me if you see anything wrong in my understanding?

Thanks a lot,

Best regards,

Re: Repeat function in AutoCAD VBA

norman.yuan — Mon, 13 Sep 2021 14:19:47 GMT

Firstly, I do not know what you need AutoCAD to do, but assume it would process one or more drawings every time a new job item is detected in your database. I'd think using AutoCAD core console would be better choice with proper notification mechanism, likely a service time of app. But that would be out of topic of this forum and beyond the question you asked here.

Regardless what exactly your business process, VBA's Timer CAN provide a way to execute VBA code in a given interval. You just need a bit programming (we are programmer here, eh?). Following code shows how to do in VBA:

Option Explicit Private Const PAUSE As Integer = 5 '' Seconds for Timer to pause Private Const INTERVAL As Long = 20 '' 30 seconds Private startTime As Date Private start As Integer Public Sub DoWork() Dim go As Boolean Dim currentTime As Date Dim timeDiff As Long startTime = Now Do go = True start = Timer Do While Timer < start + PAUSE DoEvents Loop currentTime = Now Debug.Print currentTime timeDiff = DateDiff("s", startTime, currentTime) Debug.Print timeDiff If timeDiff > INTERVAL Then '' This is where the work is done in every a while '' determined by const INTERVAL, I set it for 20 seconds '' you can set it to 3000 (5 min) go = DoSomething If go Then startTime = Now End If Loop While go MsgBox "Repeating work has been cancelled!" End Sub Private Function DoSomething() As Boolean Dim msg As String msg = "Do work here... done." & vbCr & vbCr & _ "Do you want to it again?" If MsgBox(msg, vbYesNo + vbQuestion) = vbYes Then DoSomething = True Else DoSomething = False End If End Function

I do not think using VBA with its Timer is a very good solution to your business need, but again, the code here is only meant to show how to use Timer to run a piece of code with given interval. Run the code and watch the immediate window for the output from Debug.Print, you can have clear sense how the code works.

Re: Repeat function in AutoCAD VBA

kh.teow — Mon, 13 Sep 2021 14:40:49 GMT

Thanks Mr.Yuan for your help!

Re: Repeat function in AutoCAD VBA

kh.teow — Tue, 14 Sep 2021 15:30:52 GMT

Hi Mr.Yuan,

To be honest, I'm not really a programmer but a mechanical engineer who has had a term of training in coding Turbo Pascel some 25 years back. The line that saved the day was:

Private StartTime as date

from here, I could just manipulate the time interval via the date function. Last time never paid attention to any date function as most of the coding was done for non-business related process, and date was rarely used as a reference. I know it must have looked stupid on my side to have missed out such a basic thing but then again, what can I say? 🙂

Thanks for you help!