Thread: Excel pushing to the front

I have a long series of macros that harvest data from multiple workbooks, then create and update multiple other workbooks. The entire process takes about 10 minutes to run. I have screen updating turned off, but have noticed that after a few minutes the screen I was looking at goes blank. That's not a problem. What is a problem is that while the macro is running, if I take my hands off the keyboard for any length of time, no matter what I'm working in Excel becomes the active window. I can select another app and go to it, but after a while Excel is again the active window. Any idea how to stop that?

It's a long series of macros. Could I put the FALSE section in my workbook.open, and the TRUE right before closeout in my last macro?

What is the

("E:\OF\macros in background.xls")

Is that a workbook that should be existing in one of my drives?

That's what I thought. My workbook name and filepath (sorry it's so long right now) is

S:\Budget\SOBUDGET\CPS\Reports\Budget Monitoring Tool\Work in Progress\Test Version-The Budget Monitoring Expense Update Tool.xls

If the workbook is already open, do I need to provide a filepath, or just the workbook name? Trying it like this:

With GetObject("Test Version-The Budget Monitoring Expense Update Tool.xls")
    .Close 0
  End With

I get an automation error "invalid syntax".

SNB,

I tried it with the full path, and it seems to work, but I'm not sure it works. Here's the way I configured it:

Private Sub Workbook_Open()
'This macro opens all of the regional and State office Expense Detail files
    Dim ExpenseFolder As String
    Dim FileNames As Variant
    Dim Wkb As Variant
    
    With GetObject("\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\CPS\Reports\Budget Monitoring Tool\Work in Progress\The Budget Monitoring Expense Update Tool2.xls")
     .Close 0
    End With
    
    Application.DisplayAlerts = False
    Application.AutoRecover.Enabled = False
    
    

    Sheets("Sheet1").Activate
    MsgBox "Worked so far"
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        ExpenseFolder = "\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\12MFR\Expense_Dtl_Reports\"

        FileNames = Array("BR1112_ExpDtl_JB.xls", "BR1112_ExpDtl_AH.xls", "BR1112_ExpDtl_DG.xls", _
                          "BR1112_ExpDtl_MH.xls", "BR1112_ExpDtl_BV.xls", "BR1112_ExpDtl_APS_CCL_Team.xls", _
                          "BR1112_ExpDtl_JAS.xls", "BR1112_ExpDtl_SO_Team.xls")


        For Each Wkb In FileNames
            If Dir(ExpenseFolder & Wkb) <> "" Then
                Workbooks.Open ExpenseFolder & Wkb, ReadOnly:=True, UpdateLinks:=True
            End If
        Next Wkb
        
        Workbooks.Open FileName:="\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\CPS\Reports\Budget Monitoring Tool\Todays Contracts.xls"
        Workbooks.Open FileName:="\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\CPS\Reports\Budget Monitoring Tool\Todays WPRs.xls"
        Workbooks("The Budget Monitoring Expense Update Tool2.xls").Activate

    End With
    MsgBox "Last stop!  All out!"
Stop
    Processing

End Sub

When I open my workbook, the worksheet flashes and goes away, but the application window remains open. No action occurs beyond that point. Neither of the MsgBoxs fire up, and in the Application Window I can reopen the workbook and achieve the same results.

No, I'm not joking. If you want to call me an idiot, be honest and say so.

I did successfully utilize the Application.Visible routines at the start and end of my routines, but you then posted the .Close 0 code, so I DID try to look into what .Close 0 was trying to do, but couldn't find anything on that in the help files, nor on the internet. The manner in which you posted that code led me to believe that what you had posted was the full code (other than having the incorrect workbook path and name). So, it was easy enough to understand the With and End With pieces, but no, I couldn't find anything on .Close 0, and yes, I did try.

I post because I want help. I don't always understand the help, and so I ask questions. I try to learn, I try to research, but I'm completely self-taught on this stuff, and I have little or no time to research issues. I read everything I can get my hands on, and so am learning, but no, I'm not there yet. If you want to post smarmy remarks about my lack of abilities that's your right, but doesn't encourage me in any way. If you want to help, then please help. If I need to research, show me the way and I'll do it. If you instead want to ridicule and demean me, then please don't respond to my posts.

Thanks for your help thus far.

John

Leath,

Thanks for asking. Yes, they're all opening in the same instance of Excel, and no userform is being used. When I open my main workbook, it then opens all of the source workbooks, runs each one through multiple gyrations, harvests the data it needs, then closes the workbook, then opens others to which it lookups up additional information, closes those, then opens a template in which to insert the information gathered, configures the data for a specific userbase, and saves the template under a new name for each userbase required. Altogether, it opens, references, creates, and saves about 40 workbooks. I'd be glad to post my main workbook if it would be beneficial, but it wouldn't run well without all of the additional workbooks it depends on. But if you want to look at it let me know.

Thanks,
John

You bet. Let me know if I can help explain anything. It WILL try to run upon opening, but should fail out pretty quickly.