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!

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
[email protected]HIS


"vrk1" <[email protected]> wrote in message
news:[email protected]...
> 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 - 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
> [email protected]HIS
>
>
> "vrk1" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>

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
> > [email protected]HIS
> >
> >
> > "vrk1" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

Ravi

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

http://tinyurl.com/cteja

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"vrk1" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]HIS
>>
>>
>> "vrk1" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!
>> >
>>
>>
>>