?
Solved

Microsoft Excel: VB code running thats loads SQL script but once running can't use computer.

Posted on 2006-05-10
11
Medium Priority
?
197 Views
Last Modified: 2010-04-07
Hello All,

I am running an Excel spreadsheet that calls 3 different SQL scripts that take about 20mins to run through. When it is running I can not use my machine because the SQL window keeps taking
over. For example when it is running if I click on my Microsoft Outlook I will only have it for about
5 seconds then the SQL window is Live again, I then click on Outlook again but then 5 seconds later the SQL plus window again pops up.

Here is the code I have in Excel I click a command button to run it:

Sub command_button1()

Call Build_Tables("1_get_managers_names")
Call Build_Tables("2_get_sales")
Call Build_Tables("3_get_orders")

End Sub

Sub Build_Tables(sql_script)

UserName = UCase(Environ("USERNAME"))

    UID = "USER_" & UserName
    PWD = UserName
    DSN = "my_database"
   
     script_file = "C:\my_sql\" & sql_script & ".sql"
     cmd = "C:\ORANT\BIN\PLUS80W.EXE " & UID & "/" & PWD & "@" & DSN & " @" & script_file
    AppID = Shell(cmd, 6)
    sleep (10)
   
    On Error GoTo Finished
    Do While True
        AppActivate AppID
        sleep (2)
    Loop
       
    AppActivate "Microsoft Excel"
   
Finished:
       
End Sub

Sub sleep(Secs As Integer)

    Start = Timer
    Do While Timer < Start + Secs
        DoEvents
    Loop
 
End Sub

Is there a way I can set this thing running and then excel pops up when finished or something like that.

Many Thanks,

Jason.
0
Comment
Question by:JasonAsh
  • 6
  • 4
11 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16648346
Is it not this block:

    Do While True
        AppActivate AppID
        sleep (2)
    Loop

That is responsible for the SQL window popping to the front ?
0
 

Author Comment

by:JasonAsh
ID: 16648404
I think it is but I need my VB code to check when the first SQL query has finished so it can then run the second query, do you know a way round this ?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16648503
Hi,
If you add this function, you can then pass it your AppID to check whether the SQLPlus process is still running:

Function IsProcRunning(lngPID As Long) As Boolean
    Dim objWMIService, objProcess, colProcess
    Dim strComputer, strList
    IsProcRunning = False
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")
        Set colProcess = objWMIService.ExecQuery _
            ("Select * from Win32_Process Where ProcessID = " & lngPID)
    If colProcess.Count > 0 Then IsProcRunning = True
End Function

Just put it in a loop like:
Do While IsProcRunning(AppID)
Sleep 60
Loop


HTH

Rory
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:JasonAsh
ID: 16648626
Thanks Rory,

Is may be a little beyond me, do I added the Function 'IsProcRunning' to my Sheet1 Object and
the loop under my Sub Sleep ?

Sub sleep(Secs As Integer)

Do While IsProcRunning(AppID)
Sleep 60
Loop
 
End Sub

Thanks,

Jason.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16648720
Hi Jason,
I would add it to a normal module (just use Insert-Module in the VBE) and make it a public function. Then your sub becomes:

Sub Build_Tables(sql_script)

UserName = UCase(Environ("USERNAME"))

    UID = "USER_" & UserName
    PWD = UserName
    DSN = "my_database"
   
     script_file = "C:\my_sql\" & sql_script & ".sql"
     cmd = "C:\ORANT\BIN\PLUS80W.EXE " & UID & "/" & PWD & "@" & DSN & " @" & script_file
    AppID = Shell(cmd, 6)
    sleep (10)
   
    On Error GoTo Finished
    Do While IsProcRunning(AppID)
        sleep 60
    Loop
       
    AppActivate "Microsoft Excel"
   
Finished:
       
End Sub

HTH

Rory
0
 

Author Comment

by:JasonAsh
ID: 16648770
Hi Rory,

Just gave that a go but I am getting

'ByRef Argument Type MisMatch' on Do While IsProcRunning(AppID)

Thanks,

Jason.
0
 

Author Comment

by:JasonAsh
ID: 16648915
Hi Rory,

Think that may have done the trick I just added Dim AppID as long and it is running through.
Will test it now and get back to you.

Thanks,

Jason.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16648921
Sorry - change the first line of the function to:
Public Function IsProcRunning(ByVal lngPID As Long) As Boolean

and that should do it.

HTH

Rory
0
 

Author Comment

by:JasonAsh
ID: 16649259
Looking good Rory thanks for your help I will give you the points.
One thing though I think it is running a little slower now, would that be becase of the
sleep 60 line ?

Thanks,

Jason.

    Do While IsProcRunning(AppID)
        sleep 60
    Loop
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 16649472
Jason,
Yes, very probably - try setting it back to 5 and see if that helps.
Rory
0
 

Author Comment

by:JasonAsh
ID: 16649519
Many Thanks Rory for your help.

Jason.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question