Time to execute a Macro or Query

SKV

Active Member
Joined
Jan 7, 2009
Messages
257
I am running a macro having a number of queries and I want to get a report showing time taken to execute each query and the macro.

how can I do this?

it would be nice if it can display results after each execution

Thanks
SKV
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
This tutorial shows you how to create a log table so that you can record start and end times for any process that you need to track.

Denis
 

SKV

Active Member
Joined
Jan 7, 2009
Messages
257
I have around 60 Update queries grouped under 12 macros which are finally grouped under 1 Master macro.
1)So where should I write this code?? (at Master Macro or child Macro etc)
2) can I write this code in Update query?
3) In the regular query, where should it be on the top portion or the bottom?

I am not too access savy so that why asking these basic questions.

Thanks
SKV





This tutorial shows you how to create a log table so that you can record start and end times for any process that you need to track.

Denis
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
The WriteLog code goes into a module (Alt+F11, Insert>Module, Paste then Alt+Q).
You also need to create the table as described.

To run the code you use the Click event of a button. Say, for example, that you want to run qryUpdateMain. Do this:

1. Create a button that will run that query (you do this by dragging a button onto the form in Design view, with Wizards ON, and when the dialog pops up select OPen Query from the Miscellaneous section).

2. Finish the wizard, then go to that button's properties, select the Events tab, and click the ... button at the end of the On Click row.

3. Write in the WriteLog calls, similar to the ones in the tutorial. Now, if you want to run several update queries at once, you can copy and paste those lines directly below and change the description and the query name to suit.

4. When you click the button the update queries will run. Then go to the log table, scroll to the botttom, and you will see the sequence that you just ran, along with the time that each one finished.

Denis
 

SKV

Active Member
Joined
Jan 7, 2009
Messages
257
Thanks, I ran it in a SUB. it worked fine



I have around 60 Update queries grouped under 12 macros which are finally grouped under 1 Master macro.
1)So where should I write this code?? (at Master Macro or child Macro etc)
2) can I write this code in Update query?
3) In the regular query, where should it be on the top portion or the bottom?

I am not too access savy so that why asking these basic questions.

Thanks
SKV
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,985
Messages
5,767,441
Members
425,414
Latest member
chwein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top