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

Posted on 2007-03-26
Medium Priority
Last Modified: 2013-11-25
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,


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
    AppActivate "Microsoft Excel"
End Sub

Sub sleep(Secs As Integer)
    Start = Timer
    Do While Timer < Start + Secs
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
Question by:JasonAsh
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 85

Expert Comment

by:Rory Archibald
ID: 18793690
Couldn't you just move the AppActivate line from the Build_Dates_Table sub to the end of the run_my_scripts sub?
PS Is there a reason for using SQL*Plus for this?

Author Comment

ID: 18798217
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.



Author Comment

ID: 18798387
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.

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 18798545
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.

Author Comment

ID: 18798552
OK thanks Rory

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

719 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