Solved

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

Posted on 2007-03-26
5
1,596 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
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?
Regards,
Rory
PS Is there a reason for using SQL*Plus for this?
0
 

Author Comment

by:JasonAsh
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.

Regards,

Jason
0
 

Author Comment

by:JasonAsh
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.

Thanks,
Jason.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 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.
Regards,
Rory
0
 

Author Comment

by:JasonAsh
ID: 18798552
OK thanks Rory
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

16 Experts available now in Live!

Get 1:1 Help Now