Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    I have a macro using MSQuery calls a stored procedure on a SQL database. I have two modules one that gets the sql data and one which formats this data once it is in Excel. The later uses the current.region to name the data extracted as a range called "Database", then using offset and resize individual columns are named etc. Both modules work fine if run separately. However if run together the later fails. This is because the latter starts before the data is returned and the currentregion range is $A$1. I need to be able to start the later module when all the data is returned and not before. I am sure this can be done using a loop or something or is there a seperate wait for a period of time function, command.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    The following will pause a macro for 30 seconds

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

    Andrew C

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    Thanks Andrew. This unfortunately stops the SQL data being returned as well, even if if I put the code below the SQL extract code. This means I am getting the same problem??????

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    I've never tried this, but does DoEvents help? You also can try the Sleep API if you find a way to identify when the process of populating the worksheet is complete. Either would be something like:

    Do While MSQueryNotDoneYet
    pause & yield to other processes
    If a very long time has passed Then Exit Do
    Loop

    I put that last If test in there as a safety. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> But save you work first anyway.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    Another thing to consider is to use the OnTime Method to start the second procedure:

    Sub First()
    'blah, blah
    Application.Ontime Now +Timevalue("00:00:10"),"Second"
    End Sub

    Sub Second()
    'Blah, Blah
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    Thank you for this but I am unsure how I can tell if the MSQUERY has completed. Whilst the query is executing cell A1 has some text in it to say Database: Getting Data... and my rngdata variable address is $A$1. I must be able to use this information somehow, however once I have figured this I am unable to pause the macros with out pausing the entire application including the MSQUERY.

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Script (Calling a Stored Procedure) (EXCEL 2000, XP)

    > I am unsure how I can tell if the MSQUERY has completed

    Does it launch a separate window? I once had some code locate a window by title and "sleep" until that window was closed.

    If there is no separate window, then perhaps you can check a cell in the sheet that will contain content after the query is completed? I don't use MSQuery, so this is just a guess.

    It also should be possible to check the process list maintained by the OS, but that's beyond my knowledge, too.

Posting Permissions

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