Automation of running macro

[ColleenLee]

Hi everyone, currently i am modifying a access program. My program is to store some data (of course ) and to export the table into excel file for report generation. There are lots of them and now i want to automate this procedure by just clicking one button.

This is what i have to do before the modification for generating reports:
1.Run the make table query and save it as excel format somewhere.
2.open the macro workbook
3.choose the correct macro and run it

I tried:
Add RunApp in macro to run the macro workbook
Modified the code ( actually i am very new with this, so i do not know to do it)

so would you please give me some suggestion or direction to solve the problem?
Millions Thx

 

[freakazeud]

re:

Hi,
I'm not sure if you will be able to do all this within a macro.
Try to adapt VBA...you are much more flexible and can use error handling which might be vital in such a heavy automated process.
So in a procedure/function you could first run your append query with:

CurrentDb.Execute "YourQueryNameOrSql", dbFailOnError

Then use the TransferSpreadSheet() or OutputTo() method to export the created table to an excel file or why not export a select query instead of creating a separate table.
Afterwards you can use automation to open up excel and run a macro within it. A good start for that would be the following samples:
01
02
03
HTH
Good luck

 

[ColleenLee]

First of all, thx for your help.

I read the website and i tried.
But i still cannot solve the problem.
This is my code:

Sub Workbook_Open()
Dim XL As Object

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "C:\myreportfile.xls"
Windows("macro.xls").Activate
Run "Module1.Report_1"

End Sub

rereportfile.xls is the orignal file.
macro.xls is the workbook that i stored all the macro
It seem that when i am running this, they are not running in the "same" window. so when the program run, it stopped at the following line:

Sub Report_1()

Windows("myreportfile.xls").Activate
Sheets("sheet1").Select
Columns("E:E").Select
...................

How to fix it? or it is welcome to give any opinion.

Thank you so much for your kind attention.

 

[ColleenLee]

I changed my code into:
Sub Workbook_Open()
Dim XL As Object

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "C:\myreportfile.xls"
Application.Run "macro.xls!Report_1"

It works if i open the macro.xls directly. It will automatically generate all the report.
However, if i use access to do it, i mean i click the access button with macro to do it, it doesnt work.
in access, my macro is
Output To
RunApp macro.xls


thx

 

[ColleenLee]

I changed my code to:

Sub Workbook_Open()

Workbooks.Open "C:\myreportfile.xls"
Application.Run "macro.xls!Report_1"

End Sub

Then it's work!!!! hahaha so happy with that but anyone knows the reason?


Now i have to work on how to tell the macro to pick the correct sub to generate the report, i hope it is not very difficult.
Thx

 

[freakazeud]

re:

Hi,
I think you misunderstood me. The different sample automation code I gave you links to should be used within Access and not Excel. If you already have excel open then you don't need to create another instance of it. As I understood it you wanted to export a query or table from within Access and then run a macro which is housed in Excel on this exported document from within Access. As mentioned you cannot really achieve all this within Access with a macro unless you use the RunCode action which calls a function within a module.
HTH
Good luck