Make Access wait for Excel

  • Thread starter Thread starter Suggy1982
  • Start date Start date
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
 
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
 
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)
 
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?
 
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.
 
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)
 
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)
 
Back
Top