vba当其他用户使用文件时打开excel

本文介绍了vba当其他用户使用文件时打开excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我当前的代码

Public Sub OpenFiles() 'Set LiveDealSheet file path 'Check if LiveDealSheet is already open LDSP = "C:\Users\DCHEUNG\Desktop\Programing\LiveDealSheet.xlsm" IsOTF = IsWorkBookOpen(LDSP) 'Set quick workbook shortcut Set TWB = ThisWorkbook If IsOTF = False Then Set LDS = Workbooks.Open(LDSP) Else Workbooks("LiveDealSheet.xlsm").Activate Set LDS = ActiveWorkbook End If End Sub Function IsWorkBookOpen(FileName As String) Dim ff As Long, ErrNo As Long On Error Resume Next ff = FreeFile() 'i was just browsing through the online library and I found that "Open FileName For..." 'have a lot of keywords. If I only want to open the file and copy stuff out to 'another workbook do I use "Open FileName for Input Read As #ff"? 'Then when I actually open the file in OpenFiles() I change '"Set LDS = Workbooks.Open(LDSP)" to "Set LDS = Workbooks.Open(LDSP) (ReadOnly)" Open FileName For Input Lock Read As #ff Close ff ErrNo = Err On Error GoTo 0 Select Case ErrNo Case 0: IsWorkBookOpen = False Case 70: IsWorkBookOpen = True Case Else: Error ErrNo End Select End Function

我要打开的文件是一个共享文件。当没有使用，或当我已经打开文件，这段代码工作正常。但是每当其他用户已经打开一个文件，这个代码就会停止。

The file I am trying to open is a shared file. When no is it using, or when I already opened the file, this code works fine. But whenever another user already open a file, this code stops.

我知道一个事实，即使另一个用户使用该文件，我仍然可以在Read - 只有模式。所以我的问题是如何在这里包含该代码，希望没有弹出窗口询问您是否要以只读模式打开。

I know for a fact that even if another use is using the file, I can still open it in Read-Only mode. So my question is how to include that code in here, and hopefully without the pop-up asking if you want to open in Read-Only mode.

对不起，如果这是一个愚蠢的问题，但我完全不懂编码。

Sorry if this is a dumb question, but I am totally new to coding.

推荐答案

首先感谢您的输入。我已经通过一些试验和错误解决了我自己的问题。

First of all thanks for you input. I have solve the problem on my own with some trial and error.

将代码更改为以下

Public Sub OpenFiles() 'Set LiveDealSheet file path 'Check if LiveDealSheet is already open LDSP = "Z:\LiveDealSheet.xlsm" IsOTF = IsWorkBookOpen(LDSP) 'Set quick workbook shortcut Set TWB = ThisWorkbook If IsOTF = False Then Set LDS = Workbooks.Open(LDSP) Debug.Print "Stage 1 Success"

更改了其他语句中的所有内容

changed everything in this else statement

Else On Error Resume Next Set LDS = Workbooks("LiveDealSheet.xlsm") If LDS Is Nothing Then Workbooks.Open FileName:=LDSP, ReadOnly:=True, IgnoreReadOnlyRecommended:=True End If End Sub Function IsWorkBookOpen(FileName As String) Dim ff As Long, ErrNo As Long On Error Resume Next ff = FreeFile() Open FileName For Input Lock Read As #ff Close ff ErrNo = Err On Error GoTo 0 Select Case ErrNo Case 0: IsWorkBookOpen = False Case 70: IsWorkBookOpen = True Case Else: Error ErrNo End Select End Function