Autoexec!!! Please help

[prabhu]

Hello All,

I have created Autoexec() in macro to run some set queries and export to excel on a daily basis at a scheduled time. Everything was good; Since i could not export this data to excel with date in the file name, i made some changes and now its a big challenge for me.
Here is what i did,
I converted Autoexec macro to Visual basic codes and in the Modules - Converted macro, i made some correction inorder to export the data to excel with date in the file name. But now the problem is when i run the macro from Visual basic editor screen i am getting the date in the filename but when i run the same macro from access macros screen i am not getting date in the file name. Can some one help me in this.

All i need is to run autoexec macro in a scheduled time and export the data to excel with date in the file name. Please help!!! < i am not very good in VB> kindly help me!!!

here is my vb code

'------------------------------------------------------------
' autoexec
'
'------------------------------------------------------------
Function autoexec()
On Error GoTo autoexec_Err
DoCmd.SetWarnings False
' 0G Toplevel Discrepancy Report
DoCmd.OpenQuery "0G Toplevel Discrepancy Report", acNormal, acReadOnly
DoCmd.OutputTo acOutputQuery, "0G Toplevel Discrepancy Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\OG Toplevel Discrepancy Report\0G Toplevel Discrepancy Report " & Format(Date, "DDMMMYYYY") & ".xls", , ""
' Contcode Discrepency Report
DoCmd.OpenQuery "Contcode Discrepency Report", acNormal, acReadOnly
DoCmd.OutputTo acOutputQuery, "Contcode Discrepency Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\Container Code Discrepency\Contcode Discrepency Report " & Format(Date, "DDMMMYYYY") & ".xls", , ""
' Fracz Code Discrepency - Report
DoCmd.OpenQuery "Fracz Code Discrepency - Report", acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "Fracz Code Discrepency - Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\Frazc code Discrepency\Fracz code Discrepency " & Format(Date, "DDMMMYYYY") & ".xls", , ""
' Time & WSC Discrepency Report
DoCmd.OpenQuery "Time & WSC Discrepency Report", acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "Time & WSC Discrepency Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\Time & WSC Discrpency\Time & WSC Discrepency Report " & Format(Date, "DDMMMYYYY") & ".xls", , ""
' CAPP+ Cleanup - Next Level Report
DoCmd.OpenQuery "CAPP+ Cleanup - Next Level Report", acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "CAPP+ Cleanup - Next Level Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\CAPP+ Cleanup - Next Level Report\CAPP+ Cleanup - Next Level Report " & Format(Date, "DDMMMYYYY") & ".xls", , ""
' CAPP+ Cleanup - Attach/Toplevel Report
DoCmd.OpenQuery "CAPP+ Cleanup - Attach/Toplevel Report", acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "CAPP+ Cleanup - Attach/Toplevel Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\CAPP+ Cleanup - AttachToplevel Report\CAPP+ Cleanup - AttachToplevel Report " & Format(Date, "DDMMMYYYY") & ".xls", , ""
' Opn Nomenclature Discrepancy Report
DoCmd.OpenQuery "Opn Nomenclature Discrepancy Report", acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "Opn Nomenclature Discrepancy Report", acFormatXLS, "L:\HEX\PLNG\Geometric\Daily Reports - DB2\Opn Nomenclature Discrepancy Report\Opn Nomenclature Discrepancy Report " & Format(Date, "DDMMMYYYY") & ".xls", , ""
DoCmd.Quit acSave

autoexec_Exit:
Exit Function
autoexec_Err:
MsgBox Error$
Resume autoexec_Exit
End Function

 

[Mr. B]

If you want a function that you can call from the AutoExec macro then you should rename the Function that was created when you converted your macro to VBA code.

You can name it ExprotToExcel like thie:

Function ExportToExcela()

Instead of the AutoExec name it has now.

The function will not be run when the application is opened like the macro with that name did. You now need to create your AutoExec macro that will call your function.

 

[boblarson]

Mr B.

There is no problem having a function named AutoExec and to call it from the AutoExec macro. I frequently do this. There has to be something else going on but it is so bad to look at I didn't really want to.

 

[boblarson]

Also, prabhu - you shouldn't need to open a query to output it.

 

[prabhu]

Mr.B,

Thank you so much. Its working for me now. i have renamed my macro and converted that to vb code and using autoexec to run the vb code.
Its working!!!
Thank you once again!!! you answer is simple & superb !!!

Prabhu

 

[Mr. B]

I am gald you managed to get it working for you.

Just for the record, Bob is absolutely correct with his comment to me. However, I was simply trying to point out the fact that just because a Function is named "AutoExec" does not mean that its code will be run when the applicaiton starts. Only a Macro named "AutoExec" will be run when the applicaiton starts.

 

[boblarson]

Good clarification there Mr. B

 

[Mr. B]

Thanks, Bob, I do appreciate your pointing out that use the "AutoExec" as a function name does not cause any problems. Sometimes I do not make myself as clear as I should.

Hey, if we all work together, we can make it happen.