Converting Private Sub to Public Function

[ctackett6407]

I have been on a mission to help automate a task I do every morning.

A person created an Access application years ago that we still use to compare two databases and kind of run all kinds of queries and tables. When I open the application I click a "Run All" button and then it just goes off and running and completing the list of commands inside the modules.

I've asked questions and poked around and found that I might be able to get this to work with Task Scheduler if I create something as a Public Function , add a macro somewhere, and then use Task Scheduler to invoke the access application with some type of parameter.

I'm been trying to figure out where to start on this as I reviewed the application the person created and everything in it is Private Sub.

Do I have to convert this or can I convert this? I'm hoping I'm going to be told to "run a replace all for Private Sub, because it's just that easy!"

 

[JLGWhiz]

@ctackett6407 - You already had all the elements of code that you needed to do what you want. All you needed to do was move your 'Run All' button code to a public code module, remove the 'Private' from the macro name (and for cosmetic purposes, maybe remove the '_Click'), then put the macro name into the Windows scheduler without the parenteses.. Your button code will serve as a batch file to initiate the other macros that do the various tasks.

 

[ctackett6407]

@JLGWhiz let me give that a try. I'm literally learning as I go and reading anything online that I think fits into what I'm trying to do. Thanks for all the help folks as I try to get this working.

 

[GWteB]

@ctackett6407 put the macro name into the Windows scheduler without the parenteses.

Just an observation... how would Windows Task Scheduler know what file to open and how would WTS be able to launch the desired "public" macro?
WTS accepts almost everything you type in without an error message, but if it cannot be executed then nothing will happen.

 

[ctackett6407]

I'm going to have to reread this thread and regroup, I got myself lost in what I'm doing. lol

but I do thank people for the help.

 

[JLGWhiz]

Just an observation... how would Windows Task Scheduler know what file to open and how would WTS be able to launch the desired "public" macro?
WTS accepts almost everything you type in without an error message, but if it cannot be executed then nothing will happen.

If you go back to the beginning of this thread, the OP states that the button macro then calls other macros which do all the work. So the only code the scheduler needs to call is the button macro, converted to a public procedure in a public module. That macro will then call the other necessary procedures to do the tasks.. The problem the OP is having is getting the right procedure name in the scheduler and having that procedure in the correct code module so the scheduler can find it. There is not a problem with the code, the problem is getting the scheduler to call the code. The OP did not understand what comprises a procedure name and the nuances of _Click events, etc. But once they get that straightened out, it should work.

 

[GWteB]

I understand what the OP wants, but you simply cannot enter a macro name in WTS. You can (because you can type anything you want) but the macro will never be executed. However, you can schedule a VB script in WTS and run the macro using this VB script.

 

[xenou]

Example of running an access VBA function using WTS:

Auto Trigger MS ACCESS Code

hey there, I've recently switched to window 7. previous on XP i used task scheduler to open a MS Access database at a particular time daily where there was code attached to a form (which was opened when database opens) that runs code if opened at the correct time (when the scheduler opened...

www.mrexcel.com

See Post 6.

Other ways of using WTS are of course possible. This could probably be adapted to run a macro pretty easily (my example runs a VBA Sub or Function in a public module).

 

[JLGWhiz]

I understand what the OP wants, but you simply cannot enter a macro name in WTS. You can (because you can type anything you want) but the macro will never be executed. However, you can schedule a VB script in WTS and run the macro using this VB script.

Well, I won't get hung up on semantics. If the user follows the menu to create a task, then the task should run when scheduled. There are tutorials on the web to show how to create a task. And that is the part the OP is having difficulty with. I am sure he would appreciate any help along those lines.

 

[JLGWhiz]

This is a cut down version of the turtorial for adding a VBA macro to the scheduler. You can try it and see if it takes.

1. Get your macro into the correct code module and correctely named.
2. Open task scheduler (Control Panel>Administrative Tools>Task Scheduler)
3. Select Create Basic Task and type a name for the task (not macro name)
4. Click Next button
5. You can now select an exact time for the macro to run or "When I Log On"
a.Select 'Daily'
6. Click Next button
7. Enter the date and time to start running the macro, It will then run every day there after.
8. Click Next button
9. Select 'Start a program'
10. Click Next button
11 Enter the macro name enclosed in quotation marks ( "macroname") in the Program box.
a. In the Add arguments box enter the full path to the file holding the macro in quotation marks.( "C:\lev1\lev2\myFile.xlsm")
12. Click OK
You won't be able to locate the macro using the browse button at step ll because it only goes to the file folder level for Excel files. You haveto type in the macro name.

 

[ctackett6407]

Thank you for this, I'm revisiting it today as it got myself confused and I had to step away and work on something else. I really thank you all for replying.