"File In Use" message stopping overnight run of Visual Basic Excel Script

Question

So I have a VBScript that I run through my task scheduler. Been testing it and running it everyday and about 50% of the time I see this error in the morning when I come into work:

And then if I hit 'notify' the program then finishes executing. But that doesn't help me, because it defeats the purpose of having the program run automatically. My assumption for why it only happens 50% of the time is that after I hit notify the program executes properly and closes everything, then the next day when it runs it doesn't properly close APG.xlsx so then the next day when it runs it produces the same error message.

Program Layout

1. Open a master excel sheet to run VBA script
2. Open up the list of files 1 by 1 refresh the live data connections and then save the excel sheet as name_POSReport.xlsx (i.e. APG_POSReport.xlsx)
3. Close the file, and move to the next file.

My thinking is that even though I am saving the report as another file, do I still have to close the original file as well? It doesn't really make sense that that would be the case, but maybe it is. So I am hoping for some sort of clarification.

Visual Basic Script :

Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("\\fileserver\homeshares\asweet\My Documents\POS Reports\POS Live Reports\runReport.xlsm", 0, False)

xlApp.Run "executeUpdate"

xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

VBA Code

Public wb As Workbook

Sub executeUpdate()
' Vendors will be specified later
'    Dim vendors(0 To 12) As String
    Dim testArray(0 To 2) As String
    Dim path, savePath, ext As String
    Dim i, x, erow As Long

    Application.DisplayAlerts = False

    x = Sheets(1).Cells(Rows.Count, "A").End(xlUp).row + 1

    path = "\\fileserver\homeshares\asweet\My Documents\POS Reports\POS Live Reports\"
    savePath = "\\fileserver\homeshares\asweet\My Documents\POS Reports\POS Live Reports\Monthly POS Report\"
    ext = ".xlsx"

    testArray(0) = "APG"
    testArray(1) = "Code"
    testArray(2) = "IPC"

    For i = LBound(testArray) To UBound(testArray)
        Cells(x, 1).FormulaR1C1 = Now
        Cells(x, 2).FormulaR1C1 = testArray(i)
        Cells(x, 3).FormulaR1C1 = "Fail"

        openBook path & testArray(i) & ext, testArray(i), True
        saveBookAs savePath & testArray(i)
        closeBook

        Cells(x, 3).FormulaR1C1 = "Pass"
        x = x + 1
    Next i

    ThisWorkbook.Save
    Application.DisplayAlerts = True
End Sub

Sub openBook(ByVal fileName As String, ByVal baseName As String, ByVal refresh As Boolean)

    Set wb = Workbooks.Open(fileName, 0, False)

    If refresh = True Then
        If Application.ProtectedViewWindows.Count > 0 Then
            Application.ActiveProtectedViewWindow.Edit
        End If
        wb.Connections(baseName & " POS Report").OLEDBConnection.BackgroundQuery = False
        wb.RefreshAll
        wb.Connections(baseName & " POS Report").OLEDBConnection.BackgroundQuery = True
    End If
End Sub

Sub closeBook()
    wb.Close
End Sub

Sub saveBookAs(ByVal fName As String)
    wb.SaveAs fileName:=fName & "_posReport.xlsx"
End Sub

Answer 1

You can make a call to the VBA ontime method in order to get excel to close after the procedure has run. I'm assuming that you have saved (or closed) any workbooks including the one containing your excecuteUpdate procedure.

You will need a procedure to call to close excel for example:

Sub excelQuit()
   Application.Quit
End Sub

And a call to the ontime method in your executeUpdate procedure e.g.

Call Application.OnTime((Now + TimeValue("00:00:10")), "excelQuit")

This would attempt to close the application 10 seconds after the call is made to ontime, however it will wait until the any active procedures have finished first.

Answer 2

I had the same issue. However I found a work around. Basically I created a folder and used shutil copyfile to create a copy of the desired file. Afterwards I run my pywin32 script and delete the copies. This will stop the message from appearing.