避免在Excel共享工作簿中覆盖冲突 [英] Avoid overwrite conflicts in excel shared workbook

问题描述

某些背景:我建立了一个共享工作簿，由于人们同时在新行中添加项目，因此遭受了很多保存冲突.

Some background: I built a shared workbook which suffered a lot of save conflicts from people adding items in new rows at the same time.

添加新信息行的过程是通过宏完成的，因此，我创建了一个新版本，其中宏在添加信息之前保存了工作簿，因此停止了将两组数据放入同一行.

The process of adding a new row of information is done via a macro, so I've created a new version where the macro saves the workbook before adding the information, therefore stopping two sets of data being put into the same row.

我的问题:保存文件确实使宏速度变慢，因为该文件约为2 MB.

My issue: Saving the file is really slowing down the macro because the file is about 2 MB.

我的问题:是否可以加快保存过程，或者仅使用其他人的更改来更新工作簿以节省时间?

My question: Is there a way of speeding up the save process, or maybe only updating the workbook with other people's changes to save time?

，其更新所述共享片具有另一个主要目标的宏文件. 根据本地excel文件的数据，我们使用该宏生成了用于报告的标准文本.

The macro file which update the shared sheet has another primary objective. Based on the data of an local excel file, we used the macro to generate a standard text for reporting.

基于共享工作簿用户检查中的标记，该问题是否已报告.

Based on a flag in the shared workbook user check is the issue is already reported or not.

此宏同时被4个或更多的人使用，从而导致冲突.

Also this macro is used by 4 or more people at the same time which results in the conflict.

推荐答案

防止多个用户同时运行宏似乎有可能(尚不完全清楚)可以帮助防止此问题.根据上面OP的评论，这里是实现此目的的代码.该代码将检查Windows进程，以查看该宏的另一个实例是否已在运行.显然，此检查应该是OP脚本中首先发生的事情.

It seems possible (not entirely clear) that preventing multiple users from running the macro simultaneously could help prevent the problem. Based on OP's comments above here is the code to achieve that. The code will check Windows processes to see if another instance of this macro is already running. Obviously, this check should be the first thing happening in the OP's script.

Option Explicit

Function RunningInstancesOfThisScript()
    Dim colProcesses
    Dim objProcess
    Dim lScriptCount


    RunningInstancesOfThisScript = 0

    lScriptCount = 0

    ' Get list of running processes using WMI
    Set colProcesses = GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select * From Win32_Process")

    For Each objProcess in colProcesses
        If (Instr(1, objProcess.Commandline, WScript.ScriptFullName, vbTextCompare) <> 0) Then
            lScriptCount = lScriptCount + 1
        End If
    Next

    RunningInstancesOfThisScript = lScriptCount 
End Function

Function IsThisScriptAlreadyRunning()
    Dim lScriptCount




    lScriptCount = RunningInstancesOfThisScript()

    If (lScriptCount < 1) Then
        ' This should not happen. There should be at least one instance, the current one
        IsThisScriptAlreadyRunning = False
    ElseIf (lScriptCount = 1) Then
        ' The current instance is the only one
        IsThisScriptAlreadyRunning = False
    Else
        IsThisScriptAlreadyRunning = True
    End If
End Function

If (IsThisScriptAlreadyRunning() = True) Then
    MsgBox "Another instance of this script is already running. This instance will now terminate without making any changes. Please try again after a few minutes.", vbExclamation
    WScript.Quit
Else
    MsgBox "There is no other instance of this script currently running. This instance will now proceed and make the changes needed.", vbInformation
End If

另一种方法是检查Excel文件已打开.要运行以下脚本，您需要将<FileName>替换为真实文件名.

Another option is to check if the Excel file is already open. To run the following script, you'd need to replace <FileName> with a real file name.

Option Explicit

Function IsOfficeFileAlreadyOpen(strOfficeFileFullName)
    Dim lPos
    Dim strLockFileFullName



    lPos = InstrRev(strOfficeFileFullName, "\", -1, vbBinaryCompare)
    If (lPos = 0) Then
        ' Only file name has been given, no path specified. Must be in current folder. Good luck!
        strLockFileFullName = "~$" & strOfficeFileFullName
    Else
        strLockFileFullName = Left(strOfficeFileFullName, lPos) & "~$" & Mid(strOfficeFileFullName, lPos + 1)
    End If

    IsOfficeFileAlreadyOpen = CreateObject("Scripting.FileSystemObject").FileExists(strLockFileFullName)
End Function

If (IsOfficeFileAlreadyOpen("<FileName>") = True) Then
    MsgBox "The file '" & <FileName> & "' is already open. Please try again once the file is closed.", vbExclamation
    WScript.Quit
Else
    ' Open the file first
    MsgBox "The file '" & "<FileName>" & "' is available and will be processed.", vbInformation
End If

这两种解决方案都容易受到比赛条件的影响.

Both these solutions are susceptible to race conditions.

这篇关于避免在Excel共享工作簿中覆盖冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！