Solved

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

Posted on 2007-03-26
5
1,589 Views
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,

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
0
Comment
Question by:JasonAsh
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Comment

by:JasonAsh
Comment Utility
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
 

Author Comment

by:JasonAsh
Comment Utility
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
Comment Utility
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
 

Author Comment

by:JasonAsh
Comment Utility
OK thanks Rory
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now