Make Access wait for Excel

S

Suggy1982

Hi,

I have an access database, which open an excel file via VBA and then run
some VBA which has been written in the excel file.

The purpose of the vba and the excel file is to retrieve some data from
Essbase. The vba and the Essbase retrieve work fine when I run them directly
from the excel file, without the involvement of access.

However when I run the whole thing from access, access doesn’t wait for the
Essbase retrieve to complete before it saves and closes the excel file and
continues running the vba in access.

Is there a way to make access wait until excel has finished before it
continues. I realise I could use application.wait, however the time taken for
the Essbase retrieve might not always be the same and I do not want to put in
there a silly time of say 10mins and have the system waiting there.

Any help would be appreciated.

Regards,

Adrian
 
S

Suggy1982

Thanks for your recommendation, however due to my limited knowledge, can you
expand on the link you sent me and give me some guidance of where I would put
the vba to open the excel file?

Regards,

Adrian
 
J

Jack Leach

Copy&Paste the code in the link into a new standard module (not a form's
module or a class module... in the vba editor on the menu/toolbar the second
or third button from the left, or new module from the database window). Be
sure to name this module with some prefix so it doesn't interfere with any
function names (ex. modShellWait rather than plain old ShellWait).

Copy & paste the code, compile all modules and save. Then, where you want
to open the file, use this line of code:

Call ShellWait("C:\ThisFolder\ThatFile.xls")



hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
S

Suggy1982

But that won't run the macro in the excel file. Also before the macro run's
access has to pass a value from access into a cell in the excel file.

is there another way? Something that maybe waits until excel has closed
before the vba in access continues?
 
D

Dirk Goldgar

Suggy1982 said:
Hi,

I have an access database, which open an excel file via VBA and then run
some VBA which has been written in the excel file.

The purpose of the vba and the excel file is to retrieve some data from
Essbase. The vba and the Essbase retrieve work fine when I run them
directly
from the excel file, without the involvement of access.

However when I run the whole thing from access, access doesn’t wait for
the
Essbase retrieve to complete before it saves and closes the excel file and
continues running the vba in access.

Is there a way to make access wait until excel has finished before it
continues. I realise I could use application.wait, however the time taken
for
the Essbase retrieve might not always be the same and I do not want to put
in
there a silly time of say 10mins and have the system waiting there.

Any help would be appreciated.

Regards,

Adrian
Click to expand...


Please post the code you're currently using to do this.
 
J

Jack Leach

I realise I could use application.wait, however the time taken for
the Essbase retrieve might not always be the same and I do not want to put in
there a silly time of say 10mins and have the system waiting there.
Click to expand...

I'm not sure about application.wait in access, but there is a Sleep API (see
http://mvps.org/access/api/api0021.htm ) that should accomplish the same
thing.

Rather than setting some exorbitant time in the code, run a small increment
loop on the sleep api with a condition to check the status of your excel
operation. How you might do that, I'm not quite positive, but I would think
you should be able to flag this out somehow.



--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
J

Jack Leach

But that won't run the macro in the excel file.
Click to expand...

Access has the ability to autorun a macro if it is named AutoExec. Possibly
Excel provides some sort of similar functionality?

Or, I believe it is possible to execute an exel macro from access via
automation. I'm not well versed in exel automation myself, but there's
plenty here that could help with something like that (half the threads in
this NG seem to revolve around running excel from access).

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finicky EssVRetrieve 1
Access #Error & Excel Pivot 0
Scheduling task to run for Excel Addin 1
replace missing values 1
Hyperion Essbase Excel Add-in - returning text ("0") for null valu 5
Essbase VBA Functions for Excel Add-In 0
Essbase Retrieve 1
How do I code to retrieve on a hidden tab? 0

Top