How to force Excel to complete one statement before executing othe

[Guest]

This is my problem:

I have two lines of statement in my VBA code as follows:

__________________________________________
line1: Shell("net use \\fs1\ipc$")

line2: Set db = OpenDatabase("\\fs1\test\abc.mdb", False, True, "MS
Access;PWD=12345")

Other lines follow here....
_________________________________________

When I execute the VBA code, sometimes I get a handle to the database and
sometimes I dont (I get a runtime error 3051 because there is a delay in
getting the handle to the database due to slow network).

When I debug this line by line, I dont get any error message.

I want to make Excel complete executing LINE1 (however long it takes on a
slow network) and then execute LINE2 . How do I make Excel do this?

I have been researching on this for the last 3 days. If anyone here can
help me on this, this would be of great help to me! Thanks in Advance!

 

[Nick Hodge]

You cannot stop Excel from executing the next line as the Shell function
runs asynchronously.

You could use Application.Wait so you put a delay in that would ensure,
barring crashes that the application was open, e.g (30 sec delay)

Application.Wait(Now + TimeValue("0:00:30"))

I supect you could use an API call but this could be a complicated route


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Nick - Excellent! Thank you. Thats exactly what I wanted to know.

I do not want to use the Application.wait statement as a 10 second / 30
second delay would make my customers feel that I am doing a sloppy job in my
code.

How I can use API calls to connect to the File Server instead of using this
Shell statement to do the same?

Thank you so much.

Regards,
Ravi

 

[Dave Peterson]

Here are a couple of options:

http://support.microsoft.com/?kbid=214248
XL2000: How to Force Macro Code to Wait for Outside Procedure

And

Here's a link to a nice ShellAndWait function that does that.
http://groups.google.com/groups?threadm=ump2dlfcBHA.1656@tkmsftngp03

 

[Nick Hodge]

Ravi

You'll likely find something in this or other archived threads

http://tinyurl.com/cteja

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)