+ Reply to Thread
Results 1 to 5 of 5

How to force Excel to complete one statement before executing othe

  1. #1
    vrk1
    Guest

    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!


  2. #2
    Nick Hodge
    Guest

    Re: 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
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    "vrk1" <vrk1@discussions.microsoft.com> wrote in message
    news:44ADE79E-508C-44B0-A193-EB18580199AC@microsoft.com...
    > 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!
    >




  3. #3
    vrk1
    Guest

    Re: 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
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > "vrk1" <vrk1@discussions.microsoft.com> wrote in message
    > news:44ADE79E-508C-44B0-A193-EB18580199AC@microsoft.com...
    > > 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!
    > >

    >
    >
    >


  4. #4
    Dave Peterson
    Guest

    Re: 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
    > > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > >
    > >
    > > "vrk1" <vrk1@discussions.microsoft.com> wrote in message
    > > news:44ADE79E-508C-44B0-A193-EB18580199AC@microsoft.com...
    > > > 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

  5. #5
    Nick Hodge
    Guest

    Re: 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
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    "vrk1" <vrk1@discussions.microsoft.com> wrote in message
    news:624F22A6-5A1C-40C3-9411-F164517C27BB@microsoft.com...
    > 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
    >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >>
    >>
    >> "vrk1" <vrk1@discussions.microsoft.com> wrote in message
    >> news:44ADE79E-508C-44B0-A193-EB18580199AC@microsoft.com...
    >> > 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!
    >> >

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1