VBA in Excel how to stop - AppActivate "Microsoft Excel" getting focus.

I am calling 4 .sql scripts from excel and at the moment excel opens sql plus and then excel runs the scripts in order and checks for the app to finish each one then adds a meesage onto excel saying script 1 done then opens script 2 and then runs the next one etc. Problem I have is if I am working in another app eg Microsoft Outlook writing an email when excel sees the script has finshed excel pops up on the screen which I think is becaseu of the line: AppActivate "Microsoft Excel"

How can I make excel be always in the background and only come alive when the last sql script has finshed. I have tried Excel.Application.Visible = False which worked great except I couldn't see in excel when things had run if need be.

Many Thanks,

Jason.

Here is my code.

Sub run_my_scripts
' This script callsthe other code and passes the file names and also adds a message onto excel when finshed.

Call Build_Dates_Table("1_file")
Cells(1,2) = "Done script 1"
Call Build_Dates_Table("2_file")
Cells(1,2) = "Done script 2"
Call Build_Dates_Table("3_file")
Cells(1,2) = "Done script 3"
Call Build_Dates_Table("4_file")
Cells(1,2) = "Done script 4"

End Sub

This bit of code opens and runs the sql files.
Sub Build_Dates_Table(sql_script)
Dim AppID As Long
 
    UID = "USER_J"
    PWD = "J"
    DSN = "MY_DB"
   
    sql_script = "C:\my_sql_files\" & sql_script & ".sql"
         
    script_file = (sql_script)
   
     cmd = "C:\ORANT\BIN\PLUS80W.EXE " & UID & "/" & PWD & "@" & DSN & " @" & script_file
   
    AppID = Shell(cmd, 6)
    sleep (1)
   
    On Error GoTo Finished
   
    Do While IsProcRunning(AppID)
     sleep 5
    Loop
       
    AppActivate "Microsoft Excel"
   
Finished:
       
End Sub

Sub sleep(Secs As Integer)
    Start = Timer
    Do While Timer < Start + Secs
        DoEvents
    Loop
End Sub

' This checks if SQL is still alive.
Public 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
JasonAshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Couldn't you just move the AppActivate line from the Build_Dates_Table sub to the end of the run_my_scripts sub?
Regards,
Rory
PS Is there a reason for using SQL*Plus for this?
0
JasonAshAuthor Commented:
Hi Rory,
If I move the 'AppActivate "Microsoft Excel" to the run_my_scripts sub will excel still take over ?
I want excel to run the scripts but never come to the foreground until all the scripts have finished.
The whole process takes about 10mins to run as SQL plus is building various tables and then the final table is imported into excel.

I have it running SQL*plus so I can seee it is running minimized at the bottom of the screen while I am working on other things like MSOutlook, Word etc.

I'll give it a go now and let you know if moving the 'AppActivate "Microsoft Excel" does the trick.

Regards,

Jason
0
JasonAshAuthor Commented:
Hi Rory,
I think it may have worked.... What I'm trying to prevent is the excel sheet popping up in the foreground
while the vb code is running and the user accidently typing in the cells while it is running. eg user runs the vb code then goes off to write an email in outlook and while typing the email, the excel spreadsheet pops up and they start typing in excel without knowing until they look at the computer screen.

So i just thought what with your idea of maybe moving the 'AppActivate "Microsoft Excel' and then maybe I protect the excel sheet so they can't type in it should solve this problem, what do you think ?

If I do the protect sheet do you know off the top of your head how can I stop the microsoft excel message: 'The Cell or Chart you are trying to change is protected and therefore read only'
popping up. Just quickly tried Application.DisplayAlerts = False but it didn't stop the message, any ideas or maybe I can disable the sheet or something so they can view it not type in it.
Sorry to be a pain but I was hoping this was a easy one.

Thanks,
Jason.
0
Rory ArchibaldCommented:
I wouldn't bother with the protection. Move the AppActivate line to the main sub, then add a Beep line after it and/or perhaps pop up a message box or userform to say that the routine has finished. This will take focus off the worksheet so keystrokes don't go into it.
Regards,
Rory
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JasonAshAuthor Commented:
OK thanks Rory
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.