“使用中的文件”消息停止运行Visual Basic Excel脚本 [英] "File In Use" message stopping overnight run of Visual Basic Excel Script

问题描述

所以我有一个VBScript我通过我的任务调度程序。测试它并运行它每天，大约50％的时间我看到这个错误，在我上班的早晨：

然后如果我点击通知程序，然后完成执行。但是这并不能帮助我，因为它违背了程序自动运行的目的。我的假设为什么只发生在50％的时间是，在我命中通知程序正常执行并关闭所有内容后，运行它的第二天没有正确关闭 APG.xlsx 所以运行它的第二天会产生相同的错误信息。

程序布局

1. 打开一个主excel表来运行VBA脚本


2. 打开文件列表1 by 1刷新实时数据连接然后将excel表保存为 name_POSReport.xlsx （即 APG_POSReport.xlsx ）


3. 关闭该文件，并转到下一个文件。

我的想法是即使我将报表另存为另一个文件，我还要关闭原始文件吗？情况并非如此，但也许是这样。所以我希望有一些澄清。

Visual Basic脚本

<$设置xlApp = CreateObject（Excel.Application）
设置xlBook = xlApp.Workbooks.Open（ \\fileserver\homeshares\asweet\My Documents\POS Reports\POS Live Reports\runReport.xlsm，0，False）

xlApp.RunexecuteUpdate

xlBook.Close
xlApp.Quit

设置xlBook =没有
设置xlApp =没有
/ pre>

VBA代码

  public wb As Workbook 
 
 Sub executeUpdate（）
'稍后指定供应商
'Dim vendor（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 = （1）.Cells（Rows.Count，A）。End（xlUp）.row + 1 
 
 path =\\fileserver\homeshares\asweet\My Documents\POS Reports\POS Live Reports\
 savePath =\\fileserver\homeshares\asweet\我的文档\POS Reports\POS Live Reports \Monthly POS Report\
 ext =.xlsx
 
 testArray（0）=APG
 testArray （1）=代码
 testArray（2）=IPC
 
对于i = LBound（testArray）到UBound（testArray）
单元格（x，1）。公式R1C1 =现在
单元格（x，2）.FormulaR1C1 = testArray（i）
单元格（x，3）.FormulaR1C1 =失败
 
 openBook path& testArray（i）& ext，testArray（i），True 
 saveBookAs savePath& testArray（i）
 closeBook 
 
单元格（x，3）.FormulaR1C1 =通过
x = x + 1 
下一个i 
 
 ThisWorkbook.Save 
 Application.DisplayAlerts = True 
 End Sub 
 
 Sub openBook（ByVal fileName As String，ByVal baseName As String，ByVal refresh As Boolean）
 
设置wb = Workbooks.Open（fileName，0，False）
 
如果refresh = True然后
如果Application.ProtectedViewWindows.Count> 0然后
 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 
  

解决方案

我遇到过同样的问题。但是我发现了一个工作。基本上我创建了一个文件夹，并使用了shutil copyfile来创建所需文件的副本。之后我运行我的pywin32脚本并删除副本。这将阻止邮件显示。

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

解决方案

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.

这篇关于“使用中的文件”消息停止运行Visual Basic Excel脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！