How to force Excel to complete one statement before executing othe
 

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!

How to force Excel to complete one statement before executing othe
 

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
HIS


"vrk1" wrote in message
...
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!

How to force Excel to complete one statement before executing
 

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

"Nick Hodge" wrote:

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
HIS


"vrk1" wrote in message
...
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!

How to force Excel to complete one statement before executing
 

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?thre...%40tkmsftngp03

vrk1 wrote:

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

"Nick Hodge" wrote:

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
HIS


"vrk1" wrote in message
...
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!





--

Dave Peterson

How to force Excel to complete one statement before executing
 

Ravi

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

http://tinyurl.com/cteja

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"vrk1" wrote in message
...
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

"Nick Hodge" wrote:

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
HIS


"vrk1" wrote in message
...
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!