VBA to invoke Excel after running Access Queries Question

[Andre Laplume via AccessMonster.com]

I have a form/button that issues VBA commands to run multiple queries, modify
some tables and finally open an Excel template. The template is set to
refresh on open against one of the just created Access tables. Now that I ma
confident everything is ok data-wise, I want MsAccess to open (?) the
template, refresh the pivots AND THEN turn the auto-refresh feature off and
save the template file to a share under a new name. (Auto-refresh must be
off since the final resting place of the file will not be able to read the db)
..

Can I do all this frm with in MsAccess? I'd expect the code to be short, I
am just not sure:

1) what the code would look like
2) when does Access give up control to Excel
3) where the code to do the pivot-refresh/save-the-file needs to go.

Thanks!

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Klatuu]

This could be all done from Access; however, I think the better way would be
to write a macro to do what you want in the Excel template that will execute
when the spreadsheet is opened, then just launch Excel using the Shell
function in Access.

I have a form/button that issues VBA commands to run multiple queries, modify
some tables and finally open an Excel template. The template is set to
refresh on open against one of the just created Access tables. Now that I ma
confident everything is ok data-wise, I want MsAccess to open (?) the
template, refresh the pivots AND THEN turn the auto-refresh feature off and
save the template file to a share under a new name. (Auto-refresh must be
off since the final resting place of the file will not be able to read the db)
..

Can I do all this frm with in MsAccess? I'd expect the code to be short, I
am just not sure:

1) what the code would look like
2) when does Access give up control to Excel
3) where the code to do the pivot-refresh/save-the-file needs to go.

Thanks!

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Andre Laplume via AccessMonster.com]

ok, thanks!

This could be all done from Access; however, I think the better way would be
to write a macro to do what you want in the Excel template that will execute
when the spreadsheet is opened, then just launch Excel using the Shell
function in Access.

I have a form/button that issues VBA commands to run multiple queries, modify
some tables and finally open an Excel template. The template is set to

[quoted text clipped - 15 lines]

[Amt]*[Percent],0)))

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Andre Laplume via AccessMonster.com]

one thing......I really do not need the sheet opened at all...unless the act
of opening is necesary to refresh the pivots, turn off auto-refresh and save
to a share.....

It sounds like I need to do this in Excel but as part of the vba, I must
close up everything on the way out...I'd assume in Access I'd see the sheet
open, refresh save and close...?

Is there an Excel option like 'RunMacro on Open or something' ?

I found the code to refresh to pivots and I know how to save it I think...
just not sure how Excel knows to run the macro when opened.....

Finally I assume control is returned to MsAccess when done...?

This could be all done from Access; however, I think the better way would be
to write a macro to do what you want in the Excel template that will execute
when the spreadsheet is opened, then just launch Excel using the Shell
function in Access.

I have a form/button that issues VBA commands to run multiple queries, modify
some tables and finally open an Excel template. The template is set to

[quoted text clipped - 15 lines]

[Amt]*[Percent],0)))

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Klatuu]

Yes, there is a way to do that, but exactly how escapes me for the moment.
It may similar to access in naming it autoexe but I think you name it
Autpen, but I can't be sure.

If you are working with Excel through Access, it is not necessary to make it
visible.

one thing......I really do not need the sheet opened at all...unless the act
of opening is necesary to refresh the pivots, turn off auto-refresh and save
to a share.....

It sounds like I need to do this in Excel but as part of the vba, I must
close up everything on the way out...I'd assume in Access I'd see the sheet
open, refresh save and close...?

Is there an Excel option like 'RunMacro on Open or something' ?

I found the code to refresh to pivots and I know how to save it I think...
just not sure how Excel knows to run the macro when opened.....

Finally I assume control is returned to MsAccess when done...?

This could be all done from Access; however, I think the better way would be
to write a macro to do what you want in the Excel template that will execute
when the spreadsheet is opened, then just launch Excel using the Shell
function in Access.

I have a form/button that issues VBA commands to run multiple queries, modify
some tables and finally open an Excel template. The template is set to

[quoted text clipped - 15 lines]

[Amt]*[Percent],0)))

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))