Solved

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

Posted on 2007-03-26
5
1,625 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
[X]
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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

635 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