VBA to invoke Excel after running Access Queries Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andre Laplume - 26 Jul 2005 18:13 GMT

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!

Signature

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)))

Reply to this Message

Klatuu - 26 Jul 2005 20:48 GMT

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 - 17 lines]
>
> Message posted via http://www.accessmonster.com

Reply to this Message

Andre Laplume - 26 Jul 2005 21:00 GMT

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
[quoted text clipped - 6 lines]
>>
>> >[Amt]*[Percent],0)))

Signature

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)))

Reply to this Message

Andre Laplume - 26 Jul 2005 21:09 GMT

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...?

Signature

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)))

Reply to this Message

Klatuu - 26 Jul 2005 21:22 GMT

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
Auto_Open, 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
[quoted text clipped - 25 lines]
>
> Message posted via http://www.accessmonster.com

Reply to this Message