scheduling a macro to run causing error

[penfold1992]

Hello All,

so I have scheduled a macro using the following settings within task scheduler:

Actions: Start a program
Program/Script: "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"

Arguements:
"\\firo\Testing\dbname.accdb" /x PropReport

and the macro I am trying to run(PropReport) extracts a query and then saves it to the hdd.

the macro contains:

Option Compare Database
Public Function PropReport()
DoCmd.TransferSpreadsheet acExport, 10, "Main_Query", _
    "[COLOR=#0066cc]\\firo\Testing\"[/COLOR] & _
    "Reporting Test.xlsx", -1
    
Application.CloseCurrentDatabase
Application.Quit
End Function

so...
on scheduled time, the database opens but it gives me the following error:

Microsoft Acces cannot find the object 'PropReport.'

if 'PropReport' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.


anyone have any ideas?

 

[pr2-eugin]

Have you made extra sure that this function "PropReport" is in a Standard module and not in a Form module?

 

[penfold1992]

Have you made extra sure that this function "PropReport" is in a Standard module and not in a Form module?

well... I guess that is a good question because I don't know...
Within VBA I right click, created a module (not a class module) and its called "AutoRun"... I didnt want to call it AutoExec to execute on open, it was just a placeholder name to run automatically lol.

also, when I go into Access, in the "Shutter bar" thing on the left hand side, I can select "Filter by group" - "Modules" and it is in here... I am not sure what that means though.

I am not sure if that answers your question...

 

[pr2-eugin]

Well you have done it right, Created a Module (not a Class module) named it different than the function name. And you used the AutoExec macro to call this function?

 

[namliam]

If you can select it from access it is in a standard module.

/x runs a MACRO, that is a MACRO not a function.

Make a macro to call from the command line, if you already have an autoexec macro, you shouldnt need the /x option

 

[penfold1992]

Well you have done it right, Created a Module (not a Class module) named it different than the function name. And you used the AutoExec macro to call this function?

Nono, I didnt want to use the AutoExec macro because this would run when I open the db, which I dont want... I created a module and called in AutoRun.. I just named the module that, I dont actually want it to run on startup.

I then entered into the arguments section on the tasker
"location.accdb" /x PropReport

the first argument is the location, the flag "/x" runs the macro after it.

 

[namliam]

MACRO is not a function, a macro can run a function.

Create a macro to call using /x, that calls your function.